Normalization & Schema Design
The problem
Storing the same fact redundantly in multiple rows invites contradictions — update one copy and forget another, and the database now contains two different answers to the same question. Normalization is a disciplined process for structuring tables so each fact is stored exactly once.
Why now
The relational model gave the vocabulary of tables and rows, but not a design discipline for what belongs in which table. Functions and relations already established that a function guarantees exactly one output per input — a functional dependency (this column determines that one) is exactly that guarantee, and normalization is the process of organizing tables around it.
Mental model
Normalization asks, for every column, 'what does this value depend on, and is that dependency captured by the table's own key?' If a column depends on something other than the whole primary key, it's stored in the wrong table and needs to move — each normal form is a progressively stricter version of that same one-fact-one-place question.
Requires
- The Relational Model & SQLDatabase Engineering
Normalization is a design discipline applied to relational tables specifically; you need to already be comfortable with tables, rows, and keys before reorganizing them according to normalization rules is meaningful.
- Functions & RelationsMathematical Thinking
A functional dependency — the core concept normalization is built around — is literally a function from one set of columns to another; the guarantee 'exactly one output per input' from functions-and-relations is the exact rule being enforced.
Used in
- Relational schema design for any application with structured, related data
- Preventing update anomalies where changing one fact requires touching multiple rows
- Deliberate denormalization for read performance, understood as a tradeoff against normalization's guarantees
- Database migrations that split or merge tables to fix a normalization violation discovered later
Projects
- Take a single denormalized table with repeated data (e.g. orders with customer name/address duplicated per row) and normalize it into properly related tables
- Identify and fix an update anomaly caused by denormalized data — change one fact and show how many rows would need updating before and after normalization
Examples
- Storing a customer's address on every one of their orders means updating their address requires updating every order row — normalizing moves it to a single customers table
- A composite key (order_id, product_id) determining quantity is a functional dependency: given both, quantity has exactly one correct value
Resources
Mastery checklist
- I can identify a functional dependency between columns in a table
- I can normalize a denormalized table through at least the first three normal forms
- I can explain an update anomaly caused by insufficient normalization
- I can explain when deliberate denormalization is a reasonable tradeoff