Normalisation Without Dogma

Every table that has ever gone wrong has the same shape in the incident report: the same fact lived in more than one place and the two copies disagreed. Nobody writes that bug deliberately. It arrives one column at a time, each addition reasonable on its own, until a field that describes the customer is sitting on the invoice line.

Normalisation is the name for the cleanup, and it gets taught as a numbered ladder. That framing makes it easy to recite and hard to use, because the ladder describes the result rather than the reason for it. The reason is narrower: one fact should be stored once, so one change is enough to keep it true. A table in third normal form is a table where updating a fact touches one row.

The work is not "apply 3NF". The work is finding a fact that currently exists in two places and deciding what to do about it. Each of the three forms below is the answer to a specific way that duplication appears.

The bug is an update that has to be repeated

A table with duplicated facts supports three kinds of trouble, and it helps to name them because the names appear in every review you will ever sit in.

An update anomaly is a fact that has to be changed in several rows at once, where missing one row leaves the table contradicting itself. An insert anomaly is a fact you cannot record because the table insists on attaching it to something unrelated. A delete anomaly is the reverse: removing the last row about one thing destroys the only record of another. The fix for all three is the same shape, which is why one process covers them.

First normal form prevents repeating groups from drifting apart

A table is in first normal form when every column holds one value of one type. The violation people actually write is a column that carries several facts at once: a comma-separated list of tags, a JSON blob of line items, a phone number column that sometimes holds two numbers. The list looks convenient and it breaks the moment you need to query inside it.

The other violation is subtler. Duplicating a column down a group of rows to avoid a join is also a repeating group, even though each cell holds one value:

CREATE TABLE invoice_line_flat (
  invoice_no       INT NOT NULL,
  customer_email   VARCHAR(255) NOT NULL,
  customer_city    VARCHAR(64) NOT NULL,
  line_no          INT NOT NULL,
  product_name     VARCHAR(64) NOT NULL,
  unit_price_cents INT NOT NULL,
  qty              INT NOT NULL,
  PRIMARY KEY (invoice_no, line_no)
);

INSERT INTO invoice_line_flat VALUES
 (1001, 'ana@example.com', 'Lisbon', 1, 'WP Care Plan',     1200, 2),
 (1001, 'ana@example.com', 'Lisbon', 2, 'Courier Timeline', 4500, 1),
 (1002, 'bo@example.com',  'Porto',  1, 'Courier Timeline', 4500, 3);

Three rows, and the customer's city appears three times because it arrived with the import. Now correct one of them:

UPDATE invoice_line_flat
   SET customer_city = 'Lisboa'
 WHERE invoice_no = 1001 AND line_no = 1;

+------------+---------+-----------------+---------------+
| invoice_no | line_no | customer_email  | customer_city |
+------------+---------+-----------------+---------------+
|       1001 |       1 | ana@example.com | Lisboa        |
|       1001 |       2 | ana@example.com | Lisbon        |
|       1002 |       1 | bo@example.com  | Porto         |
+------------+---------+-----------------+---------------+

One invoice now has two cities. The statement was correct, the row it targeted was correct, and the table is wrong. No constraint fired because no constraint was violated: nothing in the schema says those rows must agree, and nothing can, because the schema does not know that those three rows are the same customer.

The repair is a separate table where the customer appears once, with a foreign key from the line. The repeating group disappears because the fact no longer has anywhere to repeat.

CREATE TABLE customer (
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  city  VARCHAR(64)  NOT NULL,
  CONSTRAINT uq_customer_email UNIQUE (email)
);

CREATE TABLE invoice_line (
  invoice_no       INT NOT NULL,
  line_no          INT NOT NULL,
  customer_id      INT NOT NULL,
  product_name     VARCHAR(64) NOT NULL,
  unit_price_cents INT NOT NULL,
  qty              INT NOT NULL,
  PRIMARY KEY (invoice_no, line_no),
  CONSTRAINT fk_invoice_line_customer
    FOREIGN KEY (customer_id) REFERENCES customer (id) ON DELETE RESTRICT
);

