Bye Bye Foreign Keys

It’s incredibly common that there’s a relationship between tables in a relational database that’s best modeled with a foreign key (FK). However, sometimes you don’t want to sully the child table by adding a FK to it – it isn’t really a part of the table’s identity, it’s merely a relationship.

If you’re gonna pursue an idea, you might as well pursue it ad absurdum so what would a codebase be like if all FKs were replace with “through tables” (aka many-to-many tables)? Don’t be distracted by the migration effort or required boilerplate or painful ergonomics of through tables. All of those problems are either incidental to the thought experiment or an indictment of the quality of your tools.

I personally think it would be pretty interesting. I can imagine it being psychologically easier to add relationships between tables – it might even take on a graph database vibe. Hard to know. You could also imagine it being trivial to add indexes to enforce many-to-one and one-to-one relationships so you wouldn’t lose out on those properties.