CODEWITHSUNDEEP
mysqldatabase-designrelational-databaseschema
user2896120
user2896120

Reputation: 3282

Posts, comments, replies, and likes database schema

I have a site where a users can comment on posts or reply to a comment. The user can also like replies or comments. However, there is another field called reply_to within the reply table. Here's my current schema:

Comment
id
user (foreign key)
post (foreign key)
comment

Reply
id
user (foreign key)
reply_to (who the user is replying to)
comment (foreign key)
reply

CommentLike (Table that shows which user liked which comments)
id
comment (foreign key)
user (foreign key)
like (1 = likes, 0 = dislikes)

ReplyLike (Table that shows which user liked which replies)
id
reply (foreign key)
user (foreign key)
like (1 = likes, 0 = dislikes)

Does this seem like a good schema to use, or is there a better way to create this sort of structure?

Upvotes: 1

Views: 6933

Answers (1)

nice_dev
nice_dev

Reputation: 17835

I would propose the structure like below having only 2 tables:

Comment:
id
user (foreign key)
post (foreign key)
comment_text
parent_comment_id (null or -1 if a new comment and comment_id of the parent if a reply)


CommentLike (Table that shows which user liked which comments):
id
comment (foreign key)
user (foreign key)
like (1 = likes, 0 = dislikes)
  • The reason to do this is because reply is nothing but a comment in itself, with only being a child to some parent comment. Hence, I wouldn't make it a separate entity.
  • Note that, you will need to take care of delete operation and delete all comments who have the current comment being deleted as it's parent_id. You can take the help of ON DELETE CASCADE for this.

Upvotes: 14

Related Questions