I'm going to set up a voting system for items on a site and am debating how I want to implement it. Visitors will rate individual items 1-10. Ideas:
* Store each vote in a votes table - one row per vote. This approach could allow storing of times and other info with each vote. It could also create tons of rows - there'd likely be hundreds to thousands of items per site, with thousands of votes per item. Would there be a performace impact on using aggregates (avg, count) on so many rows?
* Put vote count and vote total columns in the items table. Each time a vote is cast, add it to vote total and increment vote count. You compute the average yourself.
* Put vote count and vote average columns in the items table. Each time a vote is cast, multiply average by count, add the vote, find the new average and store it, increment count.
?
* Store each vote in a votes table - one row per vote. This approach could allow storing of times and other info with each vote. It could also create tons of rows - there'd likely be hundreds to thousands of items per site, with thousands of votes per item. Would there be a performace impact on using aggregates (avg, count) on so many rows?
* Put vote count and vote total columns in the items table. Each time a vote is cast, add it to vote total and increment vote count. You compute the average yourself.
* Put vote count and vote average columns in the items table. Each time a vote is cast, multiply average by count, add the vote, find the new average and store it, increment count.
?