Normalization

Introduction

So far you have mostly worked with tables that were already well-designed. But when a database is designed poorly, the same piece of information ends up duplicated across many rows, which makes updates error-prone and wastes storage. Normalization is the process of fixing that.

This lesson walks through normalization step by step, using the three most common stages — 1NF, 2NF, and 3NF — and a single concrete example table that gets progressively cleaned up at each stage.

What is Normalization?

Normalization is the process of organizing the tables in a database to reduce data redundancy (the same fact stored in multiple places) and improve data integrity (making sure that fact stays consistent everywhere it appears).

It works by splitting a large, messy table into smaller, related tables, each focused on a single subject, and connecting them with foreign keys. Normalization is usually described as a series of stages, called normal forms, each stricter than the last.

The Unnormalized Starting Table

Here is a single, unnormalized table trying to store orders, their customer, and every product on the order, all at once.

This design has serious problems: it can only store two products per order, customer_name and customer_phone are repeated on every order a customer places, and adding a third product would mean adding yet another pair of columns.

First Normal Form (1NF)

First Normal Form requires that every column hold a single, atomic value, and that there be no repeating groups of columns (like product1, product2, product3...).

To reach 1NF, we move each product on an order into its own row, in a separate orders_items table, instead of stretching columns sideways.

This is now 1NF, but notice customer_name, customer_phone, and price are still duplicated across rows — that redundancy is what the next two stages fix.

Second Normal Form (2NF)

Second Normal Form applies to tables with a composite primary key (a key made of more than one column) and requires that every non-key column depend on the whole key, not just part of it. This is called removing partial dependency.

In the 1NF table above, the composite key is (order_id, product_name). But price only depends on product_name (a keyboard costs 25.00 no matter which order it is on), and customer_name/customer_phone only depend on order_id, not on product_name at all. Both are partial dependencies.

To reach 2NF, we split out a products table so price is stored once per product, not once per order line.

CREATE TABLE products (
    product_name VARCHAR(50) PRIMARY KEY,
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    order_id INT,
    product_name VARCHAR(50),
    customer_name VARCHAR(50),
    customer_phone VARCHAR(20),
    PRIMARY KEY (order_id, product_name)
);

Now a product’s price lives in exactly one row of the products table, no matter how many orders include that product.

Third Normal Form (3NF)

Third Normal Form requires that non-key columns depend only on the primary key, and not on some other non-key column. A column that depends on another non-key column is called a transitive dependency.

In order_items above, customer_name and customer_phone depend on order_id, but order_id is not the whole key by itself — and more importantly, customer_phone really depends on the customer, not directly on the order. This is a transitive dependency: order_id → customer_name → customer_phone.

To reach 3NF, we pull customer details into their own customers table, and have orders reference a customer_id instead of repeating the customer’s details on every row.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    customer_phone VARCHAR(20)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE products (
    product_name VARCHAR(50) PRIMARY KEY,
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    order_id INT,
    product_name VARCHAR(50),
    PRIMARY KEY (order_id, product_name),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_name) REFERENCES products(product_name)
);

The Fully Normalized Result

The single messy table has now become four small, focused tables, each describing exactly one thing: customers, orders, products, and order_items linking them together.

INSERT INTO customers VALUES (1, 'Alice', '555-0101'), (2, 'Bob', '555-0102');
INSERT INTO orders VALUES (101, 1), (102, 2);
INSERT INTO products VALUES ('Keyboard', 25.00), ('Mouse', 10.00), ('Monitor', 150.00);
INSERT INTO order_items VALUES (101, 'Keyboard'), (101, 'Mouse'), (102, 'Monitor');

SELECT o.order_id, c.customer_name, oi.product_name, p.price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_name = p.product_name;

+----------+---------------+--------------+--------+
| order_id | customer_name | product_name | price  |
+----------+---------------+--------------+--------+
| 101      | Alice         | Keyboard     | 25.00  |
| 101      | Alice         | Mouse        | 10.00  |
| 102      | Bob           | Monitor      | 150.00 |
+----------+---------------+--------------+--------+

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Normalization takes a single, redundant table and reorganizes it into smaller, focused tables, each representing one concept, connected through keys — making your data easier to update correctly and safely.

In this lesson, you learned what normalization is, and walked a real table through 1NF, 2NF, and 3NF, splitting it into customers, orders, products, and order_items. Next, you will learn about Denormalization — when and why it can make sense to intentionally reintroduce some redundancy.