35. Normalization: 1NF, 2NF, 3NF, and when to denormalize

What normalization is actually for

Normalization is a set of formal rules for organizing tables to eliminate redundant data and the update anomalies that redundancy causes. It's not an aesthetic preference — each "normal form" fixes a specific, concrete failure mode. This lesson walks through the failure modes first, then names the rule that fixes each.

The starting problem: one flat table

Imagine pagila's rental data stored as one giant flat table instead of the normalized schema you've been using:

rental_id | customer_name | customer_email | film_title | film_rating | rental_date

This looks convenient — no joins needed! — but it has three concrete, serious problems:

• Update anomaly: a customer changes their email. You must find and update every row that customer appears in (potentially hundreds of rentals). Miss one, and you now have contradictory data about the same customer inside one table.
• Insertion anomaly: you can't record a new film in the catalog until someone actually rents it — there's no film row independent of a rental row, so "add a film" and "record a rental" have been incorrectly fused into one operation.
• Deletion anomaly: delete a customer's only rental, and you've silently deleted all record that customer ever existed — customer data was never stored independently of rental data.

Every normal form below is a rule that, once satisfied, makes one or more of these three anomalies structurally impossible.

First Normal Form (1NF): atomic values, no repeating groups

A table is in 1NF if every column holds a single, atomic value — no comma-separated lists crammed into one field, no repeating groups of columns (phone1, phone2, phone3).

-- Violates 1NF: a list crammed into one column.
customer_id | favorite_films
1           | 'Academy Dinosaur, Ace Goldfinger, African Egg'

-- 1NF-compliant: one row per (customer, favorite film) fact.
customer_id | film_id
1           | 1
1           | 2
1           | 5

This should look familiar — it's the same shape as film_actor, a proper bridge table (see the ER-modeling lesson). Cramming a list into one column makes "find everyone who likes Academy Dinosaur" require string parsing instead of a plain WHERE/JOIN — 1NF exists specifically so every value stays queryable with ordinary SQL.

Second Normal Form (2NF): no partial dependency on a composite key

Applies specifically to tables with a composite primary key. 2NF requires every non-key column to depend on the whole key, not just part of it.

-- Composite PK: (film_id, actor_id). Violates 2NF: film_title depends
-- ONLY on film_id, not on the full (film_id, actor_id) pair.
film_id | actor_id | film_title        | actor_name
1       | 10       | Academy Dinosaur  | Johnny Cage

-- Fixed: film_title moves to the film table, where it properly depends
-- on the WHOLE key (film_id alone, which is film's own full PK).

This is exactly why pagila's film_actor table (composite key (actor_id, film_id)) has no columns besides the two foreign keys and a timestamp — any attribute that depended on only one half of that key would violate 2NF, so pagila's designers correctly kept title in film and first_name/last_name in actor, not duplicated into the bridge table.

Third Normal Form (3NF): no transitive dependency

3NF requires every non-key column to depend directly on the key — not on another non-key column, which itself depends on the key (a "transitive" dependency, key → A → B).

-- Violates 3NF: rating_description depends on rating, which depends on
-- film_id — a transitive chain, not a direct dependency on film_id.
film_id | title            | rating | rating_description
1       | Academy Dinosaur | PG     | Parental Guidance Suggested

-- Fixed: rating_description belongs in its own small lookup table,
-- keyed by rating — film only stores the rating CODE, not the
-- redundant-per-film description text.

This is the formal justification for lookup/reference tables in general — country and city in pagila are exactly this: address stores city_id, not the city name and country name duplicated into every single address row.

Boyce-Codd Normal Form (BCNF): a stricter version of 3NF

BCNF fixes a narrow edge case 3NF misses: every determinant (a column, or set of columns, that determines another column's value) must itself be a candidate key. In practice, most schemas that satisfy 3NF already satisfy BCNF — this is a refinement for specific edge cases (typically tables with multiple overlapping candidate keys) rather than a rule you'll reach for constantly. Know it exists and what it tightens; most real-world modeling decisions stop meaningfully changing once 3NF is satisfied.

When to deliberately denormalize

Normalization optimizes for write correctness and no redundancy — it does not optimize for read speed. A fully normalized query answering "total revenue per film category" might require joining five or six tables (you've written exactly this kind of query throughout Modules 4-7). For a report run constantly, that repeated join cost is real. Denormalization — deliberately reintroducing redundancy — trades some write-side risk (the anomalies above) for read-side speed, and is a legitimate, common production technique when:

• A materialized view (Module 9) precomputes and caches the joined/ aggregated result, refreshed periodically — the canonical normalized tables still exist underneath; denormalization lives only in the cache, not the source of truth. This is the safest, most common form of denormalization in practice.
• A reporting/analytics database (the "OLTP vs. OLAP" distinction, if you want the term to search for) is deliberately structured differently from the normalized transactional schema, because its read patterns are fundamentally different.
• A specific, measured performance problem (diagnosed with Module 10's EXPLAIN ANALYZE workflow) can't be solved any other way, and the team explicitly accepts the added write-complexity/redundancy-risk as a tradeoff — not a default, reached for only after normalization-preserving fixes (indexing, query rewriting) have been tried.

The rule of thumb this course follows: normalize by default, denormalize deliberately, and document why whenever you do — a redundant column with no comment explaining its purpose is indistinguishable from a bug to the next person who touches that schema.

Check yourself

1. Which normal form specifically prevents "a list of values crammed into one column"?
2. In a table with composite key (order_id, product_id), a column customer_name depends only on order_id. Which normal form does this violate, and why?
3. Give one legitimate, deliberate reason to denormalize data in production, and name the safeguard that keeps it from becoming an unmanaged mess.