1NF, 2NF and 3NF with Examples

First normal form requires atomic values and no repeating groups. Every cell holds one value, and every row is identified by a key. The thing it fixes is the repeating group: a table with a column called tags holding 'a,b,c' cannot be queried without string functions, and a table with two rows per order for a two-line order cannot be aggregated without a GROUP BY that happens to undo the duplication.

-- 1NF: atomic values, one row per order line.
CREATE TABLE order_items_1nf (
  order_id INTEGER NOT NULL,
  line_no INTEGER NOT NULL,
  product_name VARCHAR(120) NOT NULL,
  qty INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

-- 2NF: the key is composite, so every non-key column must depend on the whole key.
CREATE TABLE order_items_2nf (
  order_id INTEGER NOT NULL,
  line_no INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

-- 3NF: no transitive dependency, so the order's own attributes live in orders.
CREATE TABLE orders_3nf (
  id INTEGER PRIMARY KEY,
  order_date TEXT NOT NULL,
  customer_id INTEGER NOT NULL
);

CREATE TABLE order_items_3nf (
  order_id INTEGER NOT NULL REFERENCES orders (id),
  line_no INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

Second normal form requires 1NF and says that every non-key column must depend on the whole key, not just part of it. It only matters when the key is composite. In the 1NF table above, the key is (order_id, line_no) and product_name depends on the order — a partial dependency — so the same product name is repeated on every line of every order that contains it. Moving the product to its own table and storing product_id fixes it.

Third normal form requires 2NF and says that no non-key column may depend on another non-key column. In the 2NF table, the order's date and customer still live in the line items, which means an order with three lines stores its date three times. Moving those attributes into an orders table means the order's date depends on the order, and the line depends on the order — a direct dependency on the key, not a transitive one.

There is a fourth normal form, which handles the case where two independent facts about the same key both want their own table — for instance a customer's delivery address and their billing address, where neither depends on the other. Most real schemas stop at 3NF, and plenty of good ones stop at 2NF.

Start with the messy version: an orders table where each row is an order line and every line repeats the order's details and the product's details.

-- Step 0: the messy table.
CREATE TABLE order_lines_messy (
  order_id INTEGER NOT NULL,
  line_no INTEGER NOT NULL,
  order_date TEXT NOT NULL,
  customer_name VARCHAR(120) NOT NULL,
  product_name VARCHAR(120) NOT NULL,
  qty INTEGER NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

INSERT INTO order_lines_messy (order_id, line_no, order_date, customer_name, product_name, qty, unit_price) VALUES
  (1, 1, '2026-03-01', 'Ada Lovelace', 'Mechanical Keyboard', 1, 79.50),
  (1, 2, '2026-03-01', 'Ada Lovelace', 'USB-C Hub', 2, 32.00),
  (2, 1, '2026-03-02', 'Grace Hopper', 'Mechanical Keyboard', 1, 79.50);

To 1NF. The table is already in 1NF: every cell holds one value and each row is a distinct order line. If the original table had had a tags column holding a comma-separated list, that would have to be split into a child table first.

To 2NF. Two partial dependencies have to go. customer_name depends on order_id, and product_name plus unit_price depend on the product, not on the order line. So create a customers table and a products table, and replace the names with customer_id and product_id in the line table.

To 3NF. The order's own attributes — order_date and customer_id — depend on the order, not on the line, so they move into an orders table with the order as its key, and the line table keeps only the line's own facts.

The result is four tables: customers, products, orders and order_lines, where order_lines has (order_id, line_no) as its composite key, product_id and qty as its non-key columns, and each depending only on the whole key.

What the exercise is really teaching is that each step removes a specific duplication. In the messy table, Ada's name and date are stored twice, and the Mechanical Keyboard's name and price are stored twice — and every one of those is a place where the data can go stale. After the full normalisation, each fact is stored once, so a price change is a single update.