Third Normal Form (3NF)

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.

order_id	order_date	customer_id	customer_name	customer_email
1001	2024-03-02	42	Priya Nair	priya@nair.io
1002	2024-03-05	17	Tom Ola	tom@ola.dev
1003	2024-03-06	42	Priya Nair	priya@nair.io
1004	2024-03-09	42	Priya Nair	priya@nair.io

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.

Dependencies that pass through a non-key column

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.

Move the fact to the thing it describes

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.

Schema

customers: customer_id BIGINT PK, customer_name VARCHAR, customer_email VARCHAR
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), category_id INTEGER
categories: category_id INTEGER PK, category_name VARCHAR

orders.customer_id references customers.customer_id (N:1)
order_lines.order_id references orders.order_id (N:1)
order_lines.product_id references products.product_id (N:1)
products.category_id references categories.category_id (N:1)

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.

SELECT
  c.category_name,
  SUM(l.qty * p.unit_price) AS revenue
FROM order_lines AS l
JOIN products AS p
  ON p.product_id = l.product_id
JOIN categories AS c
  ON c.category_id = p.category_id
GROUP BY
  c.category_name

category_name	revenue
Lighting	102.00
Cables	36.00
Accessories	45.00

On billions of lines, 2 joins cost real time, and later lessons weigh when to keep copies on purpose. For now, the trade runs one way: a normalized schema can always be joined back into the wide shape, while a wide table can't be trusted to agree with itself. Rename a category and the report above is correct on its next run, with no update script.

An invoices table has the key invoice_id and columns issued_at, customer_id, customer_name, customer_tier and amount. A customer is promoted from the Silver tier to Gold. Which statement is right?

The table is in 3NF, because customer_tier is a single value and invoice_id is a 1-column key, so the promotion is safe.
customer_tier breaks 2NF, because it depends on customer_id, which is half of the key of the invoices table.
customer_tier depends on invoice_id only through customer_id, so it belongs in a customers table where the promotion changes 1 row.
amount breaks 3NF, because it depends on customer_tier and tiers change the discounts that are applied to it.

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.