Normalization

Normalization is the process of organizing a schema so that each independent fact is stored in exactly one place. The payoff is that updating a fact — a customer’s email, a product’s price — only ever requires changing one row. The cost is that reading back a “complete” record (an order with its customer and items) now requires joining several tables back together.

Normal forms are a series of increasingly strict rules for what a well-structured table looks like. Each one fixes a specific kind of redundancy left over from the previous form.

First normal form (1NF)

Every column must hold a single, atomic value — no repeating groups or lists crammed into one field. A table with a products column containing "Widget x2, Gadget x1" isn’t queryable by product without parsing a string; splitting those into their own rows (one per order+product) fixes it.

Second normal form (2NF)

Applies once a table has a composite primary key (like order_id + product_id). Every non-key column must depend on the whole key, not just part of it. If product_name and price only depend on product_id (not on order_id), they don’t belong in the order-items table — they belong in their own Products table.

Third normal form (3NF)

Every non-key column must depend only on the primary key — not on another non-key column. If customer_email depends on customer_id, and customer_id merely happens to sit on the orders row, then customer_email has a transitive dependency on order_id (via customer_id) rather than a direct one. Pulling customer fields into their own Customers table removes it.

-- 3NF: orders only references a customer, it doesn't duplicate their details
SELECT o.order_id, c.customer_name, c.customer_email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;