Normalisation explained without the jargon

Normalisation is taught as five numbered "normal forms" that you memorise for an exam and never think about again. That is a shame, because the underlying idea is simple, genuinely useful, and you can derive the rules from it.

The idea: store each fact in exactly one place.

Everything else follows. If a fact lives in one place, you cannot have two copies disagreeing, you cannot forget to update one of them, and you cannot lose it by deleting an unrelated row.

The problem, concretely

Here is the library as a single table — the shape a spreadsheet naturally takes:

 id | member_name    | member_email       | book_title          | author_name     | borrowed_on
----+----------------+--------------------+---------------------+-----------------+------------
  1 | Kavita Joshi   | kavita@example.com | The Long Monsoon    | Meera Kulkarni  | 2026-08-01
  2 | Kavita Joshi   | kavita@example.com | Swami and Friends   | R K Narayan     | 2026-08-14
  3 | Ravi Gadgil    | ravi@example.com   | The Long Monsoon    | Meera Kulkarni  | 2026-08-03

Four things are wrong with it, and they have names.

Update anomaly. Kavita changes her email. You must update every row she appears in, and if you miss one — a WHERE clause that was slightly wrong, a crash halfway — you now have two different emails for one person and no way to know which is right.

Insert anomaly. You cannot record a book nobody has borrowed yet. There is no row to put it in. You also cannot record a member before their first loan. The table cannot represent facts you need.

Delete anomaly. Delete the last loan of "Swami and Friends" and you have deleted the book, its author, and the fact that it exists. Information vanishes as a side effect of an unrelated operation. This is the worst of the four.

Inconsistency. Nothing stops row 1 saying Meera Kulkarni and row 3 saying meera kulkarni. Now GROUP BY author_name gives you two authors, your report is wrong, and nobody notices for a year.

Every one of these is the same root cause: the author's name is stored in many places.

The normal forms, derived

First normal form: one value per cell

member_name   | phone_numbers
--------------+----------------------------
Kavita Joshi  | 9876543210, 9123456789

Violates 1NF. The costs are immediate: you cannot index it usefully, you cannot query "who has this number" without LIKE '%…%', and a number containing a comma breaks everything.

CREATE TABLE member_phones (
  member_id bigint NOT NULL REFERENCES members(id) ON DELETE CASCADE,
  phone text NOT NULL,
  PRIMARY KEY (member_id, phone)
);

1NF also forbids repeating groups — phone1, phone2, phone3 columns. Same problem wearing a different hat: what happens when someone has four?

The honest exception: PostgreSQL has real array and jsonb types, and using them is sometimes right. That is a deliberate denormalisation, which is the next lesson. Know that you are doing it.

Second normal form: no partial dependency on a composite key

Only applies when your primary key has more than one column.

CREATE TABLE book_categories (
  book_id bigint,
  category_id bigint,
  category_name text,          -- depends on category_id ALONE
  PRIMARY KEY (book_id, category_id)
);

category_name depends on only part of the key. So it is repeated for every book in that category, and renaming the category means updating many rows. Move it to categories.

Third normal form: no dependency on a non-key column

CREATE TABLE members (
  id bigint PRIMARY KEY,
  name text,
  pincode text,
  city text,              -- depends on pincode, not on id
  state text              -- depends on pincode, not on id
);

city and state are determined by pincode, which is not the key. This is a transitive dependency, and it means every member in 411038 repeats "Pune, Maharashtra" — with a chance each time for a typo.

CREATE TABLE pincodes (pincode text PRIMARY KEY, city text NOT NULL, state text NOT NULL);

3NF is where you should aim by default. The three forms above cover essentially every practical case.

The rest, briefly

BCNF is 3NF tightened for a rare case with overlapping candidate keys. 4NF and 5NF concern multi-valued dependencies. You will hit these perhaps once in a career, and when you do you will recognise the smell — a table where two independent lists have been crossed with each other — and split it without needing the vocabulary.

The rule of thumb that makes this usable: aim for 3NF, know when you are leaving it, and be able to say why.

The normalised library

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

CREATE TABLE books (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  author_id bigint NOT NULL REFERENCES authors(id),
  isbn text UNIQUE,
  copies int NOT NULL DEFAULT 1 CHECK (copies >= 0)
);

