Normalization: 1NF, 2NF, 3NF

Start here

A denormalized table stores the same fact in more than one place, which means updating it can leave two copies disagreeing. Normalization is a sequence of rules, each one removing a specific kind of redundancy by moving data into its own table.

The three rules

• 1NF — atomic values. No column holds a list. "Pen, Notebook" in one cell becomes two rows.
• 2NF — no partial dependency. With a composite key, every other column must depend on the whole key, not just part of it. Customer name depends only on order_id, not on (order_id, product) — so it moves out.
• 3NF — no transitive dependency. A column cannot depend on another non-key column. Customer city depends on customer, not directly on the order — so it moves to its own table.

The problem normalisation solves

Put everything in one wide table and three specific problems appear. They have names, and recognising them is most of the skill.

Start with a table that stores an order, its customer and its product all in one row:

• Update anomaly. Alice moves to Bristol. Her city is stored in two rows, and if you update only one, the database now holds two contradictory answers to the same question.
• Insert anomaly. You cannot record a new customer until they place an order, because the row has no order to hang on.
• Delete anomaly. Delete order 3 and you lose the only record that Bob exists, and the only record of the keyboard's price if it was the last one.

Splitting the data into Customers, Products and Orders makes each fact live in exactly one place. Update Alice's city once and everything that references her sees the change, because there is nothing else to update.

The three normal forms, with a test each

First normal form (1NF): one value per cell. No comma-separated lists, no repeating column groups like phone1, phone2, phone3.

Test: does any cell contain more than one thing? A tags column holding "urgent,billing,uk" fails. The fix is a separate row per tag.

Second normal form (2NF): no partial dependencies. Applies when the primary key is made of several columns. Every non-key column must depend on the whole key, not just part of it.

Test: with a key of (OrderID, ProductID), does ProductName depend on both? No — it depends on ProductID alone. It belongs in a Products table.

Third normal form (3NF): no transitive dependencies. Non-key columns must not depend on other non-key columns.

Test: does CustomerCity depend on the order, or on the customer? On the customer — so it belongs in Customers, keyed by CustomerID.

The informal summary that people memorise, and which is genuinely accurate: every non-key column depends on the key, the whole key, and nothing but the key.

Higher forms exist — BCNF, 4NF, 5NF — and address rarer situations with overlapping candidate keys and multi-valued dependencies. In practice, 3NF is where most well-designed transactional schemas sit.

When to denormalise deliberately

Normalisation optimises for correctness of writes. Reads pay for it in joins, and past a certain scale that cost becomes the dominant one.

Denormalising means storing something redundantly on purpose, accepting the duplication in exchange for speed. It is a legitimate engineering decision when:

• A report joins eight tables and runs constantly.
• The redundant data does not change — the price at the time of the order genuinely belongs on the order, because it must not change when the product's price does.
• You are building an analytics warehouse, where star schemas are deliberately denormalised for query speed.
• A counter (comment_count) is read thousands of times per write.

The cost is that you now own the consistency problem the database was solving for you. Every denormalised copy needs a plan: a trigger, an application-level update, or a scheduled rebuild. Denormalising without that plan is not a trade-off, it is a bug waiting for its first inconsistency.

The order to work in: normalise first, measure, then denormalise the specific hot paths with evidence. Starting denormalised because it might be faster is how schemas become unmaintainable.

The anomalies, on a table that has them

Normalisation is usually taught as a list of numbered forms. It is easier to see as three specific bugs that a badly-shaped table makes possible, and this builds such a table so you can commit each one.

-- One wide table holding everything about an order. This is what
-- denormalised data looks like, and it is not obviously wrong.

CREATE TABLE flat (
    order_id      INTEGER,
    customer_name TEXT,
    customer_city TEXT,
    product_name  TEXT,
    product_price REAL,
    qty           INTEGER
);

INSERT INTO flat VALUES
    (101, 'Ada', 'London', 'Keyboard',  45.0, 1),
    (101, 'Ada', 'London', 'Mouse',     25.0, 2),
    (102, 'Ada', 'London', 'Monitor',  180.0, 1),
    (104, 'Chen', 'Singapore', 'Keyboard', 45.0, 2);

SELECT * FROM flat;

-- ANOMALY 1, UPDATE. Ada moves to Bristol. Her city appears three
-- times, so a partial update leaves the table disagreeing with itself.

UPDATE flat SET customer_city = 'Bristol' WHERE order_id = 101;

SELECT DISTINCT customer_name, customer_city FROM flat WHERE customer_name = 'Ada';

-- Two cities for one person. Nothing in the schema forbids it, because
-- the schema does not know those rows describe the same customer.
--
-- ANOMALY 2, DELETE. Remove order 102 and something unrelated goes
-- with it.

DELETE FROM flat WHERE order_id = 102;

SELECT count(*) AS monitor_rows_left FROM flat WHERE product_name = 'Monitor';

-- The price of a Monitor was only recorded on that row, so deleting an
-- order deleted a fact about a product.
--
-- ANOMALY 3, INSERT. A new product that nobody has ordered cannot be
-- recorded at all without inventing an order to hang it on.
--
-- The normalised schema this article's database uses has none of these:
-- the city lives once in customers, the price lives once in products,
-- and order_items holds only the link.

