Reply To: User voting system problem

[__]

Yeah… the user_votes table is a mess. Too many indexes, as well.

Here’s what I understand about your goals:

• users can vote for any other user.
• users can vote only once.
• users can remove their vote (though I’m not clear whether this would allow them to vote again?)

create table user_vote(
  user_id int(11) not null,
  vote_id int(11),
  primary key(user_id), 
  -- key(vote_id),
  foreign key(user_id) references users(user_id),
  foreign key(vote_id) references users(user_id)
)engine=innodb;

user_id is the user voting; vote_id is the user voted for.

The primary key ensures there will be no more than one vote per user.

The foreign keys enforce that both the voter and the votee are valid users.

If you plan to search for records by the user voted for (likely, I think), uncomment the key(vote_id) line.

You know if a user has not yet voted simply because they will have no entry in the table.

When a user removes their vote, you have two options: if they are allowed to vote again, just delete the record; if not, set the vote_id to NULL (so the vote still exists, but counts for no one).