CREATE TABLE members (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  joined date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE loans (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id bigint NOT NULL REFERENCES books(id),
  member_id bigint NOT NULL REFERENCES members(id),
  borrowed_on date NOT NULL DEFAULT CURRENT_DATE,
  returned_on date,
  CHECK (returned_on IS NULL OR returned_on >= borrowed_on)
);

Now check the four anomalies against it:

Kavita's email is in one row. Updating it is one statement that cannot half-succeed.
A book with no loans is just a row in books.
Deleting a loan deletes a loan. Nothing else.
An author's name exists once, so it cannot disagree with itself.

That is the whole payoff, and it is worth more than it sounds.

What normalisation costs

Be honest about it, because the next lesson is about when to pay less.

Joins. "Show me loans with member and book names" now needs three joins instead of none. Usually cheap on indexed foreign keys, and usually not your bottleneck — but not free.

More tables to hold in your head. A thirty-table schema is harder to learn than eight.

Some queries get genuinely awkward. A report crossing six tables is harder to write and harder to read.

A count you need constantly is now a computation. "How many loans does this member have" is a count with a join rather than reading a column.

The counter-argument, stated fairly

You will meet the claim that normalisation is an artefact of 1970s disk prices, and that with cheap storage you should denormalise freely.

Storage was never the main point. The point is correctness: one copy of a fact cannot contradict itself. That argument is untouched by disks getting cheaper, and duplicated data still goes out of sync in 2026 exactly as it did in 1976.

What has changed is that some workloads — analytics, read-heavy caches, event logs — do better denormalised, and that we now have tools (materialised views, generated columns) to denormalise safely. That is a real change, and it is the next lesson.

The practical procedure

List the entities — the nouns. Book, author, member, loan, category.
One table per entity.
List the facts and put each in the table it belongs to. Ask: what is this a fact about? An author's name is a fact about the author, so it lives in authors.
Connect them with foreign keys.
Check the four anomalies. Can I add a book with no loans? Can I rename an author in one statement? Does deleting a loan destroy anything else? Can two rows disagree?
Only then consider denormalising, for a measured reason.

Step 3 is the whole method. "What is this a fact about" answers almost every modelling question you will have, and it is the question to ask when you are stuck.

Check your work

The one-sentence idea. Store each fact in exactly one place.

The four anomalies. Update, insert, delete, inconsistency.

Which is worst, and why. The delete anomaly — information vanishes as a side effect of an unrelated operation.

1NF. One value per cell, and no repeating groups.

Why a comma-separated list is bad in practice. Cannot index, cannot query without LIKE, breaks on a value containing the separator.

2NF. No column depending on only part of a composite key.

3NF. No column depending on a non-key column — a transitive dependency.

What to aim for. 3NF, knowingly.

Four costs of normalisation. Joins, more tables, awkward reports, counts become computations.

Why "storage is cheap" misses the point. The point is correctness, not space.

The question that answers most modelling problems. What is this a fact about?

Practice

Build the single flat table above and demonstrate all four anomalies with real statements.
Try to insert a book nobody has borrowed into it. Describe what you have to invent.
Delete the only loan of a book and show what information you lost.
Normalise it to the four-table version and repeat all three. Confirm each now works.
Store two phone numbers in one column, then write the query to find a member by number.
Split it into member_phones and write the same query.
Find a 2NF violation in a schema you have written or seen.
Build the members-with-city-and-pincode table and show the transitive dependency by entering two different cities for the same pincode.
Fix it with a pincodes table and show that the contradiction is now impossible.
Take a spreadsheet somebody actually uses — an attendance sheet, a stock list — and normalise it. This is the most valuable exercise in the module.
For each column in it, answer "what is this a fact about?"
Count the joins your normalised version needs for its most common report. Decide whether you mind.
Write down one place you would deliberately denormalise, and the reason.

Official documentation

PostgreSQL — Data definition — Tables, constraints, defaults and the rest of the toolkit this lesson uses.
PostgreSQL — Constraints — CHECK, UNIQUE, NOT NULL and foreign keys, which are how a normalised schema defends itself.
Wikipedia — Database normalization — Not official, but the clearest free summary of all the normal forms with worked examples, including the ones this lesson skips.

Next: when to break these rules deliberately.