MySQL Up and Down Thumb Structure Table for Comment System?

Question

I already created a table for comments, but I want to add a thumb and up function for comments like Digg and Youtube, I use php and mysql, and I am interested. What is the best table layout for implementation so many people like to comment on top.

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

Note. Only registered can vote, so it should limit 1 vote to each user in the comment

thanks

Answer 1

Keep a table of votes . For instance. votes (comment_id, user_id, value, stamp) - the value is -1 or +1.

This allows for accountability, and you can do a UNIQUE index on (comment_id, user_id) to prevent re-voting. You can also easily delete the user and all your votes if he / she sends spam.

To sort comments by account, you can make a JOIN between comments and voting tables and use SUM / GROUP BY to get a total score. However, this can be slow. For speed, you can also save the rating field in the comment table. Each time a new row is added to the vote table, you add / subtract 1 from the comment rating.

Since you keep every vote in the table, it’s easy to recount the bill on demand. Overflowing does something similar with reputation - the overall reputation rating for the user is cached and recounted so often.

Answer 2

You can add a rating box and increment or decrease with each thumb action:

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

Then, when you choose, order a DESC bill.

Answer 3

Since the user needs to be registered for the thumb up / down, I would save the user id and message id to check up / down.

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