CSE Roadmap

Core tier · about 3 hours · 5 parts

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.