Advanced Normalization

Reviewed & published by Brayan K

By the end of this lesson you'll be able to look at a messy table, name exactly which normal form it breaks, explain the insert/update/delete anomaly it causes, and split it into a clean set of tables — right up to BCNF, 4NF and 5NF — while knowing when to deliberately denormalize for speed.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Recap 1NF, 2NF and 3NF as one decomposition
• Spot insert, update and delete anomalies
• Take a table to BCNF (every determinant is a key)
• Remove multi-valued dependencies with 4NF
• Recognise the rare join dependency that needs 5NF
• Denormalize on purpose — and know the trade-off

Our Worked Example: one badly-normalized table

Almost every idea below points back at this one fat enrollments table. It records who is enrolled, who teaches, and where that professor sits — all jammed into a single row. Read it, then watch us break it apart step by step.

Notice the redundancy: Adler and office B-204 repeat on every Databases row, and Mia Cole's name repeats for each course she takes. That duplication is the root of every anomaly we're about to meet.

What Normalization Actually Is

Normalization is the process of organising columns into tables so that each fact is stored in exactly one place. You do it by following a ladder of normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF), each removing a more subtle kind of redundancy than the last.

The engine behind every rule is the functional dependency, written X -> Y: "if you know X, you always know Y". For example professor -> prof_office means a professor has exactly one office. A candidate key is any minimal set of columns that uniquely identifies a row. Most rules boil down to one demand: everything should depend on the key.

Storing a customer's address on every order is like writing your home address on every letter in your house. Move once, and you'd have to find and correct hundreds of copies. Normalization keeps the address on a single sticky note (the customers row); everything else just points to it.

1. Quick Recap: 1NF, 2NF, 3NF

You met these in the beginner track, so here's the fast version. Each form fixes one problem: 1NF makes every cell hold a single value (no lists or repeating groups). 2NF removes partial dependencies — when a column depends on only part of a composite key. 3NF removes transitive dependencies — when a non-key column depends on another non-key column.

Applied to our messy table, the 3NF result is three tidy tables, each owning its own facts:

-- A quick recap of the first three normal forms.
-- Start with one fat table that holds an entire order on each row:
--   orders(order_id, customer_name, customer_city,
--          product, qty, price, line_total)

-- 1NF — every cell holds ONE value (no lists, no repeating groups).
-- 2NF — every non-key column depends on the WHOLE primary key,
--        not just part of a composite key.
-- 3NF — no non-key column depends on another non-key column
--        (no transitive dependencies).

-- The 3NF result of that fat table is three clean tables:
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_city VARCHAR(100)
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date  DATE
);

CREATE TABLE order_lines (
    order_id   INT REFERENCES orders(order_id),
    product    VARCHAR(100),
    qty        INT,
    price      DECIMAL(10,2),       -- depends only on product, really
    PRIMARY KEY (order_id, product)
);
-- Each fact now lives in exactly ONE place.

After 3NF, customer_city lives once per customer and price lives once per product. Change a city, and you change exactly one row.

2. The Three Anomalies (the "why")

Why bother splitting tables at all? Because a badly-normalized table makes three kinds of mistakes easy and sometimes unavoidable. These are the insert, update and delete anomalies — the problems every normal form exists to prevent.

-- The three anomalies a badly-normalized table suffers from.
-- Picture the fat "enrollments" table further down this lesson:
--   enrollments(student_id, student_name, course, professor, prof_office)

-- 1) UPDATE anomaly
--    Professor "Adler" moves office. Her office is repeated on every
--    row she teaches, so you must update MANY rows. Miss one and the
--    database now disagrees with itself.

-- 2) INSERT anomaly
--    A brand-new professor is hired but has no students yet. You cannot
--    record her office at all without inventing a fake student row,
--    because student_id is part of the primary key.

-- 3) DELETE anomaly
--    The last student drops a course. Deleting that row also erases the
--    only record that the professor and her office ever existed.

