DB voting system

Anon

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.

amcgrath

If I were you, and I'm not, I would put the votes into their own table, and create a view of the item table and the vote table showing totals and average. A Big Brother thing you could do is have users you know (user_id) only vote, and give em a "see what other users have voted like you..." kind of logic.

mrjam

First question is hard to answer due to the fact that we dont know the actual size of the tables/items/amount of visitors/ etc etc.

I've heard mysql tables that holds 50+ million rows, with no or small performance slowdown. But that might also be a hardware issue. (128gb vs. 128mb of ram)

The other two ideas are, for me, not bright.

"select votecount from table"
could as easely be used as:
"select count(*) from table"

with the difference, you need one less field. Same goes with most thing you might want (sum(), min() etc. etc.)