I am making a small program where users makes posts or write blogs. On those posts, other users can like or dislike the post as in facebook or upvote or downvote the post as in stackoverflow. I would like to know a good database structure which is commonly used & the program works efficiently with that structure. I have two options



id   head   message   datepost   likes   dislikes
1     ab    anchdg     DATE      1,2,3   7,55,44,3

In the above way, id is the postid. In the likes column, 1,2,3 is the user's id who liked or upvoted the post or blog. 7,55,44,3 is the id of the users who disliked or downvoted the post or blog.



id    head  message   datepost
1     ab    anchdg     DATE


id    postid    userid
1       1         1
2       2         2


id    postid    userid
1       1         7
2       1         55

In this way, I have to create two separate tables for likes & dislikes to get post's likes. In this way, the tables i.e. Likes & Dislikes will get heavily filled. This might make table heavy & processing slow.

So, I would like to know which is the better & standard way to achieve this task?

  • 6
    I'm assuming that a user cannot like and dislike a post? If so, I'd have one table for likes and dislikes, with a BIT column (1 for like, 0 for dislike).
    – dwjv
    Feb 19, 2016 at 10:59
  • 1
    Or 1 and -1 for easier sums
    – jkavalik
    Feb 19, 2016 at 11:40
  • 1
    @dwjv In the first example, user 3 has, in fact, both liked and disliked the post. Feb 19, 2016 at 17:34

2 Answers 2


The problem you face is known as "Normal forms" of databases, especially the first normal form. https://en.wikipedia.org/wiki/First_normal_form.

Your databse with the concatenated user IDs (first version) is not in first normal form.

See https://en.wikipedia.org/wiki/Database_normalization for why and how normalisation is generally considered good.

In your first example, the query for "user 4 does not like the post anymore" becomes complicated. It will have to do string operations, which will have to consider side effects and corner cases (user is the only "liking" user, user is the last liking user, user is in the middle of the liking user string). I would find this bad. Don't do it. Use a normalized design.

re: database gets heavy

If you have a post that has 4 million likes, in database design 1 you would have one row with a "likes" column that is at least 4 million characters wide (because you'll need the comma as seperator chars). You will then have to perform string operations on four million digit wide strings. This is very unperformant and slow.

On the other hand, databases are designed to handle millions of rows. We have databases with several hundred million rows, and count()-operations are fast. Extremely fast. So no, this will not be a performance bottleneck.

The next issue would be readability and maintainability.

For example, tell me what these 2 statements do:

select count(*)
from posts
inner join likes on posts.postid = likes.postid
where postid = 7

select len(likes) - len(replace(likes, ',', ''))
from posts
where postid = 7
  • As I mentioned, if crores or billions of likes present in the table, then wouldn't the table become heavy? Wouldn't it take much time for searching a table with crores of record since the table will get filled very fast?
    – Harshit
    Feb 19, 2016 at 11:06
  • 8
    @HarshitShrivastava mysql can handle simple tables of billion rows, but imagine those billion (dis)likes as strings in your table of users - that might be even bigger and hard to work with.
    – jkavalik
    Feb 19, 2016 at 11:44
  • 5
    One thing @til_b doesn't mention directly (but is typically implied through use of normal forms) is that the second design, properly implemented, will allow the underlying database engine to maintain referential integrity which cannot be done with the first design pattern. That essentially means, if User 4 gets deleted, the database will clear up linked data because it knows which records depend on the User 4 record. The first design is incapable of this because the database doesn't intuitively know how to manage the relation in the string. Feb 19, 2016 at 18:16

The second way is much better because you can easily add or remove a like/dislike.

But you should modify your second solution by using one table for like or dislike.
The columns of the like/dislike table should be id, postid, userid and another one for the value of a like or dislike e.g. 1 for dislike and -1 for like.

Set post_id and user_id as a composite primary key and it works fine.

The size of the table will grow over time. but you have only two real columns in it. The id and the value of the like/dislike. The postid and userid are only linked to it and stored in your user and post table.

  • 4
    You should have user_id, post_id and value in the table. No need for a separate id column.
    – jkavalik
    Feb 19, 2016 at 11:42
  • 4
    As @jkavalik's comment on the question suggested, 1 and -1 would probably be better values for like and dislike than 1 and 2, as it would enable calculation of a total score by way of a simple table sum, rather than subtracting the count of rows with "2" from the count of rows with "1". Feb 19, 2016 at 17:53
  • @DanHenderson: Something like likes - dislikes could be quite a bit faster than a sum. (That said, though, it would also work with 1 and -1.)
    – cHao
    Feb 19, 2016 at 21:45
  • upvoted, how would you do this if you had say 2 more actions like love and anger? i mean the 1 for likes and -1 for dislikes with 2 more actions
    – PirateApp
    Nov 21, 2019 at 12:36
  • 1
    If you don't want to sum anything you can set love = 2 and anger = 3 Nov 21, 2019 at 13:08

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.