-- Normalization removes all three by giving every fact its own table.

Your Turn: name the violation

Fill in the two blanks. They're concepts, not SQL — name the kind of dependency and the normal form it breaks. The answers are in the comments so you can check yourself.

-- 🎯 YOUR TURN — fill in the blanks (they are concepts, not code).
-- Table: invoices(invoice_id, customer_id, customer_zip, customer_city)
-- Fact about the data: a ZIP code always determines its city
--                      (customer_zip -> customer_city).

-- 1) customer_city does not depend on the key (invoice_id); it depends on
--    customer_zip, which is itself a non-key column. That is a
--    ___ dependency.                 -- 👉 one word: what kind of dependency?

-- 2) A dependency like that one is exactly what ___ forbids.
--                                    -- 👉 which normal form? (e.g. 1NF / 2NF / 3NF)

-- ✅ Expected: 1) transitive   2) 3NF
--    (Fix: move zip -> city into its own zip_codes table.)

3. BCNF — A Stricter 3NF

Boyce-Codd Normal Form tightens 3NF into one clean rule: for every functional dependency X -> Y in the table, X must be a candidate key. A table can satisfy 3NF and still hide a determinant that isn't a key — BCNF is the form that catches it.

The textbook trap is a table where two columns can each play the role of "the thing being scheduled". Below, professor -> course holds, but professor isn't a key, so the table is 3NF but not BCNF:

-- BCNF (Boyce-Codd Normal Form) — the "3.5NF" most schemas should hit.
-- Rule: for EVERY functional dependency X -> Y, X must be a candidate key
-- (something that could uniquely identify a row). 3NF allows a few leaks;
-- BCNF plugs them.

-- The classic trap: course scheduling.
--   teaching(student_id, course, professor)
--   * (student_id, course) -> professor   (the candidate key determines prof)
--   * professor -> course                 (each professor teaches ONE course)
-- The second dependency breaks BCNF: "professor" decides "course" but
-- "professor" is NOT a candidate key. This is a 3NF-but-not-BCNF table.

-- Fix: split so each determinant becomes a key in its own table.
CREATE TABLE professor_course (
    professor VARCHAR(100) PRIMARY KEY,   -- professor -> course
    course    VARCHAR(100) NOT NULL
);

CREATE TABLE student_professor (
    student_id INT,
    professor  VARCHAR(100) REFERENCES professor_course(professor),
    PRIMARY KEY (student_id, professor)
);
-- Now every determinant (professor; student_id+professor) is a key. BCNF. ✅

Here's the same idea as before/after tables. The left table lets the same professor's course drift between rows; the BCNF split makes that impossible.

4. 4NF — Independent Multi-Valued Facts

A table is in 4NF when it has no multi-valued dependency except on a key. A multi-valued dependency (MVD) appears when one key independently determines several values of two unrelated attributes. Cram both lists into one table and you get a cartesian product of meaningless combinations.

Below, a student has many hobbies and many languages, and the two have nothing to do with each other. Storing them together forces fake pairings:

Two hobbies times two languages becomes four rows that imply combinations nobody asserted:

-- 4NF — eliminate multi-valued dependencies (MVDs).
-- An MVD is when one key independently determines MULTIPLE values of
-- two unrelated attributes. Cramming both into one table multiplies rows.

-- Bad: student_id  ->>  hobby    AND   student_id  ->>  language
-- 2 hobbies and 2 languages explode into 4 meaningless combinations.

-- 4NF fix: one table per independent multi-valued fact.
CREATE TABLE student_hobbies (
    student_id INT,
    hobby      VARCHAR(50),
    PRIMARY KEY (student_id, hobby)
);

CREATE TABLE student_languages (
    student_id INT,
    language   VARCHAR(50),
    PRIMARY KEY (student_id, language)
);
-- Add a hobby? Insert ONE row, not one-per-language. No false pairs.

Your Turn: split the table

