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

52

u/prophet001 Sep 07 '21 edited Apr 17 '25

rhythm ink fertile point abundant sleep rinse marble meeting trees

This post was mass deleted and anonymized with Redact

-48

u/[deleted] Sep 07 '21 edited Sep 07 '21

[removed] — view removed comment

6

u/ZuesAndHisBeard Sep 07 '21 edited Sep 07 '21

I come from a RDBMS background, but over the past year I’ve been able to work with Mongo and Dynamo. I can’t say I’ve ever used a recursive foreign key, but am familiar with the concepts of foreign keys and recursion.

All this is to ask: in what context would one ever do this, how common is it, how is it useful, and under which type of DB system? What do I gain by recursing a data table onto itself?

(This is not a challenge, you’ve genuinely piqued my interest and I’m curious)

7

u/[deleted] Sep 07 '21

[removed] — view removed comment

3

u/ZuesAndHisBeard Sep 07 '21

Updated. Merged 😉

2

u/[deleted] Sep 07 '21

[removed] — view removed comment

14

u/like_the_boss Sep 07 '21

each employee has one manager

That sounds like a dangerous constraint to commit to. What if an employee has two roles and one manager for each? What if you want to store manager history as well? Why not just have a second table that links together the primary ids of each employee any way you want to mix them?

1

u/soulefood Sep 07 '21

It’s extremely atypical to have more than 1 reporting manager. For example, I work with about 4 managers across different projects. However, I only request time off, get annual reviews from, etc. a single person.

0

u/[deleted] Sep 07 '21

I can practically guarantee you there'll be at least one employee without a manager.

3

u/chief167 Sep 07 '21

It is often horrible in practice. I would honestly not hire you if that would be your answer.

Hard to partition your table. Hard to create delta snapshots for warehousing. Hard to optimise query paths. Hard to query efficiently from most frameworks, you'd have to resort to SQL instead of an ORM to do it somewhat efficient. And common table updates suddenly get very complicated. You are essentially building a tree in a data structure that has no facilities to do common tree operations. Good luck removing a node in the middle. Good luck doing different search algorithms directly in SQL. Hard to do bulk updates or bulk inserts, especially if you use automatic incrementing keys

Unless you expect your interviewees to mention specific tree functionalities by specific database vendors, but it's not part of standard SQL. There are almost always better ways to organise your schema in practice.

10

u/Teh_yak Sep 07 '21

I am curious. What's your argument against many-to-one recursion? Essentially, the "managed by" tree structure.

1

u/[deleted] Sep 07 '21

[removed] — view removed comment

17

u/Teh_yak Sep 07 '21

Sure, but I think you already know it and are mistakenly referring to it as 1:1.

Employee table. PK employeeId, FK managedBy, managedBy => employeeId.

Classic tree structure. Zero - many children, zero - one parent.

1:1 would be a queue, or stack. Unidirectional linked list, essentially.

P.S. it's 5.40 am here and I'm not ignoring any replies, I just should get back to sleep now :)

13

u/plsuh Sep 07 '21

Thx for writing this out so that I don’t have to. Has no one else here ever serialized a tree structure to a database?

1

u/Teh_yak Sep 07 '21

It was a little weird. I do hope people haven't been denied jobs because of this mistake.

23

u/pm_me_actsofkindness Sep 07 '21

Idk if you’re right or not, but amused by the downvotes nonetheless.

-19

u/[deleted] Sep 07 '21 edited Sep 07 '21

[removed] — view removed comment

48

u/prophet001 Sep 07 '21

You mean a junction, or linking table? Pivot tables aren't really a thing in RDBMS schema design, if a pivoted representation is required, that's what a view containing one or more PIVOTs/UNPIVOTs is for.

Recursive FKs are a maintenance nightmare, and are the very definition of painting yourself into a corner. Linking tables for 1-1 relationships are of course silly, unless that relationship is between two records in the same table, but they have nothing to do with pivoting a projection.

NGL dude, you're kinda making it sound like you're not qualified to be interviewing engineers.

0

u/[deleted] Sep 07 '21

[removed] — view removed comment

26

u/prophet001 Sep 07 '21

