Dry-Fit vs. Fixed: Optional and Mandatory Relationships

Opening Scene

Before any glue touches a joint, a careful joiner dry-fits it — assembles the piece without adhesive, checks that everything lines up, and is free to pull it back apart if something’s off. A dry-fit assembly can exist perfectly well half-together, one board test-fitted, another left aside for now. But once the glue goes on and the clamps come off, the joint becomes fixed. A leg without its rail glued in isn’t a legitimate finished table anymore — that joint is now mandatory to the piece’s integrity, not optional.

That difference — a relationship that’s fine left empty for now, versus one a valid record genuinely cannot exist without — is exactly the difference between an optional and a mandatory relationship in database design.

In Plain English

An optional relationship means a row can legitimately exist without a related row on the other side — a customer record with no orders yet, an employee with no assigned manager because they’re the CEO. A mandatory relationship means a row cannot legitimately exist without its related row — an order line item without an order makes no sense; it must reference exactly one order to be valid at all. In practice, this distinction shows up as whether a foreign key column allows null values (optional) or is constrained to always have a value (mandatory), and it’s one of the most consequential decisions a data modeler makes, because it directly determines what “valid data” even means for that table.

The Old Way

Getting optionality wrong has caused genuine, recurring problems across decades of database design:

Marking a relationship mandatory when it’s actually optional forces awkward placeholder data — a fake “no manager” employee record, or a dummy order just to satisfy a not-null constraint — polluting the data with entries that don’t represent anything real.
Marking a relationship optional when it’s actually mandatory lets genuinely invalid data slip through — an order line item with no order at all, silently orphaned, undetectable until a report turns up numbers that don’t add up.
Getting this right has always required asking a business question, not a technical one: can this entity’s existence, as a matter of real-world fact, ever legitimately precede or exist without its related entity?

Optionality decisions are exactly the kind of subtle modeling judgment that’s easy to get wrong quietly and expensive to discover late.

What’s Changing (and Why AI Is the Reason)

AI-assisted schema tools can now flag likely optionality mistakes by analyzing actual data patterns — for example, noticing that a supposedly mandatory foreign key column has a suspiciously high rate of workaround placeholder values, a strong signal the constraint doesn’t match how the business actually uses the field.
Generative AI coding assistants writing schema migrations sometimes default foreign keys to nullable “to be safe,” quietly turning what should be a mandatory relationship into an optional one, a subtle mistake that doesn’t cause an error but does let invalid records accumulate undetected until a business rule downstream breaks.
AI agents generating or modifying records directly increasingly need explicit, well-enforced mandatory constraints to behave correctly, since an agent with no business context has no way to infer on its own that an order line item without an order is nonsensical — the constraint itself has to do that job.

The Metaphor, Fully Extended

Joinery Element	ER Modelling Concept
A dry-fitted joint, assembled loosely, free to be taken apart	An optional relationship — a nullable foreign key
A glued and clamped joint, now structurally required	A mandatory relationship — a not-null foreign key
A leg still waiting for its rail to be fitted	A valid row with no related row yet, permitted under an optional relationship
A rail glued into place with no leg to receive it	An invalid record that a mandatory constraint correctly prevents from existing
A joiner deciding, board by board, which joints must be fixed before the piece is trustworthy	A modeler deciding, relationship by relationship, which foreign keys must be not-null

For Beginners: What to Actually Do

For every relationship you model, explicitly ask and answer: can this row legitimately exist, even temporarily, with no related row on the other side?
Default to marking a foreign key mandatory (not-null) whenever the related row is genuinely required for the record to make sense, rather than leaving it nullable “just in case.”
Watch for placeholder or dummy records — a “none” customer, a “TBD” manager — as a strong sign a relationship was marked mandatory when it should have been optional.
When reviewing generated migration scripts, check every foreign key’s nullability explicitly rather than trusting the tool’s default.

For Practitioners and Leaders: The Deeper Layer

Treat optionality decisions as business-rule decisions, not technical defaults, and document the reasoning behind each one so it survives team turnover.
Periodically audit “mandatory” foreign key columns for suspicious placeholder value patterns, since this is a reliable signal the constraint doesn’t reflect real business behavior.
Set an explicit team standard for how AI coding assistants should handle foreign key nullability in generated migrations, rather than accepting default nullable columns silently.
Recognize that mandatory constraints are doing real protective work now that AI agents write directly to operational data without the contextual judgment a human data-entry user might apply.

Quick Recap

An optional relationship allows a row to exist without a related row on the other side; a mandatory relationship does not — the same distinction as a dry-fitted joint versus a glued, clamped, permanent one.
Optionality is fundamentally a business-rule decision, expressed technically as whether a foreign key permits null values.
Getting this wrong in either direction causes real problems: placeholder data pollution on one side, undetected invalid records on the other.
AI-generated migrations can default foreign keys to nullable without a clear business reason, making explicit review of optionality choices more important, not less.

Where This Fits in the Series

Article 3 covered hierarchies within a single table; this article covered whether a relationship must exist at all. Article 5 begins the series’ intermediate patterns with supertype and subtype modeling — one template, several related variants.