0

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?

user2896120
  • 3,180
  • 4
  • 40
  • 100
  • In my opinion, reply_to field is redundant, since the relation comment -> user is 1 to 1. Once you’re replying to a comment, that comment belongs to one only user, and you can easily go back to the user by the comment id. – Davide Vitali Mar 09 '19 at 08:04

1 Answers1

9

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.
nice_dev
  • 17,053
  • 2
  • 21
  • 35
  • Hmm, what if let's say `Reply` also had another column named `reply_to` to indicate who the user is replying to. For example, let's say we have 2 Reply rows, each row is a reply to the same comment, however, one reply is replying to the person that posted the other reply. `reply_to` would have that person's user id. Would you still recommend the same structure you proposed with the addition of the `reply_to` column? – user2896120 Mar 09 '19 at 07:38
  • @user2896120 `parent_comment_id` is id of the comment being replied to. – nice_dev Mar 09 '19 at 07:45
  • Yes, I'm saying `reply_to` is which user the person is replying to. So a user is writing a reply to a reply – user2896120 Mar 09 '19 at 07:47
  • @user2896120 So, you can fetch the parent_comment_id row in your table which has it's user_id and notify them. Technically, you will have to notify all users from that level to the top level. – nice_dev Mar 09 '19 at 07:53
  • 1
    I see... so basically `parent_comment_id` is whatever comment the user is replying to. So it can be a reply to a comment, or a reply to a reply – user2896120 Mar 09 '19 at 08:03
  • @user2896120 didn’t you say that replies in your application can’t be nested? – Davide Vitali Mar 09 '19 at 08:06
  • @DavideVitali Now that I'm thinking about it and how the answerer is describing his approach, I feel like the application can easily be migrated to have nested replies – user2896120 Mar 09 '19 at 08:09
  • @user2896120 Yes. You can also implement it like how Facebook does it. Regardless of who you reply, everybody in that comment thread is notified. Of course, this depends upon how you wish to implement it. However, Facebook does provide an option to opt out, which probably is stored in another table and maybe has a NOT IN operation while trying to notify users. – nice_dev Mar 09 '19 at 08:10
  • Oh ok! I thought that was a design choice, like Facebook :) – Davide Vitali Mar 09 '19 at 08:10
  • @vivek_23 Very interesting. Regarding this is a MySQL database. Do you think it'll have negative performance impacts? – user2896120 Mar 09 '19 at 08:11
  • @user2896120 Not very much into DB administration to help you with performance impacts, but this thread can help you. https://stackoverflow.com/questions/39700330/handling-very-large-data-with-mysql# – nice_dev Mar 09 '19 at 08:15