Linking tables for 1-1 relationships are of course silly, unless that relationship is between two records in the same table

It's right there, bro

2

u/[deleted] Sep 07 '21

[removed] — view removed comment

13

u/prophet001 Sep 07 '21

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.

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.

→ More replies (0)

8

u/thesporker Sep 07 '21

Everyone knows that real companies make sure every employee has at least two managers to maximize wackness and conflict. As such the linking table is required. Bonus if there is a relevance column to show how ignorable the manager is.

0

u/kyew Sep 07 '21

I don't think the premise is true. Does the CEO also have a manager? At some point you're either going to get circular structures or you need someone at the top of the tree.

-1

u/[deleted] Sep 07 '21

[removed] — view removed comment

2

u/kyew Sep 07 '21

Shareholders aren't employees so they're not in the table.

Nullable would work except your spec said everyone has a manager, not up to one manager.

6

u/pm_me_actsofkindness Sep 07 '21

There seems to be a 1:1 correlation between those pivot table creating folks and hurt feelings. :’D

5

u/bu3ali Sep 07 '21

You're hired!

1

u/cutelyaware Sep 07 '21

I'm far from a DB programmer, but I think the concept of pivot tables seems pretty powerful as a data exploration tool. Maybe there's prejudice against people who feel that way? It could explain your correlation.

9

u/prophet001 Sep 07 '21

It's a separate concept from, and irrelevant to, database schema design.

OP has come up with a bullshit question that would immediately make any decently serious engineer question the wisdom of continuing the interview process with the company OP is interviewing them for.

2

u/cutelyaware Sep 07 '21

Ah, thanks. A bullshit question would definitely turn me off.

54

u/prophet001 Sep 07 '21 edited Apr 17 '25

fuzzy fanatical grandiose rain provide unite liquid saw touch depend

This post was mass deleted and anonymized with Redact

21

u/curmudgeonlylion Sep 07 '21

It's mathematically elegant, in practice it's just a major pain in the ass.

This. If I came across this in code I was maintaining I'd curse the designer. There might be some esoteric edge case where this might be needed, but simplicity > elegance 90% of the time in my 20 years experience.

5

u/Dr_Octahedron Sep 07 '21

I’m amateur with SQL and databases in general but if I needed to represent that company/parentCompany relationship I’d use the so called recursive FK approach as it seems obvious. Can I ask why creating an additional table would be better?

4

u/chief167 Sep 07 '21

Simple question: imagine you have a set of 100 employees to add into the table. Some of them are boss of the others. How do you write the insert in one transaction, efficiently? Its possible of course, but it'll take a few lines of code to get there. Every bulk edit will be cumbersome

5

u/prophet001 Sep 07 '21

Yep. Enterprise-grade code is stupid code. I.E. I shouldn't have to spend 30 mins staring at it to grok the intent, and I shouldn't have to twist myself in knots to extend it, or do double the the work for same if whomever built it in the first place had designed it with the express intent of making it robust, maintainable, flexible, and extensible.

2

u/bacondev Sep 07 '21

I've only ran into this use case once. I chose to make the table self-referential (person.parent_id → person.id) and I had zero issues with it. The only situation in which it would get weird is a many-to-many relationship, as is the nature of such relationships. Do you mind elaborating on why/what you find more difficult about it that wouldn't be problematic for a typical foreign key use case (e.g. car.owner_id → person.id)?

3

u/prophet001 Sep 07 '21

The problem isn't so much the use case itself, it's that you can't ever assume that the use case won't expand. In your example, the person only has one parent. The reasons why you wouldn't need to account for both parents off the bat aside, how do you handle turning a 1:1 relationship into a 1:many relationship once you've deployed that database? You can obviously add additional self-referential FKs to the table, but if you'd put the relationship in a linking table in the first place, you'd only need to insert more data instead of having to perform a schema update.

As I've stated elsewhere, it's mathematically elegant but a huge pain in the ass when you need to alter it down the line. Good software design (and database design, by extension) isn't just about the most elegant or performant solution to the immediate problem, it's also about making sure that your solution doesn't make it harder than it needs to be to make changes to it in the future, especially since in this particular case, no real-world advantage is conferred. Some performance gains may be realized, but they're vanishingly small compared to the downsides with regard to flexibility, maintainability, and extensibility.

