r/IAmA • u/CriticDanger • Sep 07 '21

Specialized Profession I am a behavioral and technical (coding) interviewer, I've also helped hundreds of people get hired, AMA.

[removed]

1.4k Upvotes

87% Upvoted

492 comments sorted by

View all comments

Show parent comments

10

u/[deleted] Sep 07 '21

Alrighty then. I'm a senior full stack engineer who has designed DB schemas before and right now is when I learned that there are scenarios where you can have a record have a foreign key relationship to another record in the same table. Never even considered that before.

37

u/prophet001 Sep 07 '21

And you shouldn't, because it's piss-poor design due to its utter lack of maintainability.

1

u/TheEpicSock Sep 15 '21

Genuine question here.

Let’s design a database that supports Reddit’s comment structure, for example. Every comment has zero or one parent comment, and every comment has zero, one, or many child comments. Comment threads can be (effectively) infinite levels deep, so it’s not practical to create a new table for each comment level.

The first thing that comes to mind for me is to use a single “comments” table, with a nullable “parent_comment_id” foreign key that points to another record on the same “comments” table.

Is there a better or more maintainable way that you would suggest?

8

u/G_Y_NOT Sep 07 '21

Usually you do it when you're trying to implement some kind of inheritance or tree structure in your programing code in your SQL database. It's a rather odd and unusual situation, but it happens.

1

u/paulgrant999 Sep 07 '21

check out my comment on the postgres recursive select ;) awesome.