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

Question

Hello, 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

You could add a score field and increment or decrement with each thumb action:

UPDATE comments SET score=score+1 Where id=123

Then when you select, order by score DESC.

Answer 2

Since a user should be registered to thumb up/down, I would store the user ID and the post ID to validate the up/downs.

2 tables will be appropriate for this task. Let me know if you need a design.

Answer 3

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.