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.

  • If you want to make it like StackExchange, you'd also have to include a date/time when the vote was recorded.
    – Joel Brown
    Commented Apr 8, 2014 at 10:15

2 Answers 2


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(*).

  • Thanks for the answer, I was assuming this sort of database design would be alright for what I needed, but never considered the cascade delete or integer vote column. This really helped.
    – biddano
    Commented Apr 9, 2014 at 2:40

Your design seems like a good one for what you want to achieve.

Keeping your fact table as simple as possible is a good practice i.e. keeping only foreign keys against likes/un-likes, which then links back to dimension tables, such as User, Content etc.

Don't be shy to index your fact table, for quicker search and updates.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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