Identifying Normal Form
Diagnosing a table is a fixed sequence of questions, and the first one that fails names the form. A table that fails an early question can't be judged on a later one.
The diagnosis, in order
- 1. Find the key: If 2 rows can share every value, the table has no key and the diagnosis cannot start.
- 2. Any cell holding a list, or any numbered column group?: Delimited strings, JSON blobs, item_1 through item_3. Stop here until the list becomes rows.
- 3. Multi-column key, and a non-key column that depends on part of it?: A 1-column key passes this step without further checking.
- 4. A non-key column that depends on another non-key column?: Look for an id column sitting beside the name or attribute that the id determines.
- 5. None of the above: Every non-key column is a fact about exactly the thing the key identifies.
A worked diagnosis
| ticket_id | opened_at | agent_id | agent_name | agent_team | tags |
|---|---|---|---|---|---|
| 5001 | 2024-04-01 | A3 | Lena Koch | Billing | billing;urgent |
| 5002 | 2024-04-01 | A3 | Lena Koch | Billing | refund |
| 5003 | 2024-04-02 | A8 | Raj Mehta | Tech | login;urgent |
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 shipments table has 1 row per shipment: shipment_id, shipped_at, warehouse_id, warehouse_city, carrier_code, tracking_number. Which normal form does it meet?
- 1NF only, because warehouse_city depends on part of the key (shipment_id, warehouse_id) rather than all of it.
- 2NF but not 3NF, because warehouse_city depends on warehouse_id, which is a non-key column.
- 3NF, because every cell holds a single value and the key is 1 column, so both earlier steps pass.
- Below 1NF, because tracking_number is a string that carrier systems break into several parts.
Repeated values that are not copied facts
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.
- Repetition that is a reference
- customer_id 42 on 3 order rows: 3 facts roughly 3 orders
- product_id P7 on 2 order lines
- agent_id A3 on 2 tickets
- Repetition that is a copied fact
- ines@okoro.io on 3 order rows: 1 fact written 3 times
- unit_price 34.00 on every Desk Lamp line
- agent_team Billing on every one of Lena's tickets
About this lesson
This section is part of the Normalization lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.