Database Design – Implementing a Voting System Like Reddit

database-design

In creating an application that would require a voting system similar to that of StackExchange or reddit, I want to know how the database tables should optimally be structured.

So far, I have something planned out like this:

Vote(UserID[PK], ContentID[PK], IsLike)

I had planned that the UserID and ContentID(the entity being "liked") would be a composite primary key. This would make it so that I can tell which users have already "liked" certain content. The IsLike field would determine whether the like/vote was positive or negative(like or dislike).

Also, could the composite primary keys be foreign keys linking to their respective tables (UserID –> User table, ContentID –> Content table) or is there something else I would have to do to make sure this would work?

Would this be an adequate way to store information about which users have liked certain content? Any suggestions welcome.

Best Answer

What you have seems pretty solid. How about 

create table vote (
   user_id int not null,
   content_id int not null,
   time_recorded datetime not null,
   vote int not null,
   primary key (user_id, content_id),
   foreign key (user_id) references `user`(user_id)
      on delete cascade,
   foreign key (content_id) references content(content_id)
      on delete cascade,
   check (vote = 1 or vote = -1)
);

A composite primary key with those two keys makes perfect sense. They absolutely should be foreign keys as well. I threw in to cascade a delete from the parent tables; if a user deletes, the "like" will be removed from the content (which, IIRC, is what happens in StackExchange), and if the content is removed, there is no real point in keeping the votes on it. I also implemented the vote column as an integer which must be either 1 or -1, rather than a boolean. That way you can get an overall tally of up and down votes with a simple sum(vote), rather than two select count(*).

Related Question