Same shape as the example, new data. An employee has many skills and many certifications. Finish the two tables so each list stands on its own (that's 4NF). The expected answers are in the comments.

-- 🎯 YOUR TURN — decompose a multi-valued mess.
-- One table is trying to store TWO independent lists for each employee:
--   employee_facts(emp_id, skill, certification)
-- An employee has many skills AND many certifications, and the two lists
-- have nothing to do with each other. Storing both together forces a
-- cartesian product (3 skills × 2 certs = 6 noisy rows).

-- Finish the TWO tables that store each list on its own (this is 4NF):

CREATE TABLE employee_skills (
    emp_id INT,
    ___ VARCHAR(50),                  -- 👉 the column for ONE skill
    PRIMARY KEY (emp_id, skill)
);

CREATE TABLE employee_certifications (
    emp_id INT,
    certification VARCHAR(50),
    PRIMARY KEY (emp_id, ___)         -- 👉 the column that completes the key
);

-- ✅ Expected: skill   in the first table,
--              certification   in the second table's PRIMARY KEY.

5. 5NF — Join Dependencies (the rare one)

5NF (also called Project-Join Normal Form) handles a corner case: a table that can only be rebuilt correctly by joining three or more tables, where no single pair is enough. The signal is a three-way relationship whose facts are genuinely independent two-at-a-time.

-- 5NF (PJ/NF) — eliminate join dependencies that aren't implied by keys.
-- It applies to a rare case: a table that genuinely must be rebuilt by
-- joining THREE or more tables, where no pair alone is enough.

-- Classic example: which agent sells which brand for which company,
-- where the three facts are independent two-at-a-time.
--   bad: sales(agent, company, brand)   -- can introduce false triples

-- 5NF fix: three binary relationship tables instead of one ternary table.
CREATE TABLE agent_company (
    agent   VARCHAR(50),
    company VARCHAR(50),
    PRIMARY KEY (agent, company)
);

CREATE TABLE company_brand (
    company VARCHAR(50),
    brand   VARCHAR(50),
    PRIMARY KEY (company, brand)
);

CREATE TABLE agent_brand (
    agent VARCHAR(50),
    brand VARCHAR(50),
    PRIMARY KEY (agent, brand)
);
-- Reconstruct by joining all three. No spurious rows can appear.
-- In practice 5NF is rare — most real schemas stop at BCNF.

6. Denormalization — Breaking the Rules on Purpose

Normalization optimises for correctness and write efficiency. Sometimes you trade a little of that back for read speed by deliberately duplicating data — that's denormalization. It is a tuning decision, not a design default.

✅ Denormalize when:

Reads vastly outnumber writes and JOINs are the measured bottleneck — dashboards, analytics, reporting tables, cached aggregates.

❌ Don't denormalize when:

Writes are frequent and consistency is critical — financial ledgers, inventory, anything where a stale copy is a real bug.

Start normalized (BCNF), measure, then denormalize only the proven bottlenecks. Premature denormalization is a maintenance trap: every cached copy is something you must remember to keep in sync.

-- DENORMALIZATION — deliberately adding redundancy back, for speed.
-- After measuring (never before!), you may copy a value to avoid a JOIN
-- on a hot read path. Here we cache each order's total on the order row:

ALTER TABLE orders ADD COLUMN order_total DECIMAL(12,2);

-- The total still LIVES in order_lines (the source of truth). order_total
-- is a cached copy you must keep in sync on every write — usually with a
-- trigger or in application code:
UPDATE orders o
SET order_total = (
    SELECT SUM(qty * price) FROM order_lines l WHERE l.order_id = o.order_id
);

-- Trade-off: dashboard reads get fast (no SUM + JOIN), but every line
-- change now has to update two places. You bought read speed with
-- write complexity. Only worth it where reads vastly outnumber writes.

Common Errors (and the fix)

• Over-normalizing until reads crawl: splitting every attribute into its own table means a simple screen needs eight JOINs. Normalize for correctness, then stop; denormalize only measured hot paths.
• Missing a transitive dependency: a column like customer_city that depends on customer_zip (not on the key) is easy to overlook. Ask of every non-key column: "does this depend on the key, or on another column?" If the latter, it's a 3NF violation.
• Confusing BCNF with 3NF: a table can be 3NF yet break BCNF when a non-key column is a determinant. The BCNF test is stricter: every determinant must be a candidate key, no exceptions.
• Forcing 4NF/5NF everywhere: these forms only apply to genuine multi-valued or independent ternary relationships. Splitting an ordinary table "to be safe" just adds JOINs for nothing.
• Denormalizing without a sync plan: a cached order_total that no trigger or code keeps current silently rots. If you duplicate a value, you own keeping every copy in step.

Frequently Asked Questions

Q: What's the practical difference between 3NF and BCNF?

3NF only restricts dependencies on non-key columns. BCNF goes further and insists every determinant (anything on the left of X -> Y) is a candidate key. The gap shows up when a non-key column determines part of a key — rare, but real.

Q: How far should I normalize a real database?

BCNF is the practical target for most schemas. Go to 4NF if you have genuinely independent multi-valued facts. 5NF is needed only for unusual ternary relationships — most teams never reach for it deliberately.

Q: Isn't denormalization just bad design?

No — it's a deliberate, measured trade-off. The mistake is denormalizing first. Design normalized, prove a read bottleneck with real numbers, then add a controlled, kept-in-sync copy to fix it.

Q: How do I find a transitive dependency by eye?

Walk every non-key column and ask "does this depend on the primary key, or on some other column?" If it depends on another non-key column (like city depending on zip), that's transitive — move the pair into its own table.

Mini-Challenge: Normalize to BCNF

Put it all together — a brief, a blank canvas, and the expected decomposition in the comments. Write the two CREATE TABLE statements, then sanity-check them in a playground.

-- 🎯 MINI-CHALLENGE — normalize a messy table all the way to BCNF.
-- You are given ONE table that already breaks BCNF:
--   bookings(room_id, time_slot, instructor, class_name)
-- Facts about the data:
--   * (room_id, time_slot) -> instructor, class_name   (the candidate key)
--   * instructor -> class_name   (each instructor runs exactly one class)
--
-- The dependency  instructor -> class_name  breaks BCNF, because
-- "instructor" is not a candidate key. Split the table so every
-- determinant becomes a key in its own table.
--
-- 👉 Write two CREATE TABLE statements:
--    1) instructor (PK) -> class_name
--    2) (room_id, time_slot) -> instructor
--
-- ✅ Expected decomposition:
--    CREATE TABLE instructors (
--        instructor VARCHAR(100) PRIMARY KEY,
--        class_name VARCHAR(100) NOT NULL
--    );
--    CREATE TABLE bookings (
--        room_id    INT,
--        time_slot  VARCHAR(20),
--        instructor VARCHAR(100) REFERENCES instructors(instructor),
--        PRIMARY KEY (room_id, time_slot)
--    );

-- your CREATE TABLE statements here

🎉 Lesson Complete

• ✅ Redundancy causes the insert, update and delete anomalies — normal forms remove it
• ✅ 1NF/2NF/3NF: atomic cells, full-key dependency, no transitive dependencies
• ✅ BCNF demands that every determinant is a candidate key
• ✅ 4NF separates independent multi-valued facts; 5NF handles rare join dependencies
• ✅ Denormalize only after measuring — and keep every copy in sync
• ✅ Next: Indexing Internals — how the database finds rows fast

Practice quiz

Continue this course

• Previous: Final Project – Build a Full Production-Ready SQL Database System
• Next: Indexing Internals: B-Trees, Hash Indexes, Bitmap Indexes & When to Use Them — Understand index data structures and choose the right index for each query
• Quick reference: SQL cheat sheet
• From the blog: SQL Database Design Best Practices