best use of tables to improve scaling?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Pheesh
Forum Newbie
Posts: 2
Joined: Fri Jun 18, 2010 3:42 pm

best use of tables to improve scaling?

Post 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!
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: best use of tables to improve scaling?

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: best use of tables to improve scaling?

Post 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.
Pheesh
Forum Newbie
Posts: 2
Joined: Fri Jun 18, 2010 3:42 pm

Re: best use of tables to improve scaling?

Post by Pheesh »

thanks for the comments guys, that clarifies it for me. :)
Post Reply