03 · Normalization — Eliminate Redundant Data¶
Foundations · Topic 3 of 4
Why Normalize?¶
Redundant data causes update anomalies — inconsistencies that arise when you update one copy but miss others. Normalization structures your schema to have one authoritative place for each fact.
Normal Forms¶
1NF — First Normal Form¶
- Each column holds atomic (indivisible) values
- No repeating column groups; choose domains deliberately and model independently constrained relationships explicitly
-- ❌ Violates 1NF
customer_id | name | phones
1 | Alice | "555-1234, 555-5678"
-- ✅ 1NF
customer_phones(customer_id, phone)
2NF — Second Normal Form¶
- Must be in 1NF
- Every non-prime attribute is fully functionally dependent on each candidate key (no partial dependencies on any candidate key)
3NF — Third Normal Form¶
- Must be in 2NF
- No transitive dependencies (non-key attribute depends on another non-key attribute)
BCNF — Boyce-Codd Normal Form¶
- Stricter version of 3NF: for every nontrivial functional dependency
X → Y,Xmust be a superkey
Denormalization¶
In high-read systems, intentional denormalization trades storage for query speed:
- Precompute joins and store derived columns
- Used heavily in BigQuery (columnar, denormalized star/snowflake)
- Cassandra schema is designed query-first — tables are naturally denormalized
Cloud Comparisons¶
| DB | Normalization Strategy |
|---|---|
| PostgreSQL | Full normalization + views for convenience |
| Spanner | Interleaved tables to co-locate related rows |
| BigQuery | Denormalized wide tables; nested STRUCT/ARRAY instead of joins |
| Cassandra | One table per query pattern; intentional duplication |
| MongoDB | Embed vs. reference tradeoff per access pattern |
| DynamoDB | Design around access patterns; single-table and multi-table approaches are both valid |
Worked Example: Separate Facts, Preserve History¶
Start with order_lines(order_id, product_id, customer_id, customer_email, product_name, quantity, unit_price_at_purchase). Assume one line per product per order, so (order_id, product_id) is a candidate key.
| Dependency | Problem | Decomposition |
|---|---|---|
order_id → customer_id |
Partial dependency on the composite key | orders(order_id, customer_id) |
customer_id → customer_email |
Transitive dependency from order to current customer email | customers(customer_id, email) |
product_id → product_name |
Product fact repeated on every line | products(product_id, name) |
(order_id, product_id) → quantity, unit_price_at_purchase |
Fact belongs to the purchased line | Keep in order_items |
A product's current price and the price actually charged are different facts. Keeping unit_price_at_purchase is not accidental duplication. The same reasoning applies to a shipping-address snapshot: moving house must not rewrite the history of a delivered order.
A good decomposition is lossless: joining on declared keys reconstructs the intended facts without introducing spurious rows. Check dependencies against all candidate keys, not just the chosen primary key. The formal 3NF condition allows each nontrivial dependency X → A when X is a superkey or A is prime (belongs to some candidate key); the simpler rules above are useful starting heuristics.
Denormalize only with a named source of truth, an update mechanism, a staleness budget and a repair/rebuild plan. Arrays and JSON can be valid typed values; the key concern is whether independently queried relationships and constraints are modeled clearly, not whether a type is physically indivisible.
Check Your Understanding¶
Question 1
When is storing a customer's email on an order a distinct historical fact rather than accidental duplication?