Hidden Assumptions in Every Join
Every foreign key carries an assumption about how two entities relate. When those assumptions are wrong or undocumented, queries return incorrect results and migrations break silently.
Relationships are the connective tissue of your schema. A well-documented relationship makes clear whether a link is mandatory or optional, whether it represents ownership or reference, and what happens to dependent rows when a parent is deleted.
Relationship Review Checklist
Verify each relationship in your schema against these questions.
Is the Cardinality Correct?
Confirm whether the relationship is one-to-one, one-to-many, or many-to-many. Wrong cardinality leads to duplicate rows or lost data in joins.
Is the Foreign Key Enforced?
Check whether the database enforces referential integrity. Missing constraints allow orphaned rows that break application logic.
What Is the Deletion Policy?
Define what happens to child rows when a parent is deleted. RESTRICT, CASCADE, and SET NULL each carry different business implications.
Is the Relationship Mandatory?
Can a child row exist without a parent? Optional relationships (NULL foreign keys) have different query semantics than mandatory ones.
Can the Relationship Change?
Can a child row switch parents? If foreign keys are immutable, business processes that require reassignment need a different design.
Is There a Join Table?
Many-to-many relationships require a junction table. Verify that the junction table does not accidentally carry attributes that belong to one of the parent entities.
Documenting Relationship Intent
For every foreign key, document the following in your schema annotation:
- Business meaning: What real-world relationship does this foreign key represent?
- Cardinality: One-to-one, one-to-many, or many-to-many?
- Optionality: Can the foreign key be NULL? What does NULL mean in this context?
- Delete rule: RESTRICT, CASCADE, SET NULL, or SET DEFAULT?
- Downstream impact: Which queries, views, or stored procedures depend on this relationship?
Ready to verify your relationships?
Continue your schema-intent review with related materials.