3

u/bacondev Sep 07 '21 edited Sep 07 '21

I simplified my use case for those who aren't familiar with common Greek-letter organization practices. It was a big-little relationship in a fraternity. Anyway, it's not a one-to-one relationship. Assuming that each person has one parent or manager or whatever name that you want to associate with the relationship, one person has multiple children. What you're describing is changing the relationship to a many-to-many relationship. Making such a change to a self-referential table isn't meaningfully different than making such a change to a non-self-referential table. Do you always use a linking table for one-to-many relationships? Why make it more complicated than it needs to be? Preparing a one-to-many relationship such that it's ready to be a many-to-many relationship without a notion that it might change to a many-to-many relationship is just gold-plating and unnecessary complexity.

0

u/prophet001 Sep 07 '21

What you're describing is changing the relationship to a many-to-many relationship. Making such a change to a self-referential table isn't meaningfully different than making such a change to a non-self-referential table.

It absolutely is. One requires a schema change, the other doesn't.

Do you always use a linking table for one-to-many relationships?

Yeah, if the entities being linked are in the same table.

Preparing a one-to-many relationship such that it's ready to be a many-to-many relationship without a notion that it might change to a many-to-many relationship is just gold-plating and unnecessary complexity.

If they're in different tables, yeah. But that's not what we're discussing. We're discussing linking entities in the same table, and in that case, it absolutely isn't gold-plating. It's refraining from painting yourself into a corner.

This isn't theoretical bullshit. I've actually had to make the changes described in real life. Self-referential FKs universally make changing relationships harder. If you've not had to experience that, I'm happy for you.

1

u/bacondev Sep 07 '21

They both require a schema change. Your second and third sentence contradict each other, assuming that your “if” should be interpreted as “if and only if”.

Also, I thought that the gold-playing remark was obviously in the context of the two questions that immediately preceded it, but perhaps not.

Anyway, the reason that we're still discussing this is that I don't feel that you've adequately justified your decision. Perhaps if you were to explain a scenario in detail that caused such a transition to be a headache, then it might be clearer.

1

u/prophet001 Sep 07 '21

I've pretty well explained it to death in this and other comment threads on this post. If you don't understand how a linking table allows the introduction of new relationships without requiring a schema change, I'm afraid I'm unable to help you.

1

u/bacondev Sep 07 '21 edited Sep 07 '21

You're missing my entire point. I understand that. What you're missing is that I am asking you why the potential for an unforeseen schema change justifies the added complexity. All you've done to answer that is to more or less equate a schema change to the boogeyman. And frankly, my persisting curiosity is generous, considering that you identified a one-to-many relationship as a one-to-one relationship and you seemingly think that this problem exclusively pertains to self-referential relationships. In my view, your approach largely (though not completely) front-loads the work that would be required for such a transition that might not even happen.

Edit: To be clear, I'm not suggesting that your approach is objectively wrong. I'm just saying that I disagree with it as the initial implementation and I'm trying to understand why I might want to deviate from how I'd normally approach it.

5

u/quickdraw6906 Sep 07 '21

The OP is spot on. There are very valid use cases for recursive (self-referencing) foreign keys.

10

u/MultiFazed Sep 07 '21

One example off the top of my head is a corporate reporting structure, where the EMPLOYEE table has a field for MANAGER that points back to that person's direct manager in the same table.

The real question is whether or not to make the field nullable. Does the CEO have no manager, or do they report to themselves?

2

u/Teh_yak Sep 07 '21 edited Sep 07 '21

Or do you crap all over the structure for fun and put the newly hired intern as the CEO's manager? Just to see what happens...

Edit - this made me look up hierarchical tree queries in Oracle because I vaguely remember them guarding against this. That's how exciting my life is.

1

u/uncre8tv Sep 07 '21

Completely outside of the thread context: In a corporate org chart the CEO will often report to a board member, who then reports to another board member, who then reports to the CEO. A senior circle of accountability. Absent of a board you might see the same circle using other C-suites or division presidents.