Correcting a city is now one UPDATE on one row, and no query can return two answers for the same customer. That is the entire benefit, and it is worth stating plainly because "one value per cell" sounds like a rule about tidiness rather than a rule about correctness.

Second normal form prevents a fact from depending on part of the key

A table with a composite key can have a column that depends on only one part of it. The classic example is a line item that stores the product name next to the product id. The line is identified by (order_no, line_no), and product_name depends on product_id alone.

CREATE TABLE order_line (
  order_no     INT NOT NULL,
  line_no      INT NOT NULL,
  product_id   INT NOT NULL,
  product_name VARCHAR(64) NOT NULL,
  qty          INT NOT NULL,
  PRIMARY KEY (order_no, line_no)
);

INSERT INTO order_line VALUES
 (1001, 1, 7, 'Courier Timeline', 1),
 (1002, 1, 7, 'Courier Timeline', 2);

UPDATE order_line SET product_name = 'Courier Timeline (legacy)' WHERE order_no = 1002;

+----------+---------+------------+---------------------------+-----+
| order_no | line_no | product_id | product_name              | qty |
+----------+---------+------------+---------------------------+-----+
|     1001 |       1 |          7 | Courier Timeline          |   1 |
|     1002 |       1 |          7 | Courier Timeline (legacy) |   2 |
+----------+---------+------------+---------------------------+-----+

One product, two names. The rename was meant for the catalogue and landed on an order. Six months later nobody can tell which spelling the customer actually saw, so the invoice reprint is guesswork.

The repair is the same move applied one level down: pull the product into a table keyed by product_id and keep only the id on the line. Second normal form is nothing more than that, and it only applies to tables with composite keys, because with a single-column key there is no proper subset for a column to depend on.

Third normal form prevents a fact from depending on another non-key column

The third case is a column that depends on the key through a chain. The vendor id determines the vendor name, and the vendor name is neither the key nor determined by the key alone in a way you can enforce:

CREATE TABLE vendor_contact (
  id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  vendor_id     INT NOT NULL,
  vendor_name   VARCHAR(64) NOT NULL,
  vendor_tax_id VARCHAR(32) NOT NULL,
  contact_email VARCHAR(255) NOT NULL
);

INSERT INTO vendor_contact (vendor_id, vendor_name, vendor_tax_id, contact_email) VALUES
 (3, 'Northwind Hosting', 'PT5001', 'ops@northwind.example'),
 (3, 'Northwind Hosting', 'PT5001', 'billing@northwind.example');

UPDATE vendor_contact SET vendor_name = 'Northwind Lda' WHERE id = 2;

+----+-----------+-------------------+---------------+-----------------------+
| id | vendor_id | vendor_name       | vendor_tax_id | contact_email         |
+----+-----------+-------------------+---------------+-----------------------+
|  1 |         3 | Northwind Hosting | PT5001        | ops@northwind.example |
|  2 |         3 | Northwind Lda     | PT5001        | billing@northwind.example |
+----+-----------+-------------------+---------------+-----------------------+

One tax id, two names, and the tax id is the value the business actually trusts. The department is the thing being modelled, so vendor_id belongs in the contact table and the vendor's name and tax id belong in their own table keyed by vendor_id.

There is a version of this you may have met as a note about a candidate key, and it is worth one sentence. When the chain runs through a column that is itself unique, some textbooks allow the table to pass third normal form anyway, on the grounds that the dependency is on a key you could have chosen. The diagnostic worth keeping is not the exception but the question: does changing this value require changing more than one row? If yes, the fact is stored twice, whatever the form says.

Denormalisation is a bet that reads outnumber writes

Sometimes you do this on purpose. A dashboard that aggregates millions of rows on every page load is the usual case, and so is an order that must keep the price the customer was charged even after the catalogue price changes. Both are legitimate, and both are exact copies of a fact that already exists somewhere else.

