Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I had a table to store user votes for other users. So user1 can vote for user2 (vote up or vote down). One user should be able to vote for many users, but not repeatedly to the same user; user1 can only vote once for user2. I do not think my table is properly structured to reflect this. Currently each time a user gets a vote up/down an ew row is created for this with either a 1 in the vote_up/vote_down column. What changes should I make to properly structure this table?

vote_id              int
voter_id         int
voted_for_id         int
vote_down        int  //stores the up votes
vote_up              int  //stores the down votes

To clarify users 1-19 can all vote for user 20 once each and user 20 can vote for 1-19 once each. At the end of the day I want to get a tally of how many votes each user has for both up votes and down votes each.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

If you want to restrict your votes to "one user can vote for another user once", you simply use the two user id:s as primary key. As for the vote, just use one column and make it an enum:

CREATE TABLE IF NOT EXISTS `user_votes` (
  `voter` int(11) NOT NULL,
  `voted_for` int(11) NOT NULL,
  `vote` enum('up','down') NOT NULL,
  PRIMARY KEY (`voter`,`voted_for`)
)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.