From Conceptual Model to Tables: Preserving Meaning During Design

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:

1. Can we insert the smallest legal object?
2. Which invalid states does the schema reject?
3. Can common pages be answered without reconstructing hidden meaning?
4. Can one relationship occur twice accidentally?
5. 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.

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)
);