0

I am developing a simple blog application in which users can write posts and others can like, comment, share etc. i am facing difficulty in designing database. What I have tried is:

user (user_id(pk), user_name)

post (post_id(pk), post_content, likes, user_id(fk))

comment(comment_id(pk), comment, post_id(fk))

PROBLEM: One user can have many posts and many users can like and comment on many posts. Whether I have to separate two tables like: user, post and userpost?

Can anyone tell me if anything wrong in this design?

|improve this question
4

Try something like this

user (user_id(pk), user_name)

post (post_id(pk),user_id(fk), post_content)

comment(comment_id(pk),post_id(fk),user_id(fk), comment)

likes(post_id(fk), user_id(fk) status)

In status column in likes table you can save it as like or unlike can be tinyint field with 

NULL - Default value
True - Like and False - unlike(if you have unlike option)
|improve this answer
  • thnx..I want is to count how many likes the post have from other users, and to know which users have liked the post (same as facebook) – Fenil Oct 31 '14 at 11:27
  • 1
    You can query like Select count(*) from like where post_id= @postId and similarly for user_id – vijayp Oct 31 '14 at 11:30
  • I mean, Will I necessarily have to make another table for likes? – Fenil Oct 31 '14 at 11:32
  • 1
    yes, that will be better as Many user can like Many post. M-M relation so it need to have a table to save like info – vijayp Oct 31 '14 at 11:34
  • 1
    Yes I think you will – James Anderson Oct 31 '14 at 11:34
2

All you need to do is add user_id to your comment table. That way each comment can be linked to a post and a user.

|improve this answer
  • 1
    The @vijayp answer is worth a look as I think the extra tables for likes is a good idea but the table should be called likes and not the reserved word like – James Anderson Oct 31 '14 at 11:24
  • But, all I want is to count how many likes the post have from other users, and to know which users have liked the post (same as facebook) – Fenil Oct 31 '14 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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