Database normalization explained for beginners: 1NF, 2NF and 3NF with SQL

You are asked to keep track of orders. The simplest idea is one big table with everything on every row: the customer's name, their city, the product, its price, the quantity. It works at first. Then a customer moves city, a product's price changes, and suddenly the same fact is written in five places and two of them disagree. Normalization is the set of steps that stops this, by making sure each fact is stored once.

This guide builds the problem first, so you can see it break, and then fixes it one step at a time. The queries ran in DuckDB, and each output is exactly what it printed. If keys are new to you, read primary key vs foreign key first.

Where it starts: a table with a list in a cell

A first attempt: 1 row per order, with all the products squeezed into one cell.

CREATE TABLE orders_raw (
  order_id INTEGER,
  customer VARCHAR,
  city     VARCHAR,
  products VARCHAR
);

INSERT INTO orders_raw VALUES
  (1, 'Asha', 'Pune',   'Pen, Notebook'),
  (2, 'Ravi', 'Mumbai', 'Pencil'),
  (3, 'Asha', 'Pune',   'Notebook, Stapler');

Now ask a simple question: how many orders include a Pen? The obvious try is to search the text:

SELECT COUNT(*) AS orders_with_pen
FROM orders_raw
WHERE products LIKE '%Pen%';

orders_with_pen
---------------
2
(1 row)

Two, but only order 1 has a Pen. Order 2 has a *Pencil*, and the text Pen is hiding inside it. A list stuffed into one cell cannot be searched, counted or joined reliably. That is the first thing to fix.

1First normal form (1NF): one value per cell

A table is in 1NF when every cell holds a single value and every row can be told apart. Give each product its own row. Each row is now one product on one order, so the key is the pair (order_id, product). Prices and quantities come along because a real order has them.

CREATE TABLE order_items_flat (
  order_id   INTEGER,
  customer   VARCHAR,
  city       VARCHAR,
  product    VARCHAR,
  unit_price INTEGER,
  quantity   INTEGER
);

INSERT INTO order_items_flat VALUES
  (1, 'Asha', 'Pune',   'Pen',      10,  5),
  (1, 'Asha', 'Pune',   'Notebook', 50,  2),
  (2, 'Ravi', 'Mumbai', 'Pencil',    5, 10),
  (3, 'Asha', 'Pune',   'Notebook', 50,  1),
  (3, 'Asha', 'Pune',   'Stapler', 120,  1);

SELECT COUNT(DISTINCT order_id) AS orders_with_pen
FROM order_items_flat
WHERE product = 'Pen';

orders_with_pen
---------------
1
(1 row)

One order, which is correct. Counting works now. But look at what is repeated: Asha and Pune are written on 3 rows, and the Notebook price of 50 on two. Watch what happens when Asha moves to Bengaluru and someone updates only the first order:

UPDATE order_items_flat SET city = 'Bengaluru' WHERE order_id = 1;

SELECT DISTINCT customer, city FROM order_items_flat ORDER BY city;

customer | city
---------+----------
Asha     | Bengaluru
Ravi     | Mumbai
Asha     | Pune
(3 rows)

Asha is now in two cities at once. This is called an update anomaly: the same fact is stored in several places, and a change reaches only some of them. The next 2 steps remove the repeats.

2Second normal form (2NF): depend on the whole key

2NF applies when the key has more than 1 column, as it does here. A column should depend on the whole key, not just a part. Ask of each column: "does knowing only the order tell me this, or only the product?"

• customer and city depend on the order alone. They do not change with the product.
• unit_price depends on the product alone. A Notebook costs 50 whichever order it is in.
• quantity needs both: how many of this product in this order.

So columns that depend on a part of the key move to their own tables. Here are the 3 tables, with each product given a number:

CREATE TABLE orders (order_id INTEGER, customer VARCHAR, city VARCHAR);
INSERT INTO orders VALUES (1, 'Asha', 'Pune'), (2, 'Ravi', 'Mumbai'), (3, 'Asha', 'Pune');

CREATE TABLE products (product_id INTEGER, name VARCHAR, unit_price INTEGER);
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Notebook', 50), (3, 'Pencil', 5), (4, 'Stapler', 120);

CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER);
INSERT INTO order_items VALUES (1, 1, 5), (1, 2, 2), (2, 3, 10), (3, 2, 1), (3, 4, 1);

SELECT * FROM products ORDER BY product_id;

product_id | name     | unit_price
-----------+----------+-----------
1          | Pen      | 10
2          | Notebook | 50
3          | Pencil   | 5
4          | Stapler  | 120
(4 rows)

Each product and its price now appear exactly once. Changing the price of a Notebook means changing 1 row. The order_items table keeps only what depends on both the order and the product, the quantity.

3Third normal form (3NF): no column depends on another non-key column

3NF removes the last kind of repeat. Look at orders: it stores the customer and the city. But the city depends on the *customer*, not on the order. Knowing it is Asha already tells you the city, whichever order it is. That is a dependency between two non-key columns, and it is why Asha's city is written on both of her orders.

The fix is a separate customers table, with each order pointing to it by id:

CREATE TABLE customers (customer_id INTEGER, name VARCHAR, city VARCHAR);
INSERT INTO customers VALUES (1, 'Asha', 'Pune'), (2, 'Ravi', 'Mumbai');

CREATE TABLE orders_3nf (order_id INTEGER, customer_id INTEGER);
INSERT INTO orders_3nf VALUES (1, 1), (2, 2), (3, 1);

The tables are now customers (who), products (what), orders_3nf (which customer placed it) and order_items (which product, how many). Each fact lives in exactly 1 row. Now move Asha to Bengaluru, as before, but this time there is only one place to change:

UPDATE customers SET city = 'Bengaluru' WHERE customer_id = 1;

SELECT o.order_id, c.name, c.city
FROM orders_3nf o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

order_id | name | city
---------+------+----------
1        | Asha | Bengaluru
2        | Ravi | Mumbai
3        | Asha | Bengaluru
(3 rows)

Both of Asha's orders report Bengaluru, because the city is stored once. The anomaly cannot happen.

4Nothing was lost: put the tables back together

Normalizing splits data into pieces, and a join puts them back. To prove the pieces hold the same information, rebuild the revenue figure both ways. From the flat table it is:

SELECT SUM(unit_price * quantity) AS revenue_from_flat_table
FROM order_items_flat;

revenue_from_flat_table
-----------------------
370
(1 row)

And from the normalized tables, joined back together:

SELECT SUM(p.unit_price * i.quantity) AS revenue_from_normalized_tables
FROM order_items i
JOIN products p ON p.product_id = i.product_id;

revenue_from_normalized_tables
------------------------------
370
(1 row)

370 both times. The cost of normalizing is joins, and the benefit is that every fact is stored once and stays consistent.

5When data engineers break the rules on purpose

Normalized tables are ideal for applications that write data all day, such as a shop's order system, because updates are safe. Analysts, though, read data and run big queries, and joins across many tables are slower and harder to write. So a data warehouse often goes the other way and denormalizes: it keeps one wide table, or a star shape with a few flat tables around a central one. You saw that design in star schema vs snowflake schema. The skill is knowing both: normalize where changes are frequent and correctness matters, and flatten where reading speed and simplicity matter.

Quick checklist

1. 11NF: is there exactly one value in every cell, and can I tell rows apart?
2. 22NF: does every non-key column depend on the whole key?
3. 33NF: does any non-key column depend on another non-key column?
4. 4Can every fact be changed by updating 1 row?

Read primary key vs foreign key to see how the tables above are linked, and the data modeling questions for the interview versions of this topic.