Database in GDSR 2.0

As I said few days ago, one of the main concerns for GDSR 2.0 is new database model. And at this point, I can confirm that new model is created.  Here is the overview of the new database model and how that compares to old one and why I decided on such structure.

New model takes into account the multi site network installations in WP 3.0, and it’s tables can be split into two categories. First category are site wide tables, shared by all blogs in WP. Second category are blog specific, and each blog will have own data storage tables. I was thinking about only having site wide tables, but I decided against that because of the performance issues. If all blogs are to share data storage tables, we would end up having concurrency problems if multiple threads try to insert into the same table.For sites that have 2-3 blogs this wouldn’t be issue, but if site is larger, there is no way to avoid it. So, rating data is stored in each blog individually. That’s the reason WP have post tables individual for each blog, and not merged into single table. And the other reason is backup of data.

Site wide tables

All tables in this group have blog_id column. If this column is zero, data will be accessible by all blogs.

• IPS: this table is used for banning IP’s or range of IP’s from accessing the rating system.
• TEMPLATES: all rendering templates for all blogs will be here. New column is language set to determine the language of the template. Installation will populate this table with default templates in all supported languages (translated) and mark them as blog_id = 0 and these will be default templates for all blogs.
• MULTI_SETS: all multi sets will be here. Site admin can even create some default sets that all blogs can use.

Blog storage tables

Each blog will get only these 5 tables. Also, these tables will be on the site level, or default blog and they will be used for BuddyPress integration data storing.

• DATA: this is main storage table. This will hold data for standard stars and thumbs rating types. All rating categories will be stored here: posts, pages, comments, categories, users…
• LOG: log table is not changed much from current GDSR, but this moderated votes are stored here also.
• MULTI: this is multi sets storage table. This is the same as DATA only for multi ratings.
• MULTI_VALUES: individual values for each rating for each multi set will be in this table.
• TREND: this is a single trend calculation table for all rating types.

What’s changed?

So, moderation table is gone, or better yet merged with log table. This will have a big impact on performance. Currently with moderation active 3-4 queries are needed to determine if the vote is cast, GDSR 2.o will need only one. Comments data table is also gone, all data will be in main data storage table. Same goes for categories table. Multi trends table is also gone, all trends will be in the single table. Current GDSR has 12 tables (and that’s for each blog in WPMU), new one will add 3 tables for all blogs and only 5 tables for each blog. Not to mention, that all tables have much better organization and will need much less space than previous per record.

Next time I will talk more on the rating categories and how they can be easily expanded and individually controlled and set.

Share this: