Database design for a product voting system -

i'm making system user can vote or down on product, need able explicitly work out amount of ups , downs product has, total score recent period.

each vote can optionally have comment it, , users need ability echo/boost other peoples comments (kinda retweet), , add/subtract total score of product depending on parent vote being retweeted.

here current proposed tables:

product
id, name, category_id

vote
id, user_id, product_id, parent_id, comment, score, datetime

user
id, username etc.

i thinking possibly need comments table effectively? votes' score field either 1 or -1 per advice read on stackoverflow allow me gather sum() of column calculate total votes, possibility have separate vote_up , vote_down tables...but not sure.

depending on want do, can incredibly sophisticated problem, here's take on simplest way (eg. can throw in 10 min before leave work ;-p)

i try stackoverflow/hotornot style approach, , store ranking unsigned integer.

products( id,  category_id,  name,  rating integer unsigned not null default 0 ); 

then in 'votes' table, store vote (up/down). think table have 'votes' table looks fine( although use either enumeration score datatype, or strategy ensure vote can't manipulated via xss. eg. modifies vote vote +10,000 instead of +1, not cool )

for small fun app, can incrementing or decrementing count when user clicks, if doing aspirations of scaling out, vote calculation , ranking via batch process runs every 10-15 minutes.

also @ level, start using algorithm weight vote values. example, if same user votes (up or down) same product more once day(or once every) votes after first should not count towards calculating rank of product.

for example, here how quora's ranking algorithm works

if user "power user" or has account more active, maybe vote more important new users vote. think on yelp, if don't have more 1 or 2 reviews, rating , reviews don't counted until meet minimum number of reviews. really, skies limit.

ps. recommend checking out o'reilly book on of strategies solving these kinds of problems