The idea: one fact, one place
A table that stores the same fact in many rows can disagree with itself. If Ink's price is written in three order lines, one update can miss a copy, and then Ink has two prices. Normalization splits a table into smaller tables so that each fact is stored once. The normal forms 1NF, 2NF and 3NF are three rules, each stricter than the one before. The page takes one small order table through all three.
Functional dependencies and keys
A functional dependency X → Y says: any two rows with the same X have the same Y. A product id fixes the product's name and price, so product_id → product_name, unit_price. An order id fixes the date and the customer, so order_id → order_date, cust_id. A customer id fixes the name and city.
A key is a smallest set of columns that fixes every other column. The columns in some key are prime; the others are non-key columns. Dependencies come from what the data means, not from the rows you happen to see. The right panel of the canvas lists the four dependencies of the order data and says, for the current stage, whether each one is allowed.
First normal form: one value per cell
A table is in 1NF when every cell holds one atomic value: no lists, no repeating groups such as item1, item2, item3 columns. The unnormalized table keeps each order's items in one text cell, P1 Pen 2.00 ×2; P2 Ink 5.00 ×1. SQL can't index that list, join on it, or answer "who bought Ink?" without parsing text.
1NF makes one row per order line. The order's columns are copied into each of its lines, and no single column is unique any more: the key becomes the pair (order_id, product_id). The table now has 54 cells, and 16 of them repeat a fact that another row already holds (Demo: normalize step by step).
Second normal form: the whole key
A table is in 2NF when it is in 1NF and no non-key column depends on only part of a key. That is a partial dependency. In OrderItems, product_name and unit_price depend on product_id alone, and order_date and the customer depend on order_id alone. Only qty needs both halves of the key.
The fix is to move each partial dependency into its own table, keyed by the part it depends on: Products, Orders, and what is left, OrderLines. A table with a one-column key is always in 2NF, because a single column has no smaller part.
Third normal form: nothing but the key
A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. In the 2NF Orders table, order_id → cust_id and cust_id → cust_name, cust_city. The customer's name depends on the key only through cust_id: a transitive dependency. Moving the customer columns into Customers leaves cust_id in Orders as a foreign key.
The formal rule: for every dependency X → A in the table, either X is a superkey (contains a key) or A is prime. The short version, from Bill Kent: every non-key column depends on the key, the whole key, and nothing but the key. 1NF gives "the key", 2NF "the whole key", 3NF "nothing but the key".
| Stage | Tables | Cells | Repeated facts |
|---|---|---|---|
| UNF | Orders with an item list | 24 | 2, plus the lists |
| 1NF | OrderItems | 54 | 16 |
| 2NF | OrderLines, Products, Orders | 47 | 2 |
| 3NF | OrderLines, Products, Orders, Customers | 48 | 0 |
3NF has one more cell than 2NF (the cust_id foreign key) but no repeated fact. Normalization is about storing each fact once, not about using fewer cells.
Update, insert and delete anomalies
Repeated facts cause three kinds of trouble, called anomalies. The page rolls back after each try, so you can run them at every stage.
- Update anomaly: one fact in many rows must change in all of them. Miss one, and the table holds two prices for Ink or two cities for An (Demo: 1NF update anomaly, Demo: 2NF fixes products, not customers).
- Insert anomaly: a fact can't be stored until an unrelated fact exists. A new product needs an
order_id, because that column is part of the primary key and can't be NULL (Demo: 1NF insert anomaly). - Delete anomaly: deleting one fact deletes another. Order 1002 was Binh's only order; deleting it in 2NF also deletes the only record of the customer (Demo: delete order 1002).
| Try | 1NF | 2NF | 3NF |
|---|---|---|---|
| Change Ink's price | ✗ 3 rows | ✓ 1 row | ✓ 1 row |
| An moves to Hue | ✗ 4 rows | ✗ 2 rows | ✓ 1 row |
| Add a product nobody ordered | ✗ needs an order | ✓ | ✓ |
| Add a customer with no order | ✗ needs an order | ✗ needs an order | ✓ |
| Delete order 1002 | ✗ loses Binh | ✗ loses Binh | ✓ keeps Binh |
Lossless decomposition
Splitting a table is only safe if joining the parts gives back exactly the original rows. That is a lossless (lossless-join) decomposition. A split of R into R1 and R2 is lossless when the columns they share are a key of R1 or of R2. Every split on this page shares a key: product_id of Products, order_id of Orders, cust_id of Customers. Joining the 3NF tables gives the 6 rows of the 1NF table (Demo: join the 3NF tables back).
Split on a column that is not a key, and the join invents rows. If the customer columns are split off on cust_city instead of cust_id, Hanoi has two customers, and every Hanoi order joins with both. The join returns 11 rows; 5 of them never existed (Demo: split on cust_city instead).
When to denormalize
3NF is the usual target for tables that are written often. Reporting tables, caches and search indexes are often denormalized on purpose: they copy facts to avoid joins, and are rebuilt from the normalized source. Some copies are not redundancy at all. The price a customer paid belongs to the order line (unit_price_at_order), because it must not change when the catalogue price changes. That is a different fact from the product's current price.
See also B+ Tree as a Database Index, How MySQL Executes JOIN, GROUP BY and ORDER BY and How a Query Planner Picks a Plan, which show what the joins between normalized tables cost.
What the page leaves out
BCNF (every determinant is a key; 3NF still allows X → A when A is prime), 4NF and multivalued dependencies, 5NF and join dependencies. Computing keys from a set of dependencies (attribute closure, minimal cover) and the synthesis algorithm that always reaches a lossless, dependency-preserving 3NF. NULLs, which make dependencies harder to define. Foreign key actions (ON DELETE CASCADE): the delete demo removes the order's lines together with the order.