Skip to content
DiaGO

Normalization

1NF through BCNF, driven by the anomalies they prevent - and an honest word on when to stop.

Normalization restructures tables so that every fact is stored exactly once. It is not an academic ritual: each normal form eliminates a specific class of bugs — the anomalies — that a badly shaped table forces your application code to work around. Start from the entities you sketched during ER modeling, then check them against the rules below.

Consider a table a team created because "joins were annoying":

CREATE TABLE order_report (
  order_id       INT,
  customer_name  TEXT,
  customer_email TEXT,
  product_id     INT,
  product_name   TEXT,
  unit_price     DECIMAL(10,2),
  quantity       INT,
  PRIMARY KEY (order_id, product_id)
);

Each row is one order line: an order with three products occupies three rows, each repeating the customer's name and email.

The three anomalies

Every argument for normalization reduces to three concrete failure modes:

AnomalyWhat happens in order_report
UpdateA customer changes her email. It is repeated on every line of every order she ever placed; miss one row and the table now holds two contradictory emails.
InsertionYou want to catalog a new product before anyone orders it. You cannot: a row needs an order_id, so the product waits or gets a fake order.
DeletionAn order is cancelled and its rows deleted. If it was the only order containing product 42, that product's name and price vanish with it.

These are not style complaints. Each one is defensive code you would otherwise have to write and test — multi-row updates, sentinel rows, "do not really delete" flags.

Functional dependencies

A functional dependency X -> Y means X determines Y: any two rows that agree on X must agree on Y. The dependencies in order_report are:

order_id               -> customer_name, customer_email
product_id             -> product_name, unit_price
(order_id, product_id) -> quantity

The key is the pair (order_id, product_id) — the only combination that determines every column. That composite key is what the next two normal forms interrogate.

First normal form: atomic values

1NF requires that every column hold a single atomic value and that there are no repeating groups — no products column containing "42:2, 17:1, 88:5", no product_1, product_2, product_3 columns. Such shapes make even basic queries ("which orders contain product 17?") string-parsing exercises. Our one-row-per-line table already satisfies 1NF; its problems live deeper.

Second normal form: no partial dependencies

2NF forbids a non-key column from depending on only part of a composite key. Here, product_name and unit_price depend on product_id alone, and customer_name and customer_email depend on order_id alone — both partial dependencies. Split the facts by what actually determines them:

CREATE TABLE orders (
  order_id       INT PRIMARY KEY,
  customer_name  TEXT,
  customer_email TEXT
);
 
CREATE TABLE products (
  product_id   INT PRIMARY KEY,
  product_name TEXT,
  unit_price   DECIMAL(10,2)
);
 
CREATE TABLE order_lines (
  order_id   INT REFERENCES orders,
  product_id INT REFERENCES products,
  quantity   INT,
  PRIMARY KEY (order_id, product_id)
);

A product now exists independently of any order, and its name is stored once. The insertion and deletion anomalies for products are gone.

Third normal form: no transitive dependencies

One problem remains: customer_email still travels with every order. The chain is order_id -> customer -> customer_email — the email is a fact about the customer, not about the order. 3NF forbids such transitive dependencies, where a non-key column depends on another non-key column rather than on the key directly:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name  TEXT,
  email TEXT
);
 
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT REFERENCES customers
);

Each table now states one kind of fact about one kind of thing — the data-layer cousin of the Single Responsibility Principle. Updating an email touches exactly one row.

BCNF in one paragraph

Boyce-Codd normal form tightens 3NF: every determinant — every left-hand side of a functional dependency — must be a candidate key. A 3NF schema can violate BCNF only in a narrow situation involving overlapping composite candidate keys, the classic example being a bookings table where both (room, time_slot) and (instructor, time_slot) are keys and instructor -> room. Treat this as an advanced check: most 3NF schemas are already in BCNF, and in the rare cases where they differ, decomposing to BCNF can cost you the ability to enforce some dependencies with keys alone.

When to stop

3NF is the practical default for OLTP systems — the databases taking orders and payments under concurrent writes. Beyond it, returns diminish fast. Denormalization — deliberately reintroducing redundancy for read speed — is a legitimate, documented trade-off, not a sin. The rule that matters: denormalize from a normalized design, not instead of one. A reporting table derived from 3NF sources can be rebuilt when it drifts; a schema that was never normalized has no source of truth to rebuild from. This is the same reasoning behind separate read models in microservice architectures.

Reach for it

  • The schema backs a transactional system with frequent concurrent writes
  • Multiple features or services update the same underlying facts
  • Read patterns are still unknown and you need a neutral baseline

Avoid it

  • The table is a derived, rebuildable read model for analytics or reporting
  • A measured query is too slow and the redundant copy is documented and refreshed automatically
  • The column is an immutable historical snapshot such as price at time of sale

Common mistakes

  • Meaningless lookup tables. A status column holding "shipped" does not need a statuses table with surrogate id 3 unless statuses carry their own attributes. A CHECK constraint or enum enforces validity without an extra join.
  • Treating normal forms as a purity contest. The goal is eliminating anomalies your system actually risks, not reaching the highest-numbered form.
  • Denormalizing prematurely. "Joins are slow" is a hypothesis, not a fact. Well-indexed 3NF joins are fast in every mainstream database; measure before you duplicate data you will then have to keep consistent by hand.

Practice

A library tracks borrowing in one table: loans(loan_id, member_name, member_email, isbn, book_title, author_name, due_date), one row per borrowed copy. First, list its functional dependencies. Second, name one concrete update, insertion, and deletion anomaly. Third, decompose it to 3NF, showing each table and its key. Finally, the head librarian wants a nightly overdue report that would join four of your tables — decide whether to denormalize for it, and justify your answer in one sentence.