Part III — Database Design

"A good schema makes every query simple, every index obvious, and data integrity automatic. A bad schema makes everything hard — and no amount of clever SQL will save you."

If Part II taught you to speak SQL, Part III teaches you the skill that actually determines whether a database is a pleasure or a nightmare to work with: design. This is the book's first recurring theme, and it is the single biggest reason databases fail in practice. Most developers never learn it. They let an ORM generate their tables, or they grow a schema one panicked ALTER TABLE at a time, and five years later they are trapped in a structure that fights them on every feature.

Design is also where theory finally earns its keep. The relational model from Chapter 3 and the algebra from Chapter 4 become practical tools here: functional dependencies tell you exactly how to decompose a table; the difference between a one-to-many and a many-to-many relationship tells you whether you need a foreign key or a junction table.

What you will learn

  • Chapter 17 — Entity-Relationship Modeling. Drawing the blueprint before building: entities, attributes, relationships, cardinality, weak entities, and the design process from requirements to diagram. Both Chen and crow's-foot notation.
  • Chapter 18 — From ER Diagram to Tables. Translating a conceptual model into physical DDL: mapping entities, one-to-many and many-to-many relationships, and the three strategies for inheritance — with their trade-offs.
  • Chapter 19 — Normalization. The discipline that eliminates redundancy: functional dependencies, 1NF through BCNF, and the update/insert/delete anomalies that corrupt un-normalized data. Home of the book's normalization-disaster anchor.
  • Chapter 20 — Denormalization. The dirty secret: production databases are often deliberately denormalized for performance. When breaking the rules is right, the techniques, and the costs you accept when you do.
  • Chapter 21 — Data Modeling Patterns. Reusable solutions to problems that appear in every domain: audit trails, soft deletes, versioning, multi-tenancy, hierarchies (adjacency list, nested sets, closure table), tagging, and polymorphic associations.
  • Chapter 22 — Schema Migration & Evolution. Schemas change. Migration tools (Flyway, Liquibase, Alembic), safe vs. dangerous changes, zero-downtime migrations (expand-contract), and version control for your database.

Why this part matters

Chapters 19 and 20 together carry one of the book's most important lessons: normalization is not dogma — it is a tool with costs and benefits. You will take a flat, redundant Mercado "spreadsheet" table, watch its anomalies appear, normalize it step by step until they vanish, and then intentionally denormalize a reporting view — understanding exactly what you trade in each direction. This is the difference between someone who recites "third normal form" and someone who designs databases that last.

For every learning path

This part is essential for 💻 developers and 🏗️ DBAs, who live and die by schema quality. 🔬 CS students get the formal backbone (functional dependencies, normal-form proofs) that exams love. 📊 Analysts can read more lightly but should not skip Chapter 19 — understanding why a schema is shaped the way it is makes every query you write against it clearer, and denormalized reporting tables (Chapter 20) are the analyst's natural habitat.

By the end of Part III you will be able to look at a set of requirements and produce a schema you would be proud to hand to another engineer. That is a rare and valuable skill. Let's build it.

Chapters in This Part