0

Let's assume I've a table called post and want to add comments, they both essentially the same so there's no need to create another table, now I can just make a FK column to point to the same model, should I take this approach or just make a separate table to link the comment and post?

so I'll have a new table let's call it PostCommentLinker PCL that will have 3 columns:

-id -post_id -comment_id

is there a better approach? is that a better approach?

1
  • Depends one if you can have more than one comment linked to a row in post Mar 25, 2023 at 1:38

2 Answers 2

0

It just depends on what your use cases are and will be.

From a logical design perspective, it would sound like Comments and Posts should be separate tables. But if you're pretty certain those two objects will be modeled exactly (or near exactly) the same way. Then you can probably implement them as one table.

In reality, especially as requirements change in the future typically, you're generally better off having a single table per object / purpose. So a separate Comments table from the Posts table may arguably be better future proofing.

Keeping them as separate tables (even if they have very similar shapes) may be beneficial for performance and maintenance too, if there are different querying use cases for the objects. For example, you may want to run a query to get the top 3 most recently created Posts for a user, and you might want an aggregative query to get the Tags with the sum of the lowest scored Comments of that user. Completely separate queries that would likely have unrelated indexes on their tables. Separating Posts and Comments into their own tables, allows you to also keep separate those unrelated indexes and maintain them with respect to their tables appropriately.

Also, the relationship between Posts and Comments is typically one-to-many, as a Comment is generally the child to only one single parent Post. If you're following that kind of model, then even as separate tables you wouldn't need a linking table like PostCommentLinker. Instead you can create a foreign key column in the Comments table called PostId that references the parent Post of that Comment.

0

If post and comment has same attribut, that's okay you don't create separate table. Just add ParentID on that table and set 0 for every post, and PostID refering to primary key on the table for comment. Additionally, with that design, you can have nested comment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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