Referential Integrity and Relationship Mistakes

The single biggest advantage of a relational database over a flat file or a spreadsheet is the ability to link tables through primary and foreign keys, then join across those links to retrieve information that no single table holds on its own. Link the CD and Distributor tables in the Stories on CD database, and suddenly you can list a CD's title right alongside the name of the distributor that supplies it — two facts that live in two different tables, joined together on demand.

Creating that relationship does more than enable a join, though — it also lets you enforce referential integrity: the guarantee that a change on one side of a relationship never leaves the other side pointing at something that no longer makes sense. That means preventing a distributor from being deleted while CDs still reference it, and it means changes on one side propagating correctly to the other when they're supposed to. Get relationships and referential integrity right, and the database actively protects you from bad data. Get them wrong, and the mistakes tend to be quiet ones — not crashes, just data that slowly stops making sense.

The mistakes in this lesson fall into a few different categories: relationships that shouldn't exist at all, foreign keys and referential integrity applied in the wrong place or unnecessarily, and structural relationship patterns that actively work against the database's ability to enforce integrity in the first place.

In the Stories on CD project database, there is no legitimate reason to create a relationship between the Category and Distributor tables — a CD's category and its distributor have nothing to do with each other. Even so, the temptation to insert a CategoryID field into the Distributor table as a foreign key is one some designers can't resist, usually in the name of convenience: "then I can get category and distributor info in one query."

That convenience isn't worth the cost. The two tables genuinely have nothing to do with each other, and a relationship that doesn't reflect a real business connection just adds noise and false structure to the schema. If you need category and distributor information together in the same view, build that view from the CD table's existing links to both Category and Distributor — the CD table is the actual connection point between them, and it already exists.

When you build a one-to-many relationship, you only need one foreign key: the primary key of the table on the "one" side, inserted into the table on the "many" side. There's a common instinct to also insert the "many" table's primary key back into the "one" table, as if the relationship needs a foreign key running in both directions to be complete. It doesn't. A one-to-many relationship works perfectly well as a single foreign key pointing one direction — adding a second, reverse-direction key doesn't strengthen the relationship, it just adds a redundant field that has to be kept in sync with nothing to actually gain from the effort.

A closely related mistake, and one of the more damaging ones on this list: skipping actual foreign key constraints in the database and handling relationship logic entirely in application code — ORM validations, business-layer checks, that sort of thing. It's an understandable shortcut. It's also fragile in a way that eventually catches up with every project that relies on it.

Application-level checks only run when the application is the thing touching the data. A bug in that validation logic, a direct insert run manually against the database, or a batch migration script that bypasses the application entirely — any of these will happily write data the application would have rejected. The predictable result is orphan records: an Order row pointing at a Customer_ID that no longer exists, silently sitting in the database because nothing at the schema level ever stopped it from being created or from being orphaned later.

The fix is simple to state, if occasionally inconvenient to implement: the database has to be the final authority on referential integrity, not a suggestion the application happens to enforce. Real foreign key constraints mean invalid data physically cannot be stored, no matter what wrote it or how it got there.

It's worth stating the opposite caution too: just because two tables are related doesn't automatically mean you should enforce referential integrity between them. Enforcing a relationship, like creating an index, costs real processing time — more noticeably so as tables grow large — so it's worth choosing deliberately rather than reflexively.

Suppose Stories on CD had a business rule: only list a distributor if they've actually supplied at least one CD the company ordered. In that case, enforcing referential integrity between the CD and Distributor tables makes real sense — it's the mechanism that prevents someone from entering a distributor Stories on CD has never actually ordered from. But if no such rule exists, enforcing that same referential integrity would be actively wrong: it would prevent users from entering potential distributors into the Distributor table before any order has been placed with them, which is a perfectly legitimate thing to want to do. The right level of enforcement depends entirely on what the business rule actually is — not on the mere fact that a relationship exists.

Instead of building a proper junction table for a many-to-many relationship, it's tempting to just store a comma-separated list of IDs in a single text column — a Movies table with a Genre_IDs column holding something like "1, 4, 12". It looks efficient. It's a serious mistake, and it's the same underlying problem as a smaller-scale one you'll also run into at the single-attribute level.

Consider a Hobbies field holding "sail boarding, skydiving, knitting". That's the identical violation of First Normal Form — one field, multiple distinct values crammed together — just applied to a single descriptive attribute instead of an entire relationship. Whether it's one field or a whole many-to-many relationship being flattened this way, the consequences are the same category of problem, just at different scale.

For the full many-to-many case specifically, the damage is severe: comma-separated IDs completely break indexing, so a query like "find every movie in Genre 4" can't use an index at all — it has to scan and parse every row's text field by hand. Worse, you cannot enforce referential integrity on a comma-separated list. A genre can be deleted from the Genres table while its ID remains permanently hardcoded, unenforced, inside movie records that now reference nothing.

