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?
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.
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.
