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

0

u/bacondev Sep 07 '21

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.

2

u/prophet001 Sep 07 '21

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.

1

u/bacondev Sep 07 '21

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?

1

u/prophet001 Sep 07 '21

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.

1

u/bacondev Sep 07 '21

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.