What you are buying is read speed and historical accuracy. What you are paying is the invariant you just gave up. The database can no longer tell you that the two copies agree, so something in your code has to, on every write path, forever. The cost is not the storage. The cost is that a fact now has several owners and one of them is a background job someone will forget to update.

The historical case is the one people conflate with denormalisation and it is not quite the same thing. An order line storing unit_price_cents is not a stale copy of the catalogue price, it is a different fact: the price agreed at the time of sale. The catalogue price can change and the order is still correct. Before you call something a cache, check whether it is really a second fact that happens to look like the first.

When you do keep a copy, three techniques make it survivable. Generated columns let the engine maintain the copy itself:

CREATE TABLE contact (
  id         INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(64) NOT NULL,
  last_name  VARCHAR(64) NOT NULL,
  full_name  VARCHAR(160)
    GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED
);

STORED computes the value on write and keeps it on disk, so reads pay nothing. VIRTUAL computes it on read and costs a little per query. Either way you cannot insert a wrong full name, which is the point. PostgreSQL spells the same feature GENERATED ALWAYS AS (...) STORED, and SQLite supports both STORED and VIRTUAL.

A summary table refreshed on a schedule is the next option, and it trades staleness for maintenance you do not have to write. The third is a trigger, the most powerful and the one I avoid unless the alternative is worse, because trigger logic is invisible to whoever debugs a slow insert at two in the morning.

Whichever you pick, write down what the copy is for and what invalidates it. A denormalised column with no owning code path is a bug with a delivery date.

Normalise until a change touches one row, then stop

The honest guidance is not "always 3NF". It is: start normalised, because a normalised schema fails loudly when two facts disagree and a denormalised one fails quietly. Then denormalise a specific column for a specific reason, with a constraint or a generator keeping it honest, and put the reason in the migration so the next person can judge whether it still holds.

What normalisation does not solve should be said as well. It cannot make an address unique, decide whether two records are the same person, or stop a well-meaning admin from merging two customers by hand. Those are domain rules, and the schema can only enforce them if someone writes them down as constraints. That is the next lesson.

The reports table is where the three forms show up together

CREATE TABLE report_row (
  report_id      INT NOT NULL,
  row_no         INT NOT NULL,
  customer_email VARCHAR(255) NOT NULL,
  customer_city  VARCHAR(64)  NOT NULL,
  product_id     INT NOT NULL,
  product_name   VARCHAR(64) NOT NULL,
  supplier_name  VARCHAR(64) NOT NULL,
  supplier_city  VARCHAR(64) NOT NULL,
  tags           VARCHAR(255) NOT NULL,
  qty            INT NOT NULL,
  PRIMARY KEY (report_id, row_no)
);

CREATE TABLE customer (
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  city  VARCHAR(64)  NOT NULL,
  CONSTRAINT uq_customer_email UNIQUE (email)
);

CREATE TABLE product (
  id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(64) NOT NULL,
  supplier_id   INT NOT NULL,
  CONSTRAINT fk_product_supplier
    FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE RESTRICT
);

CREATE TABLE report_row (
  report_id   INT NOT NULL,
  row_no      INT NOT NULL,
  customer_id INT NOT NULL,
  product_id  INT NOT NULL,
  qty         INT NOT NULL,
  PRIMARY KEY (report_id, row_no),
  CONSTRAINT fk_row_customer
    FOREIGN KEY (customer_id) REFERENCES customer (id) ON DELETE RESTRICT,
  CONSTRAINT fk_row_product
    FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE RESTRICT
);

CREATE TABLE report_row_tag (
  report_id INT NOT NULL,
  row_no    INT NOT NULL,
  tag       VARCHAR(32) NOT NULL,
  PRIMARY KEY (report_id, row_no, tag),
  CONSTRAINT fk_tag_row FOREIGN KEY (report_id, row_no)
    REFERENCES report_row (report_id, row_no) ON DELETE CASCADE
);