MENU
Database Design and Normalization
Database design is the discipline of turning a set of real-world data requirements into a schema of tables, columns, keys, and relationships that MySQL can store and query reliably. A well-designed schema avoids storing the same fact in more than one place, which in turn avoids the update, insert, and delete anomalies that redundant data causes: a fact changed in one row but not another, a fact that cannot be recorded until an unrelated fact exists, or a fact that disappears when an unrelated row is deleted.This chapter works through the standard design process: modeling the entities and relationships in the problem domain, expressing the rules that must hold between columns as functional dependencies, and then applying a series of increasingly strict normal forms to remove redundancy from the resulting tables. It closes with two decisions every working schema eventually faces: when to denormalize on purpose, and how to choose between surrogate and natural primary keys.
- Entity-Relationship Modeling — entities, attributes, relationships, cardinality, and translating an ER model into MySQL CREATE TABLE statements.
- Functional Dependencies — the X → Y notation that every normal form is defined in terms of.
- First Normal Form (1NF) — atomic column values, no repeating groups.
- Second Normal Form (2NF) — eliminating partial dependencies on a composite key.
- Third Normal Form (3NF) — eliminating transitive dependencies.
- Boyce-Codd Normal Form (BCNF) — a stricter version of 3NF.
- Fourth and Fifth Normal Forms (4NF, 5NF) — multi-valued dependencies and join dependencies.
- Denormalization — when and how to deliberately reintroduce redundancy.
- Surrogate vs. Natural Keys — AUTO_INCREMENT, UUID, and Generated Invisible Primary Keys compared to business keys.