Normalization

Organizing tables so each fact is stored once: anomalies, first, second and third normal form, and when to denormalize on purpose.

Why normalize: one fact, one place

Normalization is the process of organizing columns into tables so that each fact is stored once, and every table describes one kind of thing. It was formalized by Edgar F. Codd, who introduced the relational model in 1970, as a series of normal forms: levels of design quality, each with a precise condition a table must meet. Each form includes the ones below it, so a table in third normal form is also in first and second.

The aim is not elegance for its own sake. Duplicated facts cost storage, but the real problem is that copies drift apart: when one copy changes and another does not, the database contradicts itself and nobody can tell which version is true. A normalized design makes those contradictions impossible by having nowhere to put them.

Anomalies: what goes wrong without it

A data anomaly is an inconsistency or loss of information caused by storing the same fact in several rows, or different facts in the same row. To see them, here is how many people's first attempt at an order table looks: one wide table, like a spreadsheet. 

CREATE TABLE orders_flat (
  order_id        INTEGER PRIMARY KEY,
  order_date      TEXT,
  customer_name   TEXT,
  customer_email  TEXT,
  customer_city   TEXT,
  products        TEXT
);

INSERT INTO orders_flat VALUES
  (101, '2026-06-02', 'Sanne de Vries', 'sanne.devries@example.com', 'Utrecht',
   'USB-C cable 2 m, Wireless mouse'),
  (103, '2026-06-18', 'Sanne de Vries', 'sanne.devries@example.com', 'Utrecht',
   'Mechanical keyboard'),
  (104, '2026-07-01', 'Mei Chen', 'mei.chen@example.com', 'Amsterdam',
   'Noise-cancelling headset, USB-C cable 2 m');

-- Sanne moves to Amersfoort; only her latest order is updated
UPDATE orders_flat SET customer_city = 'Amersfoort' WHERE order_id = 103;

SELECT DISTINCT customer_name, customer_city
FROM orders_flat
WHERE customer_email = 'sanne.devries@example.com'
ORDER BY customer_city;
customer_name   customer_city
--------------  -------------
Sanne de Vries  Amersfoort
Sanne de Vries  Utrecht

Sanne now lives in two cities. That is an update anomaly: one real-world change needed to be applied to several rows, and one was missed. The other two kinds follow from the same design:

  • Insert anomaly: you cannot record a new customer who has not ordered yet, because every row needs an order. You would have to invent a fake order.
  • Delete anomaly: removing a row removes more than you meant. If Mei's only order is deleted, the shop forgets that Mei exists at all: 
CREATE TABLE orders_flat (
  order_id        INTEGER PRIMARY KEY,
  order_date      TEXT,
  customer_name   TEXT,
  customer_email  TEXT,
  customer_city   TEXT,
  products        TEXT
);

INSERT INTO orders_flat VALUES
  (101, '2026-06-02', 'Sanne de Vries', 'sanne.devries@example.com', 'Utrecht',
   'USB-C cable 2 m, Wireless mouse'),
  (103, '2026-06-18', 'Sanne de Vries', 'sanne.devries@example.com', 'Utrecht',
   'Mechanical keyboard'),
  (104, '2026-07-01', 'Mei Chen', 'mei.chen@example.com', 'Amsterdam',
   'Noise-cancelling headset, USB-C cable 2 m');

-- Mei's only order is cancelled and removed
DELETE FROM orders_flat WHERE order_id = 104;

SELECT count(*) AS rows_about_mei
FROM orders_flat
WHERE customer_email = 'mei.chen@example.com';
rows_about_mei
--------------
0

All three anomalies have one cause: the table mixes facts about orders with facts about customers (and products). The cure is to give each kind of fact its own table.

Functional dependencies in plain words

Normal forms are defined in terms of functional dependencies, so the idea is worth a minute. A functional dependency A → B ("A determines B") means: whenever two rows have the same value for A, they must have the same value for B. Knowing A, you know B.

  • customer_email → customer_name: one email address belongs to one customer, so it fixes the name.
  • order_id → order_date: an order has one date.
  • product_id → name: a product has one name.
  • (order_id, product_id) → quantity: the quantity depends on both the order and the product.

Dependencies come from the meaning of the data, not from the rows you happen to have. Two customers in the sample both live in Utrecht, but city → customer is obviously not a rule. Normalization asks: does every column depend on the table's key — and on nothing else?

First normal form (1NF)

A table is in first normal form (1NF) when every cell holds a single, atomic value and there are no repeating groups — no lists in one cell, and no numbered columns such as product1, product2, product3. The products column in orders_flat breaks the rule: 'USB-C cable 2 m, Wireless mouse' is two values in one cell.

Why it hurts: there is no place for the quantity of each product, you cannot join to a product table, and searching needs fragile text matching. "Which orders contain the USB-C cable?" becomes WHERE products LIKE '%USB-C cable%', which breaks as soon as someone types "USB C cable". The fix is one row per order and product — exactly what the shop's order_items table does: 

SELECT oi.order_id, p.name, oi.quantity
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
WHERE p.name = 'USB-C cable 2 m'
ORDER BY oi.order_id;
order_id  name             quantity
--------  ---------------  --------
101       USB-C cable 2 m  2
104       USB-C cable 2 m  3
110       USB-C cable 2 m  1

"Atomic" means atomic for the questions you ask. A full name in one column is fine if you never sort by last name; the shop splits first_name and last_name because it does.

Second normal form (2NF)

A table is in second normal form (2NF) when it is in 1NF and every non-key column depends on the whole primary key, not on part of it. The rule only matters for tables with a composite key (a key of two or more columns).

Imagine order_items with an extra column:

