Normal forms, step by step

Start from the flat sales_sheet on the previous page, whose rows are identified by the pair (order, product). Each normal form removes one kind of redundancy.

First normal form: one value per cell

Every column holds a single value, not a list. The comma-separated product_ids '1,2,5' from Review a schema breaks this rule, and so do columns like phone1, phone2, phone3.

sales_sheet already has one product per row, so it is in first normal form. That is necessary, but it did not stop any of the anomalies.

Second normal form: depend on the whole key

Every non-key column must depend on the whole key, not part of it. The key is (order_id, product_id), but:

Move each partial dependency into a table keyed by the part it depends on:

products     (product_id → name, category, price)
orders       (order_id   → ordered_at, customer_name, customer_email, customer_city)
order_items  (order_id, product_id → quantity, unit_price)

The product anomalies are gone: a product can exist with no orders, and deleting an order no longer deletes what we know about a product.

Third normal form: no dependencies through another column

No non-key column may depend on another non-key column. In orders:

order_id → customer_email     but really:     order_id → customer → customer_email

The email depends on the customer, who depends on the order. That transitive dependency is why Asha’s email was repeated once per order. Give customers their own table, and let the order point to it:

customers  (customer_id → name, email, city)
orders     (order_id    → ordered_at, status, customer_id)

Now every fact lives in exactly one place. These four tables, customers, products, orders and order_items, are the practice shop schema, and sales_sheet was built from them with three joins. Nothing was lost: the decomposition can always be joined back.

BCNF: every determinant is a key

Third normal form allows one rare remaining case. Boyce–Codd normal form says: whenever some columns determine another column, those columns must be a key. The classic example is a tutoring table (student, subject, tutor) where each tutor teaches only one subject: tutor → subject, but tutor is not a key of the table, so a subject is repeated for every student a tutor has. You split it into (tutor, subject) and (student, tutor). In everyday schemas, reaching third normal form usually reaches BCNF too.

When to stop, and when to go back

Normalize by default. Store a value twice (denormalize) only for a measured reason, and decide who keeps the copies in sync:

The second row is worth noticing. order_items.unit_price already works this way: it is not a duplicate of the product’s price, it is a different fact, the price paid on the day. Before calling something redundant, ask whether it records a different moment.

Check yourself

1. A table keyed by (order_id, product_id) has a category column. Which normal form does it break, and why?

   Show answer

   Second normal form. category depends on the product alone, which is only part of the key.

   I could explain this without looking

2. What is a transitive dependency, and which normal form removes it?

   Show answer

   A non-key column depending on the key through another non-key column, such as order → customer → email. Third normal form removes it by moving the dependent columns to their own table.

   I could explain this without looking

3. Is order_items.unit_price a redundant copy of products.price? Explain.

   Show answer

   No. It records the price paid when the order was placed, a different fact from the current catalogue price. They are allowed to differ, and in shop they do.

   I could explain this without looking