I am building a social networking website, so I wanted to know how to store i.e. the database structure for storing likes and comments of a post.

I have created a table post for storing posts but I want to know how to store "likes" and "comments" so that I can show who are the people who liked your post and what are the comments of the post.

Do I need to create a table for likes which stores user id of every user who liked the post and post_id as foreign key, but this may be very large table as it will create a new row for every user who liked the post. For example if one post has 1000 likes then it will create 1000 rows for the same post in "like" table. The same thing happens with comments as well.

Please suggest me the table structure for post, like and comments. Thanks!

closed as primarily opinion-based by Marco, James Anderson, McNets, Colin 't Hart, mustaccio May 30 '17 at 13:09

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    you can look at stackoverflow database schema to get some ideas: data.stackexchange.com/stackoverflow/query/new – Vladimir S. May 30 '17 at 7:02
  • hey thanks for the reply. I got the idea about comments but I am still confused with "like" table. As I want to list every user who has liked the post. Shall I need to create a new row for each user who has liked the post in the "like" table so that I can fetch the users who have liked the post. – Kamlesh Gupta May 30 '17 at 7:25

Doing it the way you suggested is the right way to keep track of likes. A separate table with user_id and post_id both of which are FK and these 2 FK combined can serve as PK

2 columns both of which are integers(or bigint) is not a problem to any RDMBS.

In fact, 2 integer columns should be the least of your concerns. Because integers are one of the most performant datatypes, and occupy very little space if compared to, for example, nvarchar.

I think this SO post might help you as well https://stackoverflow.com/questions/11808680/database-design-table-of-likes

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