PHP/MySQL: Rating system - Sum each user vote or update general votes table?

Each user can vote once per item (only registered users can vote). So I have a user-votes table that include the user ID, voted Item ID and the vote value (from 1 to 10).

I need to show the total votes and rating for each item so I want to kn开发者_运维知识库ow what is best: Get from the user_votes table all the votes for that item OR save the vote each time a user votes in a general votes table and then get just that field form that database?

---

You should save calculated rating value in the items table, so you will have the ability to sort items by rating etc. It will be much easier.

---

One factor to consider is exactly how many votes you're going to be seeing. If we're talking on vast orders of magnitude (say more than tens of thousands of votes on each of hundreds of thousands of items), then storing every vote could end up using more resources than it is worth.

On the other hand, if there's a reasonably small number of votes (say less than a few hundred records for each of a few hundred items), then it might be advantageous to store each vote for the added flexibility it can provide in future development. A month from now, you might think of something new to do with this information, and having the backlog of votes could be very helpful in tailoring how that should work.

As others have mentioned, this could easily be combined with a meta table to sort things easier and simplify queries. Personally, I would recommend a view for this type of thing, as doing so will decrease your overhead by making updates automatic for the fields containing derived data.

---

If you just increment a sum you won't be able to prevent repeat votes from the same user.

You could use a combination of both. Keep a table listing all of the votes and the user_id/ip address to verify that they haven't voted already and keep a sum associated with the item to avoid having to query the votes table.