A customer changes their email, and your UPDATE touches 47 rows — one per order they ever placed. Miss one and the database now holds two 'truths'. That bug wasn't in the query; it was in the schema, planted the day someone stored the email 47 times.
What you'll learn
What
Normalization is a series of increasingly strict rules — normal forms — for organizing columns into tables so every fact is stored exactly once. You'll walk one messy table through First, Second, and Third Normal Form (1NF, 2NF, 3NF), see the update/insert/delete anomalies each step eliminates, and learn why analytics systems sometimes walk deliberately back down the ladder (denormalization).
Why
Redundancy is where data corruption breeds: every duplicated fact is an extra copy that can drift out of sync. Normalized schemas make inconsistency structurally impossible instead of procedurally avoided. And as an analyst you live downstream of these decisions — reading an ERD, predicting which JOINs you'll need, and recognizing when a 'weird' schema is actually 3NF doing its job are daily skills. It's also a top-five SQL interview topic.
Where it's used
Every OLTP schema design review, data-modeling interviews, debugging 'two sources of truth' incidents, and — in reverse — the star schemas of analytics warehouses, which denormalize on purpose and expect you to know what they traded away.
Where this runs in production
Payment ledgers are strictly normalized — an account balance derived from immutable ledger entries, never stored redundantly — because a drifted duplicate in financial data is an incident, not a quirk.
Order lines store the price paid alongside a product reference — controlled, deliberate redundancy — because the product's CURRENT price keeps changing while the historical fact of what you paid must not.
Ingestion pipelines land source systems in normalized form for fidelity, then dbt models JOIN them into wide denormalized marts — both halves of this lesson, operating as an assembly line.
Storing a friend's phone number in every text thread you share would mean a number change forces edits everywhere — miss a thread, dial a dead number. Your phone stores the number ONCE, in a contact card, and threads just point at the contact. Normalization is exactly this: each fact gets one home (its own table), and everything else references it by key. Update the contact card, and every thread is instantly right.
First Normal Form: every cell holds ONE value, and there are no repeating column groups (item1, item2, item3…). A cell containing 'Mouse, Keyboard, Webcam' fails — you can't JOIN on it, index it, or count it without string surgery. Fix: one row per value, or a separate child table. If you've ever fought a comma-separated column with SPLIT_PART, you've paid the 1NF-violation tax personally.
Second Normal Form (assumes 1NF): every non-key column must depend on the WHOLE primary key, not part of it. This only bites with composite keys. In order_items(order_id, product_id, quantity, product_name), the key is (order_id, product_id) — but product_name depends on product_id ALONE. That's a partial dependency: the product's name is re-stored for every order containing it. Fix: move product facts to a products table; order_items keeps only what depends on the pair (like quantity).
Third Normal Form (assumes 2NF): non-key columns may not depend on OTHER non-key columns. In customers(customer_id, city, country), if city determines country, then country depends on customer_id only THROUGH city — transitively. Berlin's country is stored once per Berlin customer, and a typo makes Berlin/Germany coexist with Berlin/Genmany. Fix: cities(city, country) table, customers keep the city reference. The classic summary: every non-key column depends on the key (1NF), the whole key (2NF), and nothing but the key (3NF) — 'so help me Codd'.
-- The 3NF destination for our e-commerce data CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, unit_price NUMERIC(10,2) NOT NULL ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT NOT NULL REFERENCES customers, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE order_items ( order_id INT REFERENCES orders, product_id INT REFERENCES products, quantity INT NOT NULL, price_at_purchase NUMERIC(10,2) NOT NULL, PRIMARY KEY (order_id, product_id) );
The schema you've queried all course IS the normalized answer: each entity in its own table, relationships via foreign keys, the order_items junction resolving the many-to-many. Note price_at_purchase — deliberate, principled redundancy: the price PAID is a fact about the order line (historical), distinct from the product's current price. Normalization forbids storing the SAME fact twice, not two different facts that happen to look alike.
'We denormalized for performance' is legitimate engineering. 'The schema grew a copy of customer_name in six tables and nobody remembers why' is rot. The difference is a single authoritative source plus a rebuild path: warehouse marts can be dropped and regenerated from normalized sources at any time. If redundant copies are hand-maintained by application code, you've re-invited every anomaly this lesson exists to kill. Rule: normalize the system of record; denormalize only derived, regenerable layers.
The same order data at each rung of the ladder. Click each stage to see which anomaly it cures — and what warehouses do at the end.
A startup tracks everything in one table. Before fixing it, name what will go wrong — anomaly-spotting is the skill interviews probe first.
Read the redundancy: Anna's email twice, the Mouse's price twice. UPDATE anomaly — Anna's new email must change in rows 1 AND 2. INSERT anomaly — a new 'Webcam' product can't exist until ordered. DELETE anomaly — remove order 3 and Ben is gone from the business entirely. Each duplicated fact is a drift waiting for a deadline.
-- Anomalies found: -- UPDATE: customer email stored once per order (rows 1,2) -- INSERT: cannot add a product with zero orders -- DELETE: deleting order 3 erases customer Ben
Your task
A flat table enrollments_flat(student_id, student_name, course_id, course_title, instructor) has the composite key (student_id, course_id). course_title and instructor depend on course_id alone — a 2NF violation. Fill in the blanks to split out a courses table and rebuild a clean enrollments table.
student_id | course_id | course_title | instructor
------------+-----------+--------------+------------
1 | C01 | SQL Basics | Rivera
1 | C02 | Statistics | Chen
2 | C01 | SQL Basics | Rivera
(3 rows)Write your solution in the editor on the right, then hit Run.
A table stores a customer's email once per order they placed. Changing the email requires updating many rows, and missing one leaves conflicting values. Which anomaly is this?
A cell contains 'Mouse, Keyboard, Webcam'. Which normal form does this violate, and why does it matter?
order_items(order_id, product_id, quantity, product_name) has key (order_id, product_id). What makes product_name a 2NF violation while quantity is fine?
In customers(customer_id PK, name, city, country) where city determines country, what does 3NF prescribe?
What's the correct pushback?
You've normalized employees_flat(emp_id, emp_name, dept_name, dept_head) into departments(dept_id, dept_name, dept_head) and employees(emp_id, emp_name, dept_id). Write the audit query proving no drift existed: for each dept_name in employees_flat, count DISTINCT dept_head values, returning only departments where that count exceeds 1 (i.e. the flat table already held contradictory copies). Return dept_name and head_versions, ordered by dept_name.
Explain 1NF, 2NF, and 3NF to me with one running example.
Take enrollments(student_id, courses_csv, course_titles, advisor) where courses_csv holds 'C01,C02'. 1NF: atomic cells — split to one row per (student, course); now the key is composite (student_id, course_id) and rows are countable and joinable. 2NF: every non-key column must depend on the WHOLE key — course_title depends on course_id alone (partial dependency), so it moves to a courses table; the enrollment row keeps only pair-facts like a grade. 3NF: non-key columns can't depend on each other — if advisor is determined by the student's department (student → dept → advisor), that transitive chain splits into a departments table. The mnemonic ties it together: every non-key column depends on the key (1NF), the whole key (2NF), and nothing but the key (3NF). Each step kills a concrete anomaly: multi-value surgery, mass-update renames, and drifted duplicates respectively.
When would you deliberately denormalize, and what guardrails do you insist on?
I denormalize derived, read-heavy layers — never the system of record. The canonical case is an analytics mart: dashboards running hundreds of queries a day shouldn't each pay a four-table JOIN, so a pipeline pre-joins orders, customers, and products into a wide table (or a star schema) at build time. The guardrails make it safe: (1) a single writer — the pipeline, never humans or application code hand-maintaining copies; (2) a rebuild path — the mart can be dropped and regenerated from the normalized source at any time, so drift is bounded by one refresh cycle; (3) the normalized source remains the arbiter when numbers disagree. Without those three, 'denormalization' is a euphemism for reintroducing update anomalies. I'd also mention measured need: denormalizing is an optimization, so I want evidence — query patterns, latency budgets — before paying the storage and freshness costs.
A colleague argues 'storage is cheap, JOINs are annoying — why normalize at all in 2026?' Respond.
Storage cost was never the point — CONSISTENCY is. Normalization is what makes contradictory data structurally impossible: with the email stored once, 'which copy is right?' cannot arise; with it stored per-order, every update is a distributed transaction across N rows that application code must get right forever. The anomalies are concrete business bugs: renamed products that revert on old orders, customers erased by deleting their last order, geography typos coexisting with correct values. So for OLTP — concurrent writers, row-level updates — 3NF stays the default in 2026, and the JOIN 'annoyance' is the receipt for write-safety. Where the colleague is RIGHT is read-optimized derived layers: warehouses denormalize aggressively because a pipeline is the only writer and rebuilds cure drift. The mature position isn't normalize-everything or nothing: normalize where data is written, denormalize where it's read, and keep an arrow from the second back to the first.
Common Mistakes to Avoid
1) Packing lists into cells ('Mouse, Keyboard') — a 1NF violation that taxes every later query with string surgery. 2) Confusing 2NF and 3NF — 2NF is about depending on PART of a composite key; 3NF is about non-key→non-key chains; single-column keys can only have 3NF problems. 3) 'Normalizing away' legitimate historical facts like price_at_purchase — the price paid and the current price are different facts; deleting the column rewrites history on every sale. 4) Denormalizing the system of record instead of a derived layer — redundancy without a single writer and rebuild path resurrects every anomaly. 5) Reciting forms without naming anomalies — interviewers want the WHY (update/insert/delete anomalies), not a memorized ladder.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5 normalization with the contact-card analogy, then quiz me on the three anomalies.' • 'Give me a messy table and make me walk it to 3NF, checking each step.' • 'I'll name a column, you tell me if it's a partial, transitive, or legitimate dependency.' • 'Debate me: I'll argue for One Big Table, you defend 3NF — then switch sides.' • 'Interview mode: ask me when denormalization is appropriate and press on the guardrails.'
Glossary
Normalization — structuring tables so every fact is stored exactly once. Redundancy — the same fact stored in multiple places; the raw material of drift. Update / Insert / Delete anomalies — the three failure modes of redundant schemas: partial updates, facts you can't add alone, facts destroyed as collateral. 1NF — atomic cells, no repeating groups. Composite key — a primary key of multiple columns. Partial dependency — a non-key column determined by part of a composite key; banned by 2NF. Transitive dependency — key → A → B chain between non-key columns; banned by 3NF. Functional dependency (X → Y) — knowing X fixes exactly one Y; the formal language of normal forms. BCNF — stricter 3NF for overlapping candidate keys. Denormalization — deliberately reintroducing redundancy in derived, read-optimized layers. Star schema — warehouse pattern: central fact table ringed by wide dimension tables. System of record — the authoritative (normalized) source other layers are rebuilt from.
Recommended Resources
• Read: 'Database Design for Mere Mortals' (Hernandez) — the gentlest serious treatment of normalization. • Watch: Decomplexify's 'Learn Database Normalization' on YouTube — the clearest 1NF→BCNF walkthrough available. • Reference: Kimball Group's dimensional modeling articles for the denormalized (star schema) side of the story. • Practice: find a spreadsheet you actually use, list its functional dependencies as arrows, and split it to 3NF on paper — then write the JOIN that reconstructs it. • Next in DSM: normalized schemas mean JOINs everywhere — Indexes & Query Optimization shows how databases keep those JOINs fast, and how EXPLAIN reveals what your query really costs.
Recap
✓ Redundancy breeds the three anomalies: update (drifted copies), insert (facts that can't exist alone), delete (collateral erasure). ✓ 1NF: atomic cells, no repeating groups — packed lists defeat COUNT, JOIN, and WHERE. ✓ 2NF: non-key columns depend on the WHOLE composite key — partial dependencies (product_name in order_items) split off. ✓ 3NF: non-key columns depend on nothing but the key — transitive chains (city → country) split into reference tables. ✓ Redundant-looking ≠ redundant: price_at_purchase vs unit_price are different facts; keep historical truth. ✓ Normalize the system of record; denormalize only derived, regenerable layers (marts, star schemas) with a single writer and rebuild path. Next up: Indexes & Query Optimization. Normalization gave you JOIN-heavy schemas that are safe to write — now learn the B-trees, EXPLAIN plans, and sargability rules that make them fast to read.
Run your code to see the output here.