Normalization

Customer 42 changes their email to priya@nair.dev. The engineer updates the rows for order 1001, the order number on the ticket, and closes it. Rows 1 and 2 now carry the new address and rows 4 and 5 the old one, with no error raised. Where customer 42's receipts go now depends on which row a report reads first. This query finds the damage.

The store adds P12, a Monitor Arm at 59.00, which no order contains yet. Here a product exists only as part of an order line, so recording P12 means inventing an order line: an empty order_id, an empty customer, a quantity of 0, and a fake row every report must filter out. A customer who registers before buying hits the same wall. This is an insert anomaly: the table cannot hold a fact around 1 thing without asserting a fact about another.

All 3 anomalies share a cause. A fact is a single statement about a single thing: customer 42's email is priya@nair.io, product P7 costs 34.00. This table puts customer, product and order facts on 1 row, so each customer fact is copied once per order line. Normalization splits a table until each fact is stored once, in a table about the thing it describes, so changing a fact means changing 1 row.

A key is the column, or the set of columns, whose value picks out exactly 1 row. Neither order_id nor product_id does that alone here, since order 1001 has 2 rows and P7 appears on 2 orders; the pair (order_id, product_id) does. Each normal form is a rule about how the columns outside the key relate to the key.

One row is 1 order, and product_ids holds the basket as a comma-separated string, with a matching string of quantities beside it. The pairing is positional, and nothing enforces that the 2 lists have the same length. Counting the orders that contained a Desk Lamp means looking inside a string.

For both shapes, the fix is to turn the list into rows. A row now stands for 1 item in 1 basket, keyed by order_id together with product_id, and an order can have 1 item or 40. Each quantity sits on the same row as the product it counts, so the table enforces the pairing. The Desk Lamp question becomes a filter and a sum.

The query needs no string parsing, cannot match P71 by accident, and reads the same for 1 line or 100. The split did cost something: order_date and customer_id are now written once per line, so order 1001's date appears twice. First normal form allows that; the next section deals with it.

The reporting team adds product names and prices to the 1NF table, order_lines. The key is still the pair (order_id, product_id); every other column is a non-key column.

Column B depends on column A when each value of A goes with exactly 1 value of B. Product P7 always means Desk Lamp at 34.00; order 1001 always means March 2 and customer 42. But order 1001 has 2 lines with different quantities, so qty needs both halves of the key. Writing down what each column depends on is the whole technique.

Of the 5 non-key columns, 4 depend on half the key: a partial dependency, and the mechanism behind section 0's duplication. order_date is copied once per line on the order, product_name and unit_price once per order containing the product. Raise the Desk Lamp's price and 2 rows must change; add P12 with no orders and there is no row to put it on.

Second normal form says: in a table with a multi-column key, every non-key column depends on the whole key. Columns that depend on order_id alone move to a table keyed by order_id. Columns that depend on product_id alone move to a table keyed by product_id. What depends on both stays where it is.

order_lines keeps both halves of the old key and nothing that depended on only 1 of them. customer_id moved with order_date, since the order alone determines which customer placed it. Against 5 order lines, orders holds 4 rows and products holds 3, so each date and each price appears once.

Each product's price now lives on 1 row. Repricing the Desk Lamp is a single-row change that every order line picks up through the join. P12 gets a row in products with no order attached. Deleting order 1003 removes 1 row from orders and 1 from order_lines, and P9 stays in products. The cost is that a report which read 1 table now reads 3.

The join reads the price from products once per line, so a price that changes tomorrow changes every total computed tomorrow.

If the store needs the price the customer actually paid, that's a fact about the line, since the catalog price can change after the sale. It belongs on order_lines as sale_price, next to qty. The catalog price and sale_price can match today and still be 2 different facts.

The original export also carried the customer's name and email beside each order, and many teams keep them on orders to save a join on every receipt.

The key is order_id alone, so 2NF is satisfied: a 1-column key has no part to depend on. Yet customer 42's email is written 3 times, and the halfway update from section 0 can happen again. This duplication runs through a column that is in no key at all.

Knowing the order tells you the email, but in 2 steps: order_id determines customer_id, and customer_id determines customer_email. The email is a fact about the customer, and the order knows it only because it knows the customer. That 2-step path is a transitive dependency, and every column at the far end of one is a copied fact waiting to drift.

The same shape hides in products. If a product row stores category_id and category_name together, the name reaches the key through the category, and renaming Lighting to Lamps means changing every product row in it. The signature is an id column beside the attributes that id determines.

Third normal form says: each column outside the key depends on the key directly, with no other column in between. The repair is the 2NF move with a different trigger: customer_id becomes the key of a new customers table, and the columns it determines go there. orders keeps customer_id as a reference, because who placed the order is a fact about the order. Categories get the same treatment.

Each table is now about 1 kind of thing: 1 row of customers is 1 customer, 1 row of order_lines is 1 product on 1 order. The old wide row is assembled at query time instead of stored. A wide table carrying category_name would answer revenue by category with a single GROUP BY; this schema needs 2 joins.

Step 1: ticket_id identifies a row. Step 2: tags holds a list, so the table fails 1NF and the diagnosis stops. Move tags to ticket_tags, 1 row per ticket and tag, and rerun. Step 2 passes, and step 3 passes on a 1-column key. Step 4: agent_name and agent_team depend on agent_id, which is in no key, so the table is in 2NF and fails 3NF. Split out agents and the third pass reaches step 5. The table took 3 passes and 2 splits, and ended as 3 tables.

A normalized schema still repeats values. The orders table holds customer_id 42 on 3 rows: 3 facts, each saying which customer placed that order. What 3NF forbids is 3 copies of the customer's email, which is 1 fact. To tell them apart, ask whether changing the value on one row should change it on the others. If yes, the column is a copied fact and belongs elsewhere. If no, it is a reference.