MySQL Table structure of thumb UP & DOWN for comments system?

Question

i already created a table for comments but i want to add the feature of thumb Up and Down for comments like Digg and Youtube, i use php & mysql and i'm wondering What's the best table scheme to implement that so comments with many likes will be on the top.

This is my current comments table : comments(id,user,article,comment,stamp)

Note: Only registred will be able to vote so it should limit 1 vote to each user in a comment

Thanks

Answer 1

Keep a votes table. E.g. votes(comment_id, user_id, value, stamp) - value is -1 or +1.

This allows accountability, and you can do a UNIQUE index on (comment_id, user_id) to prevent duplicate voting. You can also remove a user and all of his votes easily, if he/she is spamming.

For sorting comments by score it is possible to do a JOIN between the comment and vote tables and use SUM/GROUP BY to get the total score. However, this can be slow. For speed, you might consider keeping a score field in the comments table as well. Every time a new row is added to the votes table, you add/subtract 1 from the comment's score.

Because you are storing every vote in a table, it is easy to recalculate the score on demand. Stack Overflow does something similar with reputation - the total reputation score for a user is cached and recalculated every so often.