Database Normalization Explained (1NF, 2NF, 3NF)

Normalization is a set of checks on your tables: is every fact stored exactly once, in the right place? Each 'normal form' removes one class of problems. In practice, aiming for 3NF covers almost every application.

An unnormalized table lets the same fact live in many rows; when the fact changes, some rows get updated and others do not, and your data quietly disagrees with itself. Each normal form makes one version of that failure impossible.

1NF — one value per cell

A column holds a single value of a single type. 'tags: sql, postgres, erd' in one cell is 1NF violation: you cannot filter or join on it cleanly. Fix: a post_tags join table — which conveniently also fixes the many-to-many.

The other classic 1NF problem is repeating groups: order_1_product, order_1_qty, order_2_product across a wide row. The tell is any column name with a number in it; the fix is a child table, one row per item, linked back with a foreign key.

-- violates 1NF: three copies of the same fact pattern in one row
CREATE TABLE orders_bad (
  id SERIAL PRIMARY KEY,
  customer_name VARCHAR(120),
  product_1 VARCHAR(80), qty_1 SMALLINT,
  product_2 VARCHAR(80), qty_2 SMALLINT,
  product_3 VARCHAR(80), qty_3 SMALLINT
);

-- 1NF: one row per ordered item
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id)
);

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  line_no SMALLINT NOT NULL,
  product_id INTEGER NOT NULL REFERENCES products(id),
  qty SMALLINT NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, line_no)
);

An ARRAY or JSONB column is also a 1NF compromise, even where the database supports it. Sometimes it is the right one — a read-only snapshot of line items, say — but it should be a decision, not a habit.

2NF — no partial dependencies

Only relevant with composite keys: a column must depend on the whole key, not half of it. In order_items keyed by (order_id, product_id), a column product_name depends on product_id alone — if the product is renamed, some rows update, others forget. Fix: keep product_name in products.

The test: knowing only half the key, would you still know this value? product_name from just a product_id: yes, so it does not belong here. quantity from just an order_id: no — it is a fact about this line, so it stays.

CREATE TABLE order_items_bad (
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty SMALLINT NOT NULL,
  product_name VARCHAR(120),   -- depends on product_id only
  product_price NUMERIC(10,2), -- depends on product_id only
  PRIMARY KEY (order_id, product_id)
);

-- after 2NF: the product facts live in products
CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  qty SMALLINT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL, -- see below: this one is legal
  PRIMARY KEY (order_id, product_id)
);

One subtlety: unit_price is not a 2NF violation, though product_price was. It records the price actually charged on that order, which can differ from the catalog price after a repricing — a fact about the pair, not about the product. Deciding which fact a column records is the whole game in 2NF.

3NF — no transitive facts

A column must depend on the key, 'nothing but the key'. In customers(id, city, country), country depends on city, not on the customer — a fact about cities smuggled into customers. Fix: a cities table, or accept the denormalization deliberately and document it.

Transitive dependencies appear whenever a column is a natural-language key for something else: zip code determines city, SKU determines product name. The failure mode is the update anomaly: fix a city's country in one row, miss it in a hundred, and the table now asserts two countries for one city.

-- country depends on city, not on customer id
CREATE TABLE customers_bad (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(80) NOT NULL,
  country VARCHAR(80) NOT NULL
);

-- 3NF: city facts live with cities
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city_id INTEGER NOT NULL REFERENCES cities(id)
);

CREATE TABLE cities (
  id SERIAL PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  country_code CHAR(2) NOT NULL REFERENCES countries(code)
);

Whether to reach for a cities table is a judgment call. For an address book, keep city as free text and accept that 'Köln' and 'Cologne' differ. For a shipping calculator that prices by country, it stops being pedantry.

-- violates 1NF / 3NF
CREATE TABLE orders_bad (
  id SERIAL PRIMARY KEY,
  customer_name VARCHAR(120),
  customer_city VARCHAR(80),
  tags TEXT  -- 'sql, postgres'
);

-- normalized
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(80)
);
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id)
);
CREATE TABLE order_tags (
  order_id INTEGER REFERENCES orders(id),
  tag_id INTEGER REFERENCES tags(id),
  PRIMARY KEY (order_id, tag_id)
);

Beyond 3NF, briefly

BCNF matters when a table has overlapping composite candidate keys — rare in application schemas, common in textbook exercises. 4NF and 5NF concern multi-valued facts, mostly in warehouse staging tables; a 3NF schema that stores one kind of fact per table rarely needs them.

Denormalization, deliberately

Denormalization is a tool, not a sin — caches and counters exist. The rule: normalize first, denormalize on measured evidence, and let the diagram show the choice so the next developer does not 'fix' it blind. The Describe feature in ERDGenerator proposes 3NF schemas by default, so you start clean.

Legitimate denormalizations share a pattern: an expensive value is materialized and kept in sync by a known writer — orders.total, a reply_count, a cached rating. Each needs an owner (trigger, transactional update, or scheduled rebuild) and a comment saying which. An unowned duplicate column is not denormalization, it is a bug you have not found yet.

Common mistakes

• Treating a report or a UI form as the schema. Store each fact once and let queries reshape it.
• Confusing 'one table per dropdown' with normalization. The point is each fact stored exactly once — a country list that never changes might be fine as a CHECK constraint.
• Breaking 1NF with JSONB 'flexible columns' queried like relational columns anyway, minus indexes and constraints.
• Applying 2NF thinking to tables with single-column keys and never noticing the transitive dependency that 3NF catches — the forms overlap; run all the checks.
• Denormalizing for performance before measuring. A correct join on indexed keys is fast; an inconsistent cached column is a slow-motion outage.
• Keeping product_name on order_items 'for history' without deciding which fact it records — current name or name at purchase time. Both are valid; ambiguity is not.

Best practices

• For every table, state in one sentence what fact it stores. If the sentence contains 'and', check for a hidden second entity.
• Test each non-key column with the half-key and transitive-dependency questions above; it takes minutes on a small schema.
• Let foreign keys do the deduplication. A referenced lookup table with UNIQUE on the natural key is the database enforcing 3NF for you.
• When you do denormalize, write the sync into the same transaction as the source change and note the pairing so it survives team turnover.
• Check your ER diagram against the DDL after refactors: normalization drift is invisible in code review and obvious in a diagram.

Frequently asked questions

Is 3NF always the right target? For transactional (OLTP) schemas, essentially yes. For BI star schemas, deliberate denormalization — wide fact tables, duplicated dimensions — is standard, because reads are known and updates are batched. Normalize the source of truth; denormalize the copies.

Does normalization hurt performance? It adds joins, which cost something, but they run on indexed keys and are rarely the bottleneck, while the update anomalies it prevents are expensive to clean up. Measure before trading integrity for speed.

What is the difference between 2NF and 3NF in one line? 2NF forbids non-key columns depending on part of a composite key; 3NF forbids non-key columns depending on other non-key columns. A single-column key cannot violate 2NF but can still violate 3NF.

How do I fix a legacy table that violates everything? Incrementally: extract the repeating group first (1NF), then move dependent-on-part facts to their referenced tables (2NF), then peel off transitive facts (3NF). Each step is a small migration that leaves the app runnable.