Normalization

The Problem Normalization Solves

 one denormalized table:

 orders: order_id, customer_name, customer_city, product, price

 anomalies hiding in it:
 UPDATE anomaly  customer moves → update 500 order rows,
                 miss one → contradictory data
 INSERT anomaly  can't record new customer without an order
 DELETE anomaly  delete last order → customer vanishes from existence
 
 root cause: FACTS STORED MULTIPLE TIMES drift apart.
 normalization = each fact stored ONCE, in exactly one place.

The Normal Forms That Matter (1NF–3NF)

 1NF — atomic cells, no repeating groups:
   ✗ orders(phone_numbers = "555-1, 555-2")
   ✓ separate phones table, FK to owner

 2NF — no partial dependency (composite keys):
   ✗ order_items(order_id, product_id, product_name)
     product_name depends on product_id ALONE, not whole key
   ✓ move to products table

 3NF — no transitive dependencies:
   ✗ orders(zip, city)      city determined BY zip
   ✓ city lives in zips table; orders reference zip

 practical reading: 3NF ≈ "every non-key column depends on
 the key, the whole key, and nothing but the key."

What You Buy

Benefit	Mechanism
Consistency	Single source per fact — nothing drifts
Update economy	Change once, not everywhere
Smaller storage	No repeated text blobs
Schema clarity	Entities and relationships made explicit
Flexible queries	Data answers unanticipated questions

The Price You Pay

 normalized reads often need REASSEMBLY:

 order + items + product + customer = 4-table join
 vs denormalized single-row read.

 write-heavy + read-light workloads: normalization wins big.
 read-heavy dashboards: joins cost latency → denormalize
 selectively (next lesson) or materialize views.

Normalization in Practice

 the workflow that works:

 1. normalize to 3NF by default          (correctness baseline)
 2. measure real query patterns          
 3. denormalize ONLY measured hot paths  
    (with invalidation/update discipline!)
 4. document every deliberate redundancy:
    "orders.customer_name copied for invoice rendering;
     updated via trigger on customers change"

 undiscussed redundancy is how data corruption ships with a smile.

Beyond 3NF (know they exist)

 BCNF:  stricter 3NF edge cases (rarely bites in practice)
 4NF/5NF: multi-valued/join dependencies — academic mostly
 6NF:   everything split — the world of temporal/anchor modeling
        and Datomic-style designs; niche
 
 stopping at 3NF is correct for 95% of production schemas.

Interview Framing

“Design the schema” scored beats: produce 3NF-shaped tables unprompted, EXPLAIN one normalization decision aloud (“price lives on order_items not products because prices change”), then volunteer when you’d denormalize and how updates stay consistent. Candidates reciting forms without anomaly examples miss the point; candidates who never denormalize miss reality. Show both instincts.