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.
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!