Database Normalization Explained: 1NF, 2NF, 3NF in Plain English

Normalization sounds academic, but the idea is practical: store each fact in exactly one place. When the same fact lives in several rows, sooner or later one copy gets updated and the others don't, and your data starts contradicting itself.

AI-generated schemas often get this wrong in both directions — a single giant table, or a dozen tables for something simple. Knowing the normal forms helps you spot both.

The starting point: one big table

An orders spreadsheet, turned into a table:

order_id	customer_name	customer_email	products	product_prices	order_date
1	Ada	ada@ex.com	Mug, Poster	12, 18	2026-09-01
2	Ada	ada@ex.com	T-shirt	25	2026-09-03
3	Grace	grace@ex.com	Mug	12	2026-09-04

It works — until it doesn't:

• Update anomaly: Ada changes her email. You must update every one of her orders; miss one and she has two emails.
• Insert anomaly: you can't add a product to the catalogue until someone orders it.
• Delete anomaly: delete Grace's only order and you lose the fact that the Mug costs 12 — and that Grace exists.

Normalization fixes these step by step.

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

Rule: every column holds a single, atomic value; no lists in a cell; no repeating groups (product1, product2, product3 columns).

products = "Mug, Poster" breaks this. You can't easily query "all orders containing a Mug" or sum prices. Split into one row per order item:

order_id	customer_email	customer_name	product	price	order_date
1	ada@ex.com	Ada	Mug	12	2026-09-01
1	ada@ex.com	Ada	Poster	18	2026-09-01
2	ada@ex.com	Ada	T-shirt	25	2026-09-03

Now each row is identified by (order_id, product).

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

Rule: be in 1NF, and every non-key column must depend on the entire primary key, not just part of it.

The key is (order_id, product). But order_date and customer_email depend only on order_id. price depends only on product. They're repeated in every item row. Split:

orders: order_id, customer_email, customer_name, order_date products: product, price order_items: order_id, product, quantity

Third normal form (3NF): nothing depends on a non-key

Rule: be in 2NF, and non-key columns must depend only on the key — not on another non-key column.

In orders, customer_name depends on customer_email (the customer), not on the order. That's a transitive dependency. Split customers out:

CREATE TABLE customers (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name  text NOT NULL
);

CREATE TABLE products (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL,
  price_cents integer NOT NULL
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id    bigint NOT NULL REFERENCES orders(id),
  product_id  bigint NOT NULL REFERENCES products(id),
  quantity    integer NOT NULL CHECK (quantity > 0),
  unit_price_cents integer NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Now Ada's email lives in one row. Products exist without orders. Deleting an order deletes only the order. (Primary key vs foreign key, SQL joins)

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

Wait — why is unit_price_cents in order_items?

That looks like duplication of products.price_cents. It isn't: it records the price at the time of the order. When the Mug's price changes next month, old orders must still show what the customer paid. Historical facts are different facts. Recognising this is the difference between normalizing mechanically and modelling correctly.

Beyond 3NF

BCNF, 4NF and 5NF handle rarer cases (overlapping candidate keys, independent multi-valued facts). For most application schemas, 3NF is the practical target.

When to denormalize on purpose

Normalized data is consistent; reading it sometimes needs joins and aggregation. Deliberate denormalization is fine when you know why:

• Read performance — store order_total or comment_count to avoid summing on every page load. Keep it correct with transactions or triggers, and be ready to recompute it.
• Snapshots — prices, addresses, names as they were at a point in time.
• Reporting tables — copies shaped for analytics, rebuilt from the source of truth.
• JSON for genuinely variable attributes — product specs that differ by category. (Postgres JSONB)

Normalize first; denormalize specific things when measurements say you need to. (Database indexes usually fix slow joins first.)

The summary

• Normalization = each fact stored once, preventing update, insert and delete anomalies.
• 1NF: single values, no lists in cells.
• 2NF: no columns depending on part of a composite key.
• 3NF: no columns depending on other non-key columns.
• Snapshots of historical values aren't duplication; denormalize deliberately for reads.

---

EasySpawn servers come with PostgreSQL and daily backups, so you (or Claude Code) can design, migrate and test a properly normalized schema against a real database. See how it works or join the waitlist.

Related: How to Design Your First Database · Primary Key vs Foreign Key · SQL Joins Explained · Database Indexes