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. Replies are not nested, so a reply doesn't have a reply. 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)
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?

0

Your question would be easier to read, if you included the references for your foreign keys.

I'm guessing that user (foreign key) refers to field id in users table (not displayed), and similarly with post.

With those two, as well as with the other foreign keys, I would attach _id to the name, so that it's clear they are ids and not names.

You don't specify primary keys. Did you already designate id in each table to be the primary key, and has auto-increment? If not, make sure you do.

Other than that, I think it's pretty good.

0

In general, if you have a perfectly good "natural" key, don't include an id. For example, for Replies, PRIMARY KEY(user_id, comment_id) is good. It looks like all 4 tables could follow this guideline.

Where are the Users and other tables? It would be better to provide CREATE TABLE than use verbal hand-waving. You will need to do that eventually, anyway.

  • Having (user_id,comment_id) as the PK of Replies would only allow a single reply per user, for each comment - usually this is not how discussion forums with posts, comments, and replies works. Such a composite PK is only a good idea for the "Likes" tables, which don't tend to allow multiple likes. – Willem Renzema Mar 14 at 1:00
  • @WillemRenzema - perhaps it does work for the other tables? (I am unclear on what each table represents -- the difference between a "comment" and a "reply", etc. – Rick James Mar 14 at 1:46

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.