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.

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.

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

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
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 Apr 9 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.

share|improve this answer

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.