评论投票数据库结构的最佳实践

Question

I'm working on a PHP app that has several objects that can be commented on. Each comment can be voted on, with users being able to give it +1 or -1 (like Digg or Reddit). Right now I'm planning on having a 'votes' table that has carries user_id and their vote info, which seems to work fine.

The thing is, each object has hundreds of comments that are stored in a separate comments table. After I load the comments, I'm having to tally the votes and then individually check each vote against the user to make sure they can only vote once. This works but just seems really database intensive - a lot of queries for just the comments.

Is there a simpler method of doing this that is less DB intensive? Is my current database structure the best way to go?

To be clearer about current database structure:

Comments table:

• user_id
• object_id
• total_votes

Votes table:

• comment_id
• user_id
• vote

End Goal:

• Allow user to vote only once on each comment with least # of MySQL queries (each object has multiple comments)

Answer 1

To make sure that each voter votes only once, design your Votes table with these fields—CommentID, UserID, VoteValue. Make CommentID and UserID the primary key, which will make sure that one user gets only one vote. Then, to query the votes for a comment, do something like this:

SELECT SUM(VoteValue)
FROM Votes
WHERE CommentID = ?

Does that help?