1

I have a Posts table and want to be able to have comments that are full fledged Posts in their own right. So instead of creating a Comments table I was thinking of adding a parent_id column to the Posts table. Is this an anti pattern? Is there a better way of doing this? If not how do I model this relationship? It's not really a foreign key or is it?

Database: Postgres, though I'm modeling it with DBML https://github.com/holistics/dbml/tree/master/dbml-homepage/docs

1 Answer 1

1

Your proposed approach resembles the standard employee table with a managerid, so this is not an obviously stupid one. You will be able to query this with standard queries used on such an example table (although note that many examples of queries on such a table are hierarchical - you may find yourself caching the full parent chain of each post to speed operations).

You are locking yourself down to one parent per post (which is a standard constraint if your model is e.g. Slack or Reddit). The main challenge you will have is that if you don't soft delete posts which are deleted you will need to clean up orphans - and you may have different behaviour for soft vs hard deletion (e.g. if a Reddit post is deleted the comments are gone, but if a comment is deleted the replies may be preserved).

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.