Page 1 of 1

best use of tables to improve scaling?

Posted: Fri Jun 18, 2010 4:33 pm
by Pheesh
Hello folks,
I am wondering what the most efficient database design approach is given the below problem:

The premise of my site is to allow users to rate short stories or quotes and also submit their own.
Currently I have 3 tables
user_table (stores user id, user_name, ip address etc for every unique user)
story_table (stores the story id, story content, avg rating, rating count and author via user id)
rating_table (stores an entry of the user, the story they rated, and the rating itself for every rating performed)
  • functionality: users can rate a given story once and only once. I do need to track whether a user has previously rated a given story, but not necessarily the specific rating they gave a story (that can be added to average on the fly without storing)
  • Currently I have a 'rating_table' dedicated for linking a user with a particular story id they rated. Basically every time ANY user does ANY rating, it's going to add an entry in this rating_table. Obviously that could become an issue as just 100 unique visitors rating 100 stories each will result in 10,000 entries in this table already. That does not seem efficient.
I am wondering if instead of a rating_table, I should set up a table for each individual user? rather than all users hammering the rating_table with every vote they do I could eliminate a rating table and instead offload user/story tracking into individual user tables?
Basically I would be trading one massive table for lots of smaller ones...
Is there a better approach to this, or is this last thought correct? I'm not yet familiar with best known practices in designing for scaling.

Thanks for any insight!

Re: best use of tables to improve scaling?

Posted: Fri Jun 18, 2010 6:22 pm
by s.dot
I wouldn't optimize until you need it. Even so, massive tables are fine. I have forums with over 10 million rows and they load fine. I would look into query optimization rather than table optimization.

Re: best use of tables to improve scaling?

Posted: Fri Jun 18, 2010 6:38 pm
by Eran
Break normalization only when there is an actual performance need, and certainly not in the way you suggested (table per user). As s.dot said, MySQL tables can scale well to dozens of millions of records if indexed and queried properly. Beyond that, you should look into techniques such as sharding/partitioning and perhaps alternative database solutions, but that probably will never be needed.

Re: best use of tables to improve scaling?

Posted: Sat Jun 19, 2010 12:22 am
by Pheesh
thanks for the comments guys, that clarifies it for me. :)