Normalization

Normalization is a set of guidelines for organizing tables so each fact is stored once. It prevents anomalies — inconsistencies that creep in when the same data lives in several places.

An unnormalized table

orders_flat
+----------+--------------+-----------------+----------------------------+
| order_id | customer     | customer_email  | products                   |
+----------+--------------+-----------------+----------------------------+
|        1 | Ada Lovelace | ada@example.com | Keyboard, USB-C Hub        |
|        2 | Ada Lovelace | ada@example.com | Monitor                    |
|        3 | Linus T.     | linus@ex.com    | Headphones, USB-C Hub      |
+----------+--------------+-----------------+----------------------------+

Problems:

• Update anomaly — if Ada changes her email, every one of her rows must be updated; miss one and the data contradicts itself.
• Insert anomaly — you can't record a new customer until they order.
• Delete anomaly — deleting Linus's only order also deletes the only record of Linus.
• Lists in a column — "which orders contain a USB-C Hub?" needs text searching, and counts per product are painful.

The normal forms, in brief

• First normal form (1NF) — each cell holds a single value; no lists or repeating columns (product1, product2, ...). Move repeating data into its own table (order_items).
• Second normal form (2NF) — every non-key column depends on the whole primary key. In order_items (order_id, product_id, product_name), product_name depends only on product_id, so it belongs in products.
• Third normal form (3NF) — non-key columns depend only on the key, not on other non-key columns. In orders (id, customer_id, customer_email), the email depends on the customer, so it belongs in customers.

A common summary: every column should depend on "the key, the whole key, and nothing but the key".

Our sample database is in third normal form: customers, products, orders, and order items each store their own facts, connected by keys.

Denormalization

Sometimes data is deliberately duplicated for speed — for example, storing an order's total on the orders table so reports don't recompute it, or building wide reporting tables in a data warehouse. Denormalize deliberately, keep the normalized data as the source of truth, and make sure the copies are kept in sync (with triggers, scheduled jobs, or materialized views).

A practical rule

Start normalized (3NF). Denormalize only when you've measured a performance problem that it solves.