mysql - user item ratings, 1 rating per user, updateable - table structure?

Question

I'd like to give my users the ability to rate each item on the site from 1 to 5. They also must be able to go back and revise their rating for each item as they please. Lastly, the item's page will display the average-to-date of all user ratings when viewed.

As for the PHP, I think I can handle that. What I'm struggling with is envisioning a logical set of tables to handle the above?

I was thinking just your standard:

ID | ITEM## | USERID | RATING

table design but feel that going back and constantly checking to see if a user had rated a given item before, and returning that result every time they view that item, would make for a rather slow set of scripts... especially as the item & user counts grow over time.

Does anyone have a better table(s) layout idea for this kind of situation? We're looking at 1500+ items from launch and more regularly added in the future, so a table per item is a bit excessive isn't it?

Thank you!

Answer 1

That is the best way to do it. It's called a many to many relation. You have many users and many items. One user can rate many items and one item can be rated by many users.

Instead of having an ID field in the table you proposed, put the key on ITEM and USERID. That way a user can only rate an item once. You will get an error if you try to rate it twice. Also index on both of these fields to speed up the searching of the table.

Answer 2

I'd say putting a unique index over ITEM and USERID and updating the rows with "REPLACE INTO " should work. I would strongly descourage you to make a table per item just to save a bit of load.