Second Normal Form (2NF)
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.
| order_id | product_id | order_date | customer_id | product_name | unit_price | qty |
|---|---|---|---|---|---|---|
| 1001 | P7 | 2024-03-02 | 42 | Desk Lamp | 34.00 | 1 |
| 1001 | P2 | 2024-03-02 | 42 | USB Cable | 9.00 | 3 |
| 1002 | P7 | 2024-03-05 | 17 | Desk Lamp | 34.00 | 2 |
| 1003 | P9 | 2024-03-06 | 42 | Felt Pad | 4.50 | 10 |
| 1004 | P2 | 2024-03-09 | 42 | USB Cable | 9.00 | 1 |
What depends on what
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.
| Column | Determined by | Portion of the key |
|---|---|---|
| order_date | order_id | half |
| customer_id | order_id | half |
| product_name | product_id | half |
| unit_price | product_id | half |
| qty | order_id and product_id | whole |
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.
Give each half of the key its own table
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.
- orders: order_id BIGINT PK, order_date DATE, customer_id BIGINT
- order_lines: order_id BIGINT PK, product_id VARCHAR PK, qty INTEGER
- products: product_id VARCHAR PK, product_name VARCHAR, unit_price DECIMAL(10,2)
- order_lines.order_id references orders.order_id (N:1)
- order_lines.product_id references products.product_id (N:1)
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.
SELECT
o.order_id,
o.order_date,
SUM(l.qty * p.unit_price) AS order_total
FROM order_lines AS l
JOIN orders AS o
ON o.order_id = l.order_id
JOIN products AS p
ON p.product_id = l.product_id
GROUP BY
o.order_id,
o.order_date| order_id | order_date | order_total |
|---|---|---|
| 1001 | 2024-03-02 | 61.00 |
| 1002 | 2024-03-05 | 68.00 |
| 1003 | 2024-03-06 | 45.00 |
| 1004 | 2024-03-09 | 9.00 |
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.
An exam_results table has the key (student_id, course_id) and columns student_name, course_title and score. Which columns break second normal form?
- student_name and course_title: each depends on 1 column of the key rather than on both.
- score only: it is the one column that changes between rows that share the same student.
- student_name only: course_title is a fact about the course and each course has 1 title.
- None of them: every cell holds a single value, so the table is already in 2NF and 3NF.
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.