Normalization is the process of structuring tables so that each fact is stored once. The normal forms are a formal ladder, but the practical goal is singular: eliminate the situations where the same truth is written in two places and can disagree.
What denormalised data actually costs
The classic anomalies are worth naming because each is a bug class you will otherwise meet in production:
- Update anomaly — a customer's address is stored on every order. They move, you update some rows, and the database now holds two different truths.
- Insert anomaly — you cannot record a new product until someone orders it, because product data lives on the order row.
- Delete anomaly — deleting the last order for a supplier erases the supplier's contact details, which lived nowhere else.
The forms, in the order that matters
-- Not normalised: repeating group, duplicated customer facts
CREATE TABLE orders (
id bigserial PRIMARY KEY,
customer_name text,
customer_email text,
items text -- 'SKU1:2, SKU2:1' <- a list in a column
);
-- Normalised: each fact in one place
CREATE TABLE customer (
id bigserial PRIMARY KEY,
email citext NOT NULL UNIQUE,
name text NOT NULL
);
CREATE TABLE "order" (
id bigserial PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customer(id),
placed_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_line (
order_id bigint NOT NULL REFERENCES "order"(id) ON DELETE CASCADE,
sku text NOT NULL REFERENCES product(sku),
quantity int NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, sku)
);- 1NF — no repeating groups; one value per column. The `items` string above violates it.
- 2NF — no partial dependency on part of a composite key.
- 3NF — no non-key column depending on another non-key column. Most well-designed schemas land here.
- Beyond 3NF the forms address progressively rarer anomalies and are rarely the deciding factor in application schemas.
The one denormalisation that is almost always correct
Historical facts must be copied, not referenced. An order line stores the price *at the time of purchase*, not a foreign key to the current product price. This looks like duplication and is not — it is a different fact. Getting this wrong means last year's invoices silently change when someone edits a price.
When to denormalise deliberately
- Read performance measured to be a problem, not assumed to be one.
- Point-in-time facts, as above.
- Aggregates that are expensive to compute and tolerable slightly stale — kept correct by a job or trigger, not by hope.
- Analytical tables, where star schemas trade update anomalies for query speed on purpose.
Each of these is a decision with a maintenance cost. Record it, ideally as an architecture decision record, because the next engineer will otherwise read it as a mistake and 'fix' it.
Frequently Asked Questions
Is third normal form enough?
For the large majority of application schemas, yes. Higher forms address anomalies that rarely arise in practice, and pursuing them can produce joins nobody benefits from.
Does normalization hurt performance?
It adds joins, which are cheap on indexed columns and routinely faster than the table scans denormalised wide tables encourage. Measure before denormalising; the intuition is wrong more often than not.
Do document databases need this?
The anomalies do not disappear because the storage changed. Duplicating a fact across documents creates exactly the same update problem, with less help from the database in detecting it.
References
- PostgreSQL Documentation — Data Definition — PostgreSQL
- PostgreSQL Documentation — Indexes — PostgreSQL
About Jishu Labs
Jishu Labs is a software development company founded in 2016. We build custom software, AI/ML systems, and full-stack web and mobile applications for clients, and we make eight AI tools for software teams.