Notesdata-models🌱 seedling
Normalization
Also: Database Normalization · Normal Forms · Denormalization · Update Anomaly
Store each fact in exactly one place and refer to it by a meaningless ID. Changes then touch one row and copies can't disagree; the price is joins on read.
Why it exists
If a company's name is copied into every row that mentions it, renaming the company (Facebook became Meta in October 2021) means updating every copy. Miss one and the data contradicts itself: an update anomaly.
How it works
- Shared entities (companies, cities, products) get their own table; other rows hold a foreign-key ID.
- The ID has no meaning to people, so it never needs to change, even when everything human-readable about the entity does.
- The normal forms (1NF, 2NF, 3NF, BCNF) formalize "one fact, one place"; in practice the test is "if this fact changed, how many rows would I update?"
- Denormalization deliberately copies data into the shape one read needs, e.g. a Materialized View, a search index or a precomputed timeline (Fan-Out on Write).
Trade-offs
| Normalized | Denormalized | |
|---|---|---|
| Writes | one row per fact, no anomalies | update every copy or accept drift |
| Reads | joins reassemble data | already assembled |
| Fits | OLTP with changing facts | read-heavy paths, analytics, caches |
In practice
- Relational schemas normalize by default; document schemas denormalize by default and reference when data is shared or unbounded.
- A safe denormalization is Derived Data: rebuilt from one source of truth, not edited independently.
Pitfalls
- Denormalizing without a single owner and a way to rebuild the copies; drift is silent.
- Over-normalizing read-hot paths until every page needs a dozen joins.
Connections
- Part of
- Data Models
- Learn first
- Relational Model
- Contrasts with
- Star Schema
- Mentioned
- Fan-Out on Write
Recall
These cards are also in your review deck, scheduled with spaced repetition.