Menu
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
315
To open, right-click a table in the object browser of the Navigator pane and choose Table Inspector from the context menu. The Table Inspector shows information related to the table.
The syntax to get all table names with the help of SELECT statement. mysql> use test; Database changed mysql> SELECT Table_name as TablesName from information_schema.
In order to start adding data to a table, right-click the table (in the SCHEMAS pane) to be modified and click Select Rows. You will then find yourself in a window that allows you to enter data (Figure E). In this window, you can either use the result grid or open the form editor.
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.
84
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
