Part 4 of 5 · 1 min read
From diagram to design decision
Why drawing the diagram first catches design problems before any table exists.
From diagram to design decision
Think of sketching a building's floor plan before pouring any concrete, so a mistake costs an eraser, not a demolition.
In plain words: drawing the ER diagram first forces a decision about every relationship's cardinality before any table is created. A many-to-many relationship, decided at this stage, signals that a junction table will be needed. A one-to-many relationship signals a simple foreign key will do.
A worked example. Deciding that Customer to Book is many-to-many, at the diagram stage, correctly predicts that order_items will be needed as a junction table, before a single CREATE TABLE statement is written.
The key lines. No new code; the next module covers translating this diagram into actual tables.
For the curious ER diagrams versus schema diagrams, again
Level 0 noted that an ER diagram is drawn before tables exist, and a schema diagram is drawn after. This module is where that earlier claim becomes concrete: every cardinality decision made here becomes a specific foreign key, or a specific junction table, in the next module's translation.
Quick notes
- An entity is a real-world thing worth tracking; an attribute is one of its properties; a relationship connects entities.
- Cardinality is one-to-one, one-to-many, or many-to-many, describing how many instances can participate on each side.
- A many-to-many relationship signals a junction table will be needed later.
- A one-to-many relationship signals a simple foreign key will do.
- An ER diagram is drawn before tables exist, to catch design decisions early and cheaply.