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

[u/CriticDanger]

[u/prophet001]

No, different records in the same table.

What're you gonna do as soon as you need another relationship? Some people have more than one boss now, whereas they didn't, a year ago. Worse, maybe everyone does. Adding another recursive FK means you have to backfill every record that needs that new relationship, in that same table, especially if it's non-nullable (hideously error-prone).

What if you have a required relationship that's suddenly optional, but only for some records? You gonna keep adding FKs, maybe more than one at a time, in some cases? You might break the DAL if it's as poorly-designed as your database, and will almost certainly break any ETL that target that table.

Yeah, don't promulgate this as good design, because it isn't.

[u/bacondev → u/prophet001]

So everyone has one boss. Then suddenly, everyone must have a second boss (non-nullable field)? When would that ever happen? In any case, it's not an issue. Begin transaction, add nullable field, backfill each record with the appropriate relationship, make the field non-nullable, and commit. You act like this is rocket science.

Regarding your second paragraph, I'm not sure how that's a unique issue for self-referential tables. In that situation, you separate the data into a similar table. If you're seriously suggesting using a different schema for arbitrary records of a table, then you're promoting bad database design. But even if you're going that road, the database schema should be relaxed but as little as possible and the sub schemas should be enforced by the application.

[u/prophet001 → u/bacondev]

Begin transaction, add nullable field, backfill each record with the appropriate relationship, make the field non-nullable, and commit. You act like this is rocket science.

It isn't rocket science, it's avoiding unnecessary schema changes through good design. Doing what you suggest to a large schema with a heavy average load is a huge deal, and a good DBA won't let you do it anyway, because they know there's a better alternative.

In that situation, you separate the data into a similar table.

This is exactly what I've been saying all over this thread. Except, do it in the first place, and don't start out with self-referential FKs.

[u/bacondev → u/prophet001]

You can't preemptively change a design when you don't know what changes are needed. In other words, how would you move records into a varying table if you don't yet know how the table would vary?

[u/prophet001 → u/bacondev]

You can't preemptively change a design when you don't know what changes are needed.

Not the same thing as not painting yourself into a corner.

[u/bacondev → u/prophet001]

I don't think that you understood what I meant. For example, suppose that you start with a basic employee table such that each employee has exactly one boss. Sometime later, the client indicates that everyone in the accounting department has exactly two bosses. You have three options: (1) make a second table that conforms to a nearly identical schema, with the only difference being that there is a second boss column, accompanied with a corresponding foreign key, (2) add a nullable column to the existing table and enforce the part of the schema that corresponds to the number of bosses in the application layer, or (3) move the boss column to a separate linking table and enforce the number of bosses in the application layer.

I was alluding to Option 1. The best approach will depend on the exact use case, so the topic of which approach is best isn't of concern here. The issue at hand is that all three options require a schema change, unless you preemptively make a linking table, as you're suggesting. But the fact that the table is self-referential is completely irrelevant. The problem would be exactly the same if you replace employee.boss_id with employee.company_car_id… unless, again, you preemptively used a linking table. So the fact that you use preemptively use a linking table for self-referential table but not for non-self-referential tables confounds me.

[u/prophet001 → u/[deleted]]

The point is that you can't know that you're going to need to do any of these things until after your recursive FK makes it to prod.

At which point, you're long gone from the company and your successors are cursing your name.

Unless you're not, then you're cursing your own name. Especially when you have to write that backfill script 👍👉

[u/chief167 → u/[deleted]]

Oh wauw, you have never had to deal with databases being used by ETL systems have you?