A diagram is useful only when it preserves decisions that matter in the database. Boxes and arrows are not the goal; explicit identity, cardinality, optionality, and lifecycle are.
Start from rules, not nouns
Consider a research platform:
- a paper has one venue, but a venue publishes many papers;
- a paper has one or more authors, and an author writes many papers;
- author order matters;
- a paper may cite many papers, including papers outside the local dataset.
The nouns suggest entities, but the verbs reveal relationships and constraints.
In plain terms: underline nouns to find possible tables, then underline verbs and quantities to discover what the tables must guarantee.
Identity comes before attributes
Choose an identifier that remains stable when display data changes. An author’s name is not an identity: names collide and change. A source-specific author ID may work internally, while a surrogate key plus source identifiers can support several providers.
Write down candidate keys before selecting the primary key. A primary key is a chosen candidate, not the only possible identity rule.
Map relationships systematically
| Conceptual relationship | Relational mapping |
|---|---|
| One-to-many | Foreign key on the many side |
| Optional one-to-many | Nullable foreign key, if absence is meaningful |
| Many-to-many | Junction table with both foreign keys |
| Relationship with attributes | Junction table plus those attributes |
| Recursive relationship | Foreign key back to the same table, or a junction table |
Paper authorship is not just a pair of IDs because author order belongs to the relationship:
CREATE TABLE paper_author (
paper_id bigint NOT NULL REFERENCES paper(paper_id),
researcher_id bigint NOT NULL REFERENCES researcher(researcher_id),
author_position integer NOT NULL CHECK (author_position > 0),
PRIMARY KEY (paper_id, researcher_id),
UNIQUE (paper_id, author_position)
);The first constraint prevents duplicate membership. The second prevents two authors from occupying the same position on one paper.
Optionality is a business rule
NOT NULL versus nullable is not a cosmetic choice. If a paper can be loaded before venue resolution finishes, venue_id may be temporarily nullable. If every published paper must have a venue before it becomes visible, model that state explicitly rather than accepting permanent ambiguity.
Similarly, cascade behavior should follow lifecycle. Deleting a venue should rarely delete its papers. Deleting a temporary import batch may legitimately delete all staging rows.
Validate with questions
A model is not finished when the diagram looks tidy. Test it against representative reads and writes:
- Can we insert the smallest legal object?
- Which invalid states does the schema reject?
- Can common pages be answered without reconstructing hidden meaning?
- Can one relationship occur twice accidentally?
- What happens when a referenced entity is deleted?
Review card
- Requirements provide rules; the diagram records them.
- Identity should survive display-data changes.
- Cardinality determines where foreign keys live.
- Relationship attributes belong on the junction table.
- Optionality and delete actions encode lifecycle.
- Validate a model with real operations, not aesthetics.