Normalization
1NF to 3NF, explained by the update, insert and delete anomalies that each form removes from a schema.
Normalization is a set of rules for arranging columns so that each fact is stored in exactly one place. Every normal form exists to remove a specific class of anomaly: an update that has to be applied in several rows to stay correct, an insert that cannot happen because unrelated data is missing, and a delete that destroys information you meant to keep.
First normal form
One value per column per row, and no repeating groups. A tags column holding 'sql,mysql,index' breaks it, and so do phone1, phone2, phone3. The cost is concrete: LIKE '%sql%' cannot use an index, 'mysql' matches 'mysqlite', and no foreign key can point at a value inside a string. The fix is a child table.
Second normal form
In 1NF, and no non-key column depends on only part of a composite key. If order_items(order_id, product_id) also stores product_name, that name depends on product_id alone. Renaming the product means updating every line that ever sold it — the update anomaly.
Third normal form
In 2NF, and no non-key column depends on another non-key column. An orders table carrying customer_id and customer_city has city depending on the customer, not the order. Delete a customer's last order and their city disappears with it — the delete anomaly.
What trips people
Normalization is about functional dependencies, not table count. And a snapshot is not a violation: the price charged on an order is a different fact from the product's price today, and it belongs in the order row permanently.
Example
-- BEFORE: one wide table. Every anomaly at once.
CREATE TABLE orders_flat (
order_id BIGINT UNSIGNED NOT NULL,
line_no INT NOT NULL,
customer_email VARCHAR(255) NOT NULL,
customer_city VARCHAR(100) NOT NULL, -- 3NF: depends on the customer
product_sku VARCHAR(32) NOT NULL,
product_name VARCHAR(200) NOT NULL, -- 2NF: depends on the sku alone
tags VARCHAR(255) NOT NULL, -- 1NF: 'gift,fragile' in one column
qty INT NOT NULL,
PRIMARY KEY (order_id, line_no)
);
-- AFTER: each fact recorded once, in the table its key identifies.
CREATE TABLE customers (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
city VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_customers_email (email)
) ENGINE=InnoDB;
CREATE TABLE products (
sku VARCHAR(32) NOT NULL,
name VARCHAR(200) NOT NULL,
PRIMARY KEY (sku)
) ENGINE=InnoDB;
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NOT NULL,
placed_at DATETIME NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
REFERENCES customers (id) ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE order_items (
order_id BIGINT UNSIGNED NOT NULL,
line_no INT NOT NULL,
product_sku VARCHAR(32) NOT NULL,
qty INT UNSIGNED NOT NULL,
-- Kept deliberately: what this customer was actually charged. It is not
-- a copy of products.price, it is a fact about this line.
price_cents INT UNSIGNED NOT NULL,
PRIMARY KEY (order_id, line_no),
KEY idx_order_items_sku (product_sku),
CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
CONSTRAINT fk_items_product FOREIGN KEY (product_sku) REFERENCES products (sku) ON DELETE RESTRICT
) ENGINE=InnoDB;When to use it
- Diagnose why one product name appears three different ways across historical orders.
- Split a spreadsheet-shaped import table into something a foreign key can protect.
- Decide whether a column belongs on the order or on the customer.
- Explain to a reviewer which anomaly a proposed table split actually removes.
More examples
Reaching 1NF: a comma list becomes a table
The standard many-to-many split, and the clearest case for 1NF because the payoff is measurable rather than theoretical: correct matching instead of substring matching, an index instead of a scan, and a foreign key that stops a typo from inventing a tag. Note the join table needs no surrogate id — the pair of foreign keys is the key.
-- The multi-valued column. Filtering it is a full scan and a substring bug
-- waiting to be filed: 'sql' matches the row tagged 'nosql'.
-- SELECT * FROM posts WHERE tags LIKE '%sql%';
CREATE TABLE tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_tags_name (name)
) ENGINE=InnoDB;
CREATE TABLE post_tags (
post_id BIGINT UNSIGNED NOT NULL,
tag_id INT UNSIGNED NOT NULL,
-- The composite key IS the fact: this post carries this tag, once.
PRIMARY KEY (post_id, tag_id),
KEY idx_post_tags_tag (tag_id),
CONSTRAINT fk_pt_post FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
CONSTRAINT fk_pt_tag FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- Exact matching, index-backed, and renaming a tag is now one UPDATE.
SELECT p.title
FROM posts p
JOIN post_tags pt ON pt.post_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE t.name = 'sql';Seeing the anomalies before you fix them
Run these against a wide legacy table to turn an abstract argument into a row count. The first two queries are also worth keeping after the migration: if either ever returns rows again, a denormalized copy has been added somewhere and has started to drift.
-- Update anomaly: the same product name stored on many lines. If these
-- return more than one row, your copies have already drifted apart.
SELECT product_sku, COUNT(DISTINCT product_name) AS variants
FROM orders_flat
GROUP BY product_sku
HAVING variants > 1;
-- Same test for the 3NF violation: one customer, several cities on record.
SELECT customer_email, COUNT(DISTINCT customer_city) AS variants
FROM orders_flat
GROUP BY customer_email
HAVING variants > 1;
-- Delete anomaly, made visible: these customers exist only because they
-- have an order. Remove the order and the customer record is gone.
SELECT DISTINCT customer_email, customer_city
FROM orders_flat
WHERE customer_email NOT IN (SELECT email FROM customers);The join you accept in exchange
Worth reading next to the single-table version to judge honestly what normalization costs. On indexed foreign keys this join is cheap and the plan is stable; when it stops being cheap at scale, that measurement — not a hunch — is what justifies denormalizing a specific column.
-- Normalization moves work from write time to read time. This is the bill:
-- four tables to render one order, joined on indexed foreign keys.
SELECT o.id AS order_id,
c.email AS customer,
c.city,
p.name AS product,
oi.qty,
oi.price_cents -- the snapshot, not products.price
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.sku = oi.product_sku
WHERE o.id = 10427
ORDER BY oi.line_no;
Discussion