Normalization
1NF through BCNF, driven by the anomalies they prevent - and an honest word on when to stop.
Normalization restructures tables so that every fact is stored exactly once. It is not an academic ritual: each normal form eliminates a specific class of bugs — the anomalies — that a badly shaped table forces your application code to work around. Start from the entities you sketched during ER modeling, then check them against the rules below.
Consider a table a team created because "joins were annoying":
CREATE TABLE order_report (
order_id INT,
customer_name TEXT,
customer_email TEXT,
product_id INT,
product_name TEXT,
unit_price DECIMAL(10,2),
quantity INT,
PRIMARY KEY (order_id, product_id)
);Each row is one order line: an order with three products occupies three rows, each repeating the customer's name and email.
The three anomalies
Every argument for normalization reduces to three concrete failure modes:
| Anomaly | What happens in order_report |
|---|---|
| Update | A customer changes her email. It is repeated on every line of every order she ever placed; miss one row and the table now holds two contradictory emails. |
| Insertion | You want to catalog a new product before anyone orders it. You cannot: a row needs an order_id, so the product waits or gets a fake order. |
| Deletion | An order is cancelled and its rows deleted. If it was the only order containing product 42, that product's name and price vanish with it. |
These are not style complaints. Each one is defensive code you would otherwise have to write and test — multi-row updates, sentinel rows, "do not really delete" flags.
Functional dependencies
A functional dependency X -> Y means X determines Y: any two rows that agree on X must agree on Y. The dependencies in order_report are:
order_id -> customer_name, customer_email
product_id -> product_name, unit_price
(order_id, product_id) -> quantityThe key is the pair (order_id, product_id) — the only combination that determines every column. That composite key is what the next two normal forms interrogate.
First normal form: atomic values
1NF requires that every column hold a single atomic value and that there are no repeating groups — no products column containing "42:2, 17:1, 88:5", no product_1, product_2, product_3 columns. Such shapes make even basic queries ("which orders contain product 17?") string-parsing exercises. Our one-row-per-line table already satisfies 1NF; its problems live deeper.
Second normal form: no partial dependencies
2NF forbids a non-key column from depending on only part of a composite key. Here, product_name and unit_price depend on product_id alone, and customer_name and customer_email depend on order_id alone — both partial dependencies. Split the facts by what actually determines them:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_name TEXT,
customer_email TEXT
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name TEXT,
unit_price DECIMAL(10,2)
);
CREATE TABLE order_lines (
order_id INT REFERENCES orders,
product_id INT REFERENCES products,
quantity INT,
PRIMARY KEY (order_id, product_id)
);A product now exists independently of any order, and its name is stored once. The insertion and deletion anomalies for products are gone.
Third normal form: no transitive dependencies
One problem remains: customer_email still travels with every order. The chain is order_id -> customer -> customer_email — the email is a fact about the customer, not about the order. 3NF forbids such transitive dependencies, where a non-key column depends on another non-key column rather than on the key directly:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name TEXT,
email TEXT
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers
);Each table now states one kind of fact about one kind of thing — the data-layer cousin of the Single Responsibility Principle. Updating an email touches exactly one row.
BCNF in one paragraph
Boyce-Codd normal form tightens 3NF: every determinant — every left-hand side of a functional dependency — must be a candidate key. A 3NF schema can violate BCNF only in a narrow situation involving overlapping composite candidate keys, the classic example being a bookings table where both (room, time_slot) and (instructor, time_slot) are keys and instructor -> room. Treat this as an advanced check: most 3NF schemas are already in BCNF, and in the rare cases where they differ, decomposing to BCNF can cost you the ability to enforce some dependencies with keys alone.
When to stop
3NF is the practical default for OLTP systems — the databases taking orders and payments under concurrent writes. Beyond it, returns diminish fast. Denormalization — deliberately reintroducing redundancy for read speed — is a legitimate, documented trade-off, not a sin. The rule that matters: denormalize from a normalized design, not instead of one. A reporting table derived from 3NF sources can be rebuilt when it drifts; a schema that was never normalized has no source of truth to rebuild from. This is the same reasoning behind separate read models in microservice architectures.
Reach for it
- The schema backs a transactional system with frequent concurrent writes
- Multiple features or services update the same underlying facts
- Read patterns are still unknown and you need a neutral baseline
Avoid it
- The table is a derived, rebuildable read model for analytics or reporting
- A measured query is too slow and the redundant copy is documented and refreshed automatically
- The column is an immutable historical snapshot such as price at time of sale
Common mistakes
- Meaningless lookup tables. A status column holding "shipped" does not need a statuses table with surrogate id 3 unless statuses carry their own attributes. A CHECK constraint or enum enforces validity without an extra join.
- Treating normal forms as a purity contest. The goal is eliminating anomalies your system actually risks, not reaching the highest-numbered form.
- Denormalizing prematurely. "Joins are slow" is a hypothesis, not a fact. Well-indexed 3NF joins are fast in every mainstream database; measure before you duplicate data you will then have to keep consistent by hand.
Practice
A library tracks borrowing in one table: loans(loan_id, member_name, member_email, isbn, book_title, author_name, due_date), one row per borrowed copy. First, list its functional dependencies. Second, name one concrete update, insertion, and deletion anomaly. Third, decompose it to 3NF, showing each table and its key. Finally, the head librarian wants a nightly overdue report that would join four of your tables — decide whether to denormalize for it, and justify your answer in one sentence.