Geek Answers Handbook

Best practice for voting comment database structure

I am working on a PHP application that has several objects that can be commented on. Each comment can be voted, while users can give it +1 or -1 (for example, Digg or Reddit). Right now, I plan to have a “votes” table that has user_id and their voting information, which seems to work fine.

The fact is that each object has hundreds of comments, which are stored in a separate comment table. After I upload the comments, I have to count the votes and then individually check each vote against the user to make sure that they can vote only once. This works, but just seems really intense in the database - a lot of comments-only queries.

Is there an easier way to do this, which is less database intensity? Is my current database structure the best way?

To clarify the current database structure:

Comment table:

  • user_id
  • object_id
  • total_votes

Voting table:

  • comment_id
  • user_id
  • Voting

Final goal:

  • Allow the user to vote only once for each comment with the least number of MySQL queries (each object has several comments)
+6
comments php mysql voting
source share
3 answers

To ensure that each voter votes only once, design your own vote table with these fields: CommentID, UserID, VoteValue. Make CommentID and UserID the primary key to ensure that one user receives only one vote. Then, to request votes for a comment, do the following:

SELECT SUM(VoteValue) FROM Votes WHERE CommentID = ?

Does it help?

+8
source share

Why don't you keep the total votes for each comment? Add / reduce this when a new vote has taken place.

Then you need to check if the user voted for this comment in order to allow only one vote per comment for the user.

0
source share

You can put a sql join condition that returns all votes according to the comments made by the current user for this object, if you do not have rows, the user did not vote. This is slightly different from how you check each comment one by one in the program.

in terms of database structure, keeping these things separate seems completely logical. vote {user_id, object_id, object_type, vote_info ...)

You may already be doing this, sorry, but I could not interpret your post if that is the case.

0
source share

More articles:


All Articles