The fix, at either scale, is the same principle: resolve the many-to-many relationship with a proper junction table (Movie_Genres, with real foreign keys to both parent tables), and split a multi-valued single attribute into its own related table rather than a delimited string. Real tables, real foreign keys, real indexes — not string parsing standing in for structure.

A subtler structural mistake: a child table trying to belong to multiple different types of parent table using a generic Entity_ID column plus an Entity_Type string to indicate which kind of parent it's pointing at — an Images table, say, that can point at a User, a Product, or an Article, all through the same two generic columns.

It's easy to see the appeal: one Images table instead of three nearly-identical ones. But you cannot create a valid foreign key constraint on Entity_ID, because it doesn't consistently point to any single, specific table — its meaning depends entirely on the value in a separate column. That ambiguity strips out database-level referential integrity for that relationship entirely; the database has no way to guarantee an Entity_ID actually corresponds to a real row anywhere.

Two real fixes exist. Exclusive arcs: give the child table multiple nullable foreign keys, one per possible parent type, with a constraint ensuring exactly one of them is ever populated at a time. Or implement a proper supertype/subtype relationship (table inheritance), where a shared parent table holds common attributes and each specific type extends it. Both approaches cost a bit more schema complexity than a single generic Entity_ID column — and both actually let the database enforce the integrity that a polymorphic association gives up entirely.

A different structural problem: Table A has a foreign key pointing to Table B, Table B has a foreign key pointing to Table C, and Table C has a foreign key pointing back to Table A. This is not the same mistake as the "complete the circuit" issue above — that one was an unnecessary redundant key on an otherwise-correct two-table relationship. A circular dependency is a genuine structural flaw spanning three or more tables, and it creates a real insertion problem, not just an inefficiency.

Specifically, it creates a chicken-and-egg problem: you can't insert a row into Table A until a corresponding row exists in Table C, but Table C requires a row in Table B first, and Table B requires a row in Table A — the exact row you were trying to create in the first place. Deletions face the same tangle in reverse. Usually, when you find a genuine circular dependency in a design, it's a signal of a deeper problem: either a single entity got artificially split across too many tables, or a relationship that should have been optional got mandatory constraints applied to it when it shouldn't have. The fix is to break the cycle at its actual design flaw — not to work around it with deferred constraints or careful insertion ordering, which just papers over the underlying structural mistake.

A quieter mistake: creating a separate table that shares the exact same primary key as its parent table, just to hold a handful of extra attributes — a Users table and a User_Preferences table, for instance, with no real reason the preferences couldn't just live as columns on Users itself.

The cost isn't dramatic on any single query, but it's persistent: every time you need both the user and their preferences together — which, in practice, is most of the time — the database has to perform a join it wouldn't otherwise need, for no real structural benefit. There are legitimate reasons to split a table one-to-one: genuinely massive data like BLOBs or images that shouldn't bloat every row of the main table, strict security requirements like isolating salary data behind separate access controls, or attributes that are frequently null and would otherwise leave the main table sparse. Absent one of those specific reasons, the simpler answer is almost always to just add the columns to the main table.

Too much normalization can make a database slower than it needs to be — but poor performance is rarely what actually kills a software project. Far more common culprits are designs too complex and confusing to build correctly, and designs that simply don't do what they're supposed to do. A database that fails to protect its own data's integrity falls squarely into that second category.

Normalization is one of the most effective tools available for protecting data against errors before they happen. If the database's structure simply refuses to let you make certain mistakes, you never have to clean up the bad data those mistakes would have produced. An extra join to pull related data from a properly separated table adds milliseconds to a query, at most — and it's genuinely hard to justify accepting inconsistent, unenforceable data just to save a user a second or two, once or twice a day.

None of this means every table needs to reach Fifth Normal Form. It does mean there's no real excuse for a table that isn't at least in Third Normal Form — getting a table to 3NF is straightforward enough that "it wasn't necessary" rarely holds up as a justification for skipping it.

A few concrete signals worth watching for: if application code has to parse a single field to extract multiple values (the Hobbies example from earlier is exactly this), break it into multiple fields, or split it into its own related table. If a table has several fields with near-identical names — JanPayment, FebPayment, MarPayment — that's a repeating group pretending to be separate columns; pull that data into its own table instead. If two rows might legitimately hold identical values across every visible field, figure out what actually makes them logically distinct and add that explicitly, so you have a real primary key rather than an accidental one. And if some of a table's fields don't actually depend on the entire primary key, consider whether that data belongs spread across multiple tables instead of crammed into one.

The next lesson describes mistakes associated with international issues.