Database Normalization: 1NF, 2NF and 3NF with MySQL Examples

Normalization is a set of rules for organizing tables so that each fact is stored once. When a fact is stored in many places, copies drift apart, and the data contradicts itself. Normalization removes that risk.

A flat table that does too much

Here is an order list as someone might first build it in a spreadsheet:

query.sql

SQL

CREATE TABLE orders_flat (
    order_id INT,
    order_date DATE,
    customer_name VARCHAR(40),
    customer_email VARCHAR(60),
    customer_city VARCHAR(40),
    products VARCHAR(100),
    prices VARCHAR(60)
);

INSERT INTO orders_flat VALUES
(1, '2026-03-01', 'Maya',  'maya@example.com',  'Pune',  'Pen, Notebook', '12.50, 80.00'),
(2, '2026-03-02', 'Rohan', 'rohan@example.com', 'Delhi', 'Notebook',      '80.00'),
(3, '2026-03-05', 'Maya',  'maya@example.com',  'Pune',  'Marker, Pen',   '45.00, 12.50');

SELECT * FROM orders_flat ORDER BY order_id;

Output

OUTPUT

+----------+------------+---------------+-------------------+---------------+---------------+--------------+
| order_id | order_date | customer_name | customer_email    | customer_city | products      | prices       |
+----------+------------+---------------+-------------------+---------------+---------------+--------------+
|        1 | 2026-03-01 | Maya          | maya@example.com  | Pune          | Pen, Notebook | 12.50, 80.00 |
|        2 | 2026-03-02 | Rohan         | rohan@example.com | Delhi         | Notebook      | 80.00        |
|        3 | 2026-03-05 | Maya          | maya@example.com  | Pune          | Marker, Pen   | 45.00, 12.50 |
+----------+------------+---------------+-------------------+---------------+---------------+--------------+
3 rows in set

Three problems are visible already.

The anomalies

Update anomaly: Maya moves to Mumbai. You must update every order of hers. If one is missed, the table says she lives in two cities:

query.sql

SQL

1
2

UPDATE orders_flat SET customer_city = 'Mumbai' WHERE order_id = 1;
SELECT customer_name, customer_city, COUNT(*) AS orders FROM orders_flat GROUP BY customer_name, customer_city ORDER BY customer_name, customer_city;

Output

OUTPUT

+---------------+---------------+--------+
| customer_name | customer_city | orders |
+---------------+---------------+--------+
| Maya          | Mumbai        |      1 |
| Maya          | Pune          |      1 |
| Rohan         | Delhi         |      1 |
+---------------+---------------+--------+
3 rows in set

Maya now has two cities. Nothing in the database says which is right.

Insert anomaly: you cannot record a new customer until they place an order, or a new product until someone buys it. (There is no place for them without inventing an empty order.)

Delete anomaly: delete order 2 and you silently lose everything the system knew about Rohan.

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

A table is in 1NF when every column holds a single, atomic value, and there are no repeating groups. The products and prices columns hold lists, so:

You cannot search for “orders with a Pen” with a normal comparison (LIKE '%Pen%' also matches “Pencil”).
You cannot join to a product table.
You cannot keep the price list aligned with the products list, except by position, and that is fragile.

The fix: one row per order line.

query.sql

SQL

CREATE TABLE order_lines_1nf (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    customer_name VARCHAR(40) NOT NULL,
    customer_email VARCHAR(60) NOT NULL,
    customer_city VARCHAR(40) NOT NULL,
    product VARCHAR(40) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (order_id, product)
);

INSERT INTO order_lines_1nf VALUES
(1, '2026-03-01', 'Maya',  'maya@example.com',  'Pune',  'Pen',      12.50),
(1, '2026-03-01', 'Maya',  'maya@example.com',  'Pune',  'Notebook', 80.00),
(2, '2026-03-02', 'Rohan', 'rohan@example.com', 'Delhi', 'Notebook', 80.00),
(3, '2026-03-05', 'Maya',  'maya@example.com',  'Pune',  'Marker',   45.00),
(3, '2026-03-05', 'Maya',  'maya@example.com',  'Pune',  'Pen',      12.50);

SELECT order_id, product, price FROM order_lines_1nf ORDER BY order_id, product;

Output

OUTPUT

+----------+----------+-------+
| order_id | product  | price |
+----------+----------+-------+
|        1 | Notebook | 80.00 |
|        1 | Pen      | 12.50 |
|        2 | Notebook | 80.00 |
|        3 | Marker   | 45.00 |
|        3 | Pen      | 12.50 |
+----------+----------+-------+
5 rows in set

The key is now composite: (order_id, product) identifies a row. But the customer’s name, email and city still repeat on every line of every order.

Second normal form (2NF): no partial dependencies

2NF applies to tables with a composite key. Every non-key column must depend on the whole key, not on a part of it.

order_date, customer_* depend only on order_id (part of the key).
price depends only on product (the other part).

Those are partial dependencies. Move each group to a table of its own, keyed by what it depends on:

query.sql

SQL

CREATE TABLE orders_2nf (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_name VARCHAR(40) NOT NULL,
    customer_email VARCHAR(60) NOT NULL,
    customer_city VARCHAR(40) NOT NULL
);
CREATE TABLE products_2nf (
    product VARCHAR(40) PRIMARY KEY,
    price DECIMAL(8,2) NOT NULL
);
CREATE TABLE order_items_2nf (
    order_id INT NOT NULL,
    product VARCHAR(40) NOT NULL,
    PRIMARY KEY (order_id, product),
    FOREIGN KEY (order_id) REFERENCES orders_2nf (order_id),
    FOREIGN KEY (product) REFERENCES products_2nf (product)
);

