Chapter 18 — Key Takeaways
The big idea
Turning an ER model into a schema is rule-application, not invention. A small set of mapping rules turns any diagram into correct tables, keys, and foreign keys — and a faithful mapping produces a schema whose natural joins answer every question (theme #1 paying off).
The mapping rules
| ER element | Maps to |
|---|---|
| Strong entity | a table (key → primary key) |
| Composite attribute | component columns |
| Multi-valued attribute | a child table (FK to owner) |
| 1:N relationship | a foreign key on the "many" side (nullability ← participation; choose ON DELETE) |
| M:N relationship | a junction table (composite PK of the two FKs) |
| Relationship attributes | columns on the junction (e.g., grade, quantity) |
| 1:1 relationship | a UNIQUE FK on one side, or merge tables |
| Weak entity | a table with a composite key including the owner's |
| Self-reference | a FK to the same table (manager_id, parent_category_id) |
Inheritance (a deliberate choice — Case Study 2)
- Single-table: one table +
type+ nullable subtype columns. Best for few distinct attributes, queried together. (Mercado'spayments.) - Table-per-subclass: base table + one table per subtype (1:1). Best when subtypes have many distinct attributes and integrity matters (subtype columns can be
NOT NULL). Cost: a join per object. - Table-per-concrete: a full table per subtype, no base. Rare; needs
UNIONfor "all". - Choose by: number of distinct subtype attributes + how you query (together vs. separately).
The big mistakes
- M:N without a junction (FK/array/CSV) — always a junction.
- FK on the wrong side of a 1:N — it goes on the many side.
- Relationship attributes on an entity instead of the junction.
- Multi-valued attribute as repeated columns (
phone1/2/3) — make a child table. - Defaulting to single-table inheritance → forty nullable columns and no per-subtype integrity.
You can now…
- ☐ Map entities, all attribute kinds, and all relationship cardinalities to tables/keys.
- ☐ Place 1:N foreign keys correctly and resolve M:N with junctions.
- ☐ Put relationship attributes where they belong.
- ☐ Choose and implement an inheritance mapping.
- ☐ Turn a full ER diagram into working, well-constrained DDL.
Looking ahead
Chapter 19 — Normalization. Now that you can build tables from a model, you'll learn the theory that says whether they're well-formed: functional dependencies, 1NF→BCNF, and the update/insert/delete anomalies redundancy causes. Home of the normalization-disaster anchor.
One sentence to carry forward: Apply the mapping rules faithfully — FK on the many side, a junction for every M:N with its attributes on it, and a deliberately chosen inheritance mapping — and a clean schema falls right out of a good model.