order_id (key) product_id (key) quantity product_name
101 1 2 USB-C cable 2 m
104 1 3 USB-C cable 2 m
110 1 1 USB-C cable 2 m

quantity depends on the whole key (this order, this product). product_name depends on product_id alone — a partial dependency. The name is repeated in every order line, and renaming the product means updating them all. The fix: move product_name to the table whose key it depends on, products, and look it up with a join.

Third normal form (3NF)

A table is in third normal form (3NF) when it is in 2NF and no non-key column depends on another non-key column — there are no transitive dependencies (key → A → B).

Suppose orders stored the customer's city: order_id → customer_id → customer_city. The city depends on the order only through the customer. That is the update anomaly from the start of this page: Sanne's city sits in every one of her orders. Moving customer_city to customers stores it once per customer. A second classic case is an address with a postal code and a city: if, in your country, the postal code determines the city, then postal_code → city is a transitive dependency, and a strict 3NF design puts it in a postal-code table. Many designs accept that redundancy because addresses are entered as a whole and rarely change — a conscious trade-off, not an accident.

A well-known summary, based on a line by William Kent (1983): every non-key column must depend on the key (1NF), the whole key (2NF), and nothing but the key (3NF).

Boyce–Codd normal form, and beyond

Boyce–Codd normal form (BCNF) tightens 3NF: for every functional dependency A → B (where B is not part of A), A must be a candidate key (a set of columns that could serve as the primary key) or contain one. The difference only appears in tables with several overlapping candidate keys, and most tables in 3NF are also in BCNF. Fourth normal form deals with independent multi-valued facts and fifth normal form with tables that can only be rebuilt by joining three or more smaller ones; both are mostly of academic interest; in practice, "normalized" means 3NF.

The shop database, normalized

Apply all three rules to orders_flat and you arrive at the tables of the sample database: customers, orders, products, and order_items connecting orders to products.

Before: one table orders_flat with order id, order date, customer name, email and city, and a list of products in one cell. After: four tables. customers holds customer facts, orders holds the order date and a customer_id, order_items holds one row per order and product with quantity and unit price, and products holds product facts. Lines show that each customer has many orders, each order many order items, and each product many order items. Before orders_flat order_id order_date customer_name customer_email customer_city products (list) After (3NF) customers customer_id PK name, email city orders order_id PK customer_id FK order_date order_items order_id PK FK product_id PK FK quantity unit_price products product_id PK name, category price, stock 1 n 1 n 1 n
One wide table becomes four narrow ones. "1" and "n" mark the one-to-many relationships: one customer has many orders, one order many items, one product appears in many items.

Nothing is lost: a join reassembles the flat view whenever you need it, and now each fact has one home. Sanne's move is a single-row update, and every order shows the new city at once: 

UPDATE customers SET city = 'Amersfoort' WHERE customer_id = 1;

SELECT o.order_id, c.first_name, c.city
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.customer_id = 1
ORDER BY o.order_id;
order_id  first_name  city
--------  ----------  ----------
101       Sanne       Amersfoort
103       Sanne       Amersfoort

unit_price: a deliberate historical copy

At first sight, order_items.unit_price looks like a 3NF violation: isn't the price already in products? It is not the same fact. products.price is the price today; unit_price is the price the customer agreed to when ordering. The second depends on the order line, not only on the product, so it belongs there. Watch what happens when the mouse gets more expensive: 

UPDATE products SET price = 29 WHERE product_id = 3;

SELECT oi.order_id, p.name, oi.unit_price, p.price AS current_price
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
WHERE oi.order_id = 101
ORDER BY p.name;

SELECT sum(quantity * unit_price) AS order_101_total
FROM order_items
WHERE order_id = 101;
order_id  name             unit_price  current_price
--------  ---------------  ----------  -------------
101       USB-C cable 2 m  9           9
101       Wireless mouse   25          29

order_101_total
---------------
43

Order 101 still totals €43, which is what Sanne paid. Had the order looked up the current price, a price change would have rewritten every past invoice. Recognizing which facts are "as of a moment" and must be copied is part of good design.

Denormalization on purpose

Denormalization is deliberately storing redundant data — a copy, a precomputed total, a pre-joined table — to make reading faster or simpler, accepting the cost of keeping the copies consistent. Normalization optimizes for correct writes; some workloads are dominated by reads.

  • Reporting and analytics: data warehouses use star schemas, where descriptive tables are intentionally flattened so that reports need few joins (see Data Warehousing and Analytics).
  • Precomputed values: an order_total column saves summing the order lines on every page view.
  • Document databases often embed related data in one document because that is how it is read (see NoSQL Databases).

The rules for doing it safely: start from a normalized design and denormalize only where measurements show a problem; decide which copy is the source of truth; and keep the copies in step automatically (a trigger, a scheduled refresh, an ETL job), never by hoping every program remembers. Often an index (see Indexes and Query Performance) or a view solves the speed problem without any redundancy.

A practical checklist

You rarely need to name normal forms at work. These questions catch the same problems:

  1. Does any cell hold a list, or are there numbered columns (phone1, phone2)? → Move them to a child table, one row per value (1NF).
  2. In a table with a composite key, does any column describe only one part of the key? → Move it to that part's table (2NF).
  3. Does any column describe another non-key column rather than the row's key (a customer's city in an order)? → Move it to the table of the thing it describes (3NF).
  4. If you change one real-world fact, how many rows must change? If more than one, ask why.
  5. Can you add a new customer, product or supplier without inventing data, and delete one row without losing unrelated facts?
  6. For every copy you keep on purpose: is it a historical snapshot (fine) or a cache (document how it stays in sync)?