INSERT INTO orders_2nf SELECT DISTINCT order_id, order_date, customer_name, customer_email, customer_city FROM order_lines_1nf;
INSERT INTO products_2nf SELECT DISTINCT product, price FROM order_lines_1nf;
INSERT INTO order_items_2nf SELECT order_id, product FROM order_lines_1nf;

SELECT * FROM orders_2nf ORDER BY order_id;

Output

OUTPUT

+----------+------------+---------------+-------------------+---------------+
| order_id | order_date | customer_name | customer_email    | customer_city |
+----------+------------+---------------+-------------------+---------------+
|        1 | 2026-03-01 | Maya          | maya@example.com  | Pune          |
|        2 | 2026-03-02 | Rohan         | rohan@example.com | Delhi         |
|        3 | 2026-03-05 | Maya          | maya@example.com  | Pune          |
+----------+------------+---------------+-------------------+---------------+
3 rows in set

Product prices are now stored once. But look at orders_2nf: Maya’s name, email and city are still repeated on orders 1 and 3.

Third normal form (3NF): no transitive dependencies

3NF removes columns that depend on another non-key column instead of on the key. Here the city and the name depend on the customer (identified by email), not on the order. The chain is order_id → customer → city, a transitive dependency. Move the customer into its own table:

query.sql

SQL

CREATE TABLE customers_3nf (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL,
    email VARCHAR(60) NOT NULL UNIQUE,
    city VARCHAR(40) NOT NULL
);
CREATE TABLE orders_3nf (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers_3nf (id)
);

INSERT INTO customers_3nf (name, email, city) SELECT DISTINCT customer_name, customer_email, 'Pune' FROM orders_2nf;
INSERT INTO orders_3nf SELECT o.order_id, o.order_date, c.id FROM orders_2nf o JOIN customers_3nf c ON c.email = o.customer_email;

SELECT * FROM customers_3nf ORDER BY id;

Output

OUTPUT

+----+-------+-------------------+------+
| id | name  | email             | city |
+----+-------+-------------------+------+
|  1 | Maya  | maya@example.com  | Pune |
|  2 | Rohan | rohan@example.com | Pune |
+----+-------+-------------------+------+
2 rows in set

(Maya’s city was set to the right value when the data was copied: in a real migration, you would first decide which of her two cities is correct. Here both customers get ‘Pune’ only to keep the example short. Rohan’s real city was Delhi, and the next statement fixes it.)

query.sql

SQL

1
2
3

UPDATE customers_3nf SET city = 'Delhi' WHERE email = 'rohan@example.com';
UPDATE customers_3nf SET city = 'Mumbai' WHERE email = 'maya@example.com';
SELECT name, city FROM customers_3nf ORDER BY id;

Output

OUTPUT

+-------+--------+
| name  | city   |
+-------+--------+
| Maya  | Mumbai |
| Rohan | Delhi  |
+-------+--------+
2 rows in set

Now a move is one update, and every order sees it. The anomalies are gone:

Update: change the city in one row.
Insert: add a customer or a product without any order.
Delete: removing an order does not delete the customer.

Proving that nothing was lost

Join the tables back together. The result should equal the original data:

query.sql

SQL

SELECT o.order_id, o.order_date, c.name, c.city, i.product, p.price
FROM orders_3nf o
JOIN customers_3nf c ON c.id = o.customer_id
JOIN order_items_2nf i ON i.order_id = o.order_id
JOIN products_2nf p ON p.product = i.product
ORDER BY o.order_id, i.product;

Output

OUTPUT

+----------+------------+-------+--------+----------+-------+
| order_id | order_date | name  | city   | product  | price |
+----------+------------+-------+--------+----------+-------+
|        1 | 2026-03-01 | Maya  | Mumbai | Notebook | 80.00 |
|        1 | 2026-03-01 | Maya  | Mumbai | Pen      | 12.50 |
|        2 | 2026-03-02 | Rohan | Delhi  | Notebook | 80.00 |
|        3 | 2026-03-05 | Maya  | Mumbai | Marker   | 45.00 |
|        3 | 2026-03-05 | Maya  | Mumbai | Pen      | 12.50 |
+----------+------------+-------+--------+----------+-------+
5 rows in set

Same five order lines, now stored without duplication.

The rules in one table

Form	Rule	What it removes
1NF	One atomic value per cell, no repeating groups, a primary key	Lists in a column, repeating columns (`product1`, `product2`)
2NF	1NF, and every non-key column depends on the whole key	Facts about part of a composite key
3NF	2NF, and every non-key column depends only on the key	Facts about another non-key column
BCNF	Every determinant is a candidate key	Rare leftovers when a table has several overlapping candidate keys

A memorable summary: every non-key column depends on the key, the whole key, and nothing but the key.

Is a lookup of a city a violation?

Not every repeated value is a problem. A column such as status holding ‘paid’ on a million rows is a value, not a repeated fact about something else. Normalize when the repetition is a fact about another entity (a customer’s city, a product’s price) that could change in one place and not the others.

lightbulb

Use a surrogate key in the real schema

The 3NF tables above use product text as a key to keep the example readable. A real products table gets an integer id, and order_items references it (see Foreign Keys).