SELECT 'flat: rows mentioning Ada city' AS where_a_fact_lives, count(*) AS copies
FROM flat WHERE customer_name = 'Ada'
UNION ALL
SELECT 'normalised: rows with Ada city', count(*)
FROM customers WHERE name = 'Ada';

-- One row, one place, one update. That is the whole benefit, and the
-- cost is that answering a question now needs joins -- which is why
-- reporting systems often denormalise deliberately, accepting the
-- anomalies because the data is rebuilt rather than edited.

Guided tour

1. Start at raw. One table, one row per order, a multi-valued products column — not even 1NF.
2. Normalize to 1NF. Products become atomic — one row per (order, product) — but now customer, city and price all repeat across the exploded rows.
3. Normalize to 2NF. Orders and Products split out — price no longer repeats per line item. Redundant cells fall from 8 to 2.
4. Normalize to 3NF. Customers splits out from Orders — city no longer repeats per order for the same customer. Redundant cells hit 0.
5. Read what is left. order_id and customer still repeat across tables — that is a normal foreign key, not redundancy. Redundancy is a *fact* stored twice; a key linking two facts is the whole point of splitting them apart.

Where this goes wrong

Over-normalizing has a cost too: every extra table is another join at query time. Reporting and analytics workloads often deliberately denormalize back down for read speed, accepting the redundancy because the data is written once and read constantly. Normalize for correctness where writes happen; consider denormalizing where reads dominate.

Summing up

1NF removes multi-valued columns, 2NF removes columns that depend on only part of a composite key, and 3NF removes columns that depend on another non-key column rather than the key itself. Each step moves a fact into the one table where it is defined once, which is what makes update anomalies structurally impossible rather than merely unlikely.

A worked split

Take the wide table from earlier and normalise it properly.

Customers — one row per customer:

CREATE TABLE customers (
  id    INT PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  city  VARCHAR(100)
);

Products — one row per product, with the current price:

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

Orders — one row per order, referencing the customer:

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  ordered_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

Order lines — one row per product on an order, with the price as charged:

CREATE TABLE order_lines (
  order_id     INT NOT NULL REFERENCES orders(id),
  product_id   INT NOT NULL REFERENCES products(id),
  quantity     INT NOT NULL CHECK (quantity > 0),
  unit_price   DECIMAL(10,2) NOT NULL,   -- deliberately duplicated
  PRIMARY KEY (order_id, product_id)
);

That unit_price looks like a normalisation violation and is not. The price on the order is a historical fact that must never change; the price in products is the current one. They are two different facts that happen to be equal today, which is exactly the distinction normalisation asks you to make.

Questions people ask

Do I need to go beyond 3NF? Rarely. BCNF matters when a table has overlapping candidate keys; most schemas never hit that case.

Is normalisation bad for performance? It costs joins on reads and saves work on writes, while keeping tables smaller and more cacheable. For transactional systems it is usually a net win; for analytics, denormalised star schemas generally win.

What about NoSQL? Document databases embrace denormalisation as the default — embed related data in one document and read it in one operation. The trade is the same one, made in the other direction, and the consistency work moves to your application.

How do I know if my schema is normalised? Take each non-key column and ask what it actually depends on. If the answer is anything other than "the whole primary key", it belongs elsewhere.

Should every table have a surrogate key? A single-column integer or UUID key is easier to reference and to change than a natural key, and most schemas use one. Keep a unique constraint on the natural key as well, so the real rule is still enforced.

Can I normalise an existing production table? Yes, incrementally: create the new tables, backfill, write to both for a period, migrate readers, then drop the old columns. Doing it in one step on a live system is how outages happen.

Recap in one screen

• Normalisation removes redundancy so that each fact is stored once.
• Redundancy causes update, insert and delete anomalies — the concrete reasons the rules exist.
• 1NF: one value per cell. 2NF: depend on the whole key. 3NF: depend on nothing but the key.
• Denormalise deliberately, for measured read paths, with a plan for keeping copies consistent.
• Historical values, such as the price charged on an order, are not redundancy — they are different facts.

Keys, and why they carry the design

Normalisation is a statement about dependencies, and keys are how those dependencies get enforced.

Primary key — the column, or set of columns, that identifies a row uniquely. It is NOT NULL by definition and is the anchor everything else references.

Foreign key — a column that references another table's primary key. It is what stops an order pointing at a customer who does not exist, and what ON DELETE CASCADE or ON DELETE RESTRICT hangs off.

Candidate key — any column set that could serve as the primary key. An email address and a national insurance number might both be candidates; you pick one as primary and enforce the others with UNIQUE.

Composite key — a key made of several columns, common in the junction tables that model many-to-many relationships.

The junction table is the pattern to remember, because it is how normalisation handles relationships that a single foreign key cannot express. Students and courses are many-to-many, so neither table can hold a reference to the other; a third table with (student_id, course_id) as its primary key does the job, and any facts about the enrolment itself — the grade, the enrolment date — belong there too.

A last practical note: declare your foreign keys. Enforcing referential integrity in application code instead means the database will eventually contain rows the application says are impossible, usually inserted by a migration script at two in the morning.