Database Normalization (1NF, 2NF, 3NF)

Normalization is the process of organizing tables to reduce data redundancy and prevent update, insert, and delete anomalies. It works by progressively splitting data into smaller, well-defined tables connected by foreign keys, following a series of increasingly strict rules called "normal forms."

An anomaly is a symptom of poor design: for example, if a customer's address is repeated on every one of their orders, updating that address means updating many rows — and if even one is missed, the data becomes contradictory. Normalization eliminates this by storing each fact in exactly one place.

Why it matters

When the same fact is stored in several rows, sooner or later the copies disagree: a customer's city is updated in one row and not in another. Normalization organises tables so that each fact is stored once. It is the theory behind good schema design and a guaranteed topic in database interviews.

First Normal Form (1NF)

A table is in 1NF when every column holds a single, atomic value (no lists or repeating groups packed into one field) and each row is uniquely identifiable. A column like phone_numbers = '555-1111, 555-2222' violates 1NF because it stores multiple values in one field, making it impossible to search or index individual numbers properly.

Second Normal Form (2NF)

A table is in 2NF when it is already in 1NF and every non-key column depends on the entire primary key, not just part of it. This rule only matters for tables with a composite primary key; if a column depends on only one part of that composite key, it should be moved to a separate table.

Third Normal Form (3NF)

A table is in 3NF when it is already in 2NF and every non-key column depends only on the primary key — not on another non-key column. If column B's value can be derived from column A (both non-key), such as deriving a department's manager name from a department name stored redundantly, that dependency should be factored into its own table.

A Concrete Before/After Example

Consider a single unnormalized orders table storing order_id, customer_name, customer_email, product_name, product_price. If the same customer places two orders, their name and email are duplicated across rows — an update anomaly (changing an email requires updating multiple rows) and an insert anomaly (you cannot record a customer until they place an order). Normalizing splits this into customers(id, name, email), products(id, name, price), and orders(id, customer_id, product_id, order_date), so each fact about a customer or product is stored exactly once and simply referenced by foreign key.

Example

-- Normalized structure after fixing the redundancy described above
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    product_id INTEGER NOT NULL REFERENCES products(id),
    order_date DATE NOT NULL
);

Three tables created; a customer's email now exists in exactly one row regardless of how many orders they place.

Exercise

orders_flat repeats each customer's name and city on every order. Normalise it: create a customers table holding each customer once, and an orders table that refers to the customer by id. Fill both from the flat table, then count the rows of each.

Expected output: customers, 2, orders, 3 (one per line)

CREATE TABLE orders_flat (order_id INTEGER, customer_name TEXT, customer_city TEXT, product TEXT);
INSERT INTO orders_flat VALUES
  (1, 'Asha', 'Pune', 'pen'),
  (2, 'Asha', 'Pune', 'book'),
  (3, 'Ravi', 'Delhi', 'bag');

-- TODO: create customers (id, name, city) and fill it with each distinct customer

-- TODO: create orders (id, customer_id, product) and fill it from the flat table

SELECT COUNT(*) AS customers FROM customers;
SELECT COUNT(*) AS orders FROM orders;

CREATE TABLE orders_flat (order_id INTEGER, customer_name TEXT, customer_city TEXT, product TEXT);
INSERT INTO orders_flat VALUES
  (1, 'Asha', 'Pune', 'pen'),
  (2, 'Asha', 'Pune', 'book'),
  (3, 'Ravi', 'Delhi', 'bag');

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

INSERT INTO customers (id, name, city)
SELECT MIN(order_id), customer_name, customer_city
FROM orders_flat
GROUP BY customer_name, customer_city;

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  product TEXT
);

INSERT INTO orders (id, customer_id, product)
SELECT f.order_id, c.id, f.product
FROM orders_flat f
JOIN customers c ON c.name = f.customer_name;

SELECT COUNT(*) AS customers FROM customers;
SELECT COUNT(*) AS orders FROM orders;

Quick quiz

Interview questions