SQL / DATA MODELLING AND NORMALISATION
What normalisation is trying to prevent
Diagnose a table that stores the same fact twice, name the update, insertion and deletion anomalies it causes, and prove the contradiction with one query.
What you will learn
- Point at a column in a wide table and say which subject's fact it really belongs to.
- Name the update, insertion or deletion anomaly a duplicated column will cause.
- Detect existing contradictions with GROUP BY plus COUNT(DISTINCT ...) > 1 in HAVING.
- Explain why UNIQUE or CHECK cannot force two rows to agree on the same fact.
Understanding What normalisation is trying to prevent
Every table asserts facts, and each row should be a statement about exactly one thing. The trouble starts when a row is forced to carry facts about two things at once: a delivery row that also holds the supplier's phone number says both "delivery 2 contained yeast" and "Alderman Foods answers on 020-7100-4455". The second fact then gets copied onto every delivery from that supplier, and the database now holds several independent copies of one truth with no way to know which is authoritative. This is not a storage argument, since the bytes are trivial; it is about how many places a single truth can be wrong.
Because those copies are independent, ordinary single-row work pulls them apart, and the three classic failures are just the three write operations meeting the same duplication. An UPDATE aimed at one delivery changes the phone in one place and leaves the sibling rows contradicting it; an INSERT has nowhere to record a supplier you have signed up but not yet ordered from; a DELETE of a supplier's last delivery destroys the only copy of their phone number. These are the update, insertion and deletion anomalies, and naming which one you are looking at tells you which fact is living in the wrong table.
The reason care and discipline do not fix this is that SQL constraints describe a row or a column, not agreement between rows. UNIQUE, CHECK and NOT NULL are all perfectly satisfied by a table in which Alderman Foods has two different phone numbers, and the rule you actually want, "all rows naming this supplier must show the same phone", is a statement about a group of rows that no plain constraint expresses. Normalisation is the alternative: give each fact a table where its subject has exactly one row, so the phone is stored once and a PRIMARY KEY does the enforcing for free. After that the anomalies are not merely avoided, they are unrepresentable, because there is no second row left to disagree with.
CREATE TABLE deliveries (
delivery_id INTEGER,
supplier TEXT,
supplier_phone TEXT,
item TEXT
);
INSERT INTO deliveries VALUES
(1, 'Alderman Foods', '020-7100-4455', 'flour'),
(2, 'Alderman Foods', '020-7100-4455', 'yeast'),
(3, 'Alderman Foods', '020-7100-4455', 'salt'),
(4, 'Boyd Dairy', '020-7100-9911', 'butter');
-- The supplier changes its number and the fix lands on the row someone had open.
UPDATE deliveries
SET supplier_phone = '020-7100-4460'
WHERE delivery_id = 2;
SELECT supplier, supplier_phone, COUNT(*) AS rows_saying_this
FROM deliveries
GROUP BY supplier, supplier_phone
ORDER BY supplier, supplier_phone;Duplication is the disease and the update, insertion and deletion anomalies are its symptoms: a fact stored in many rows can be changed in one of them, and no ordinary SQL constraint makes the rest follow.
Worked examples
Deleting a delivery deletes a supplier
Removing the last row for a supplier destroys a fact that had nothing to do with that delivery.
CREATE TABLE deliveries (
delivery_id INTEGER,
supplier TEXT,
supplier_phone TEXT,
item TEXT
);
INSERT INTO deliveries VALUES
(1, 'Alderman Foods', '020-7100-4455', 'flour'),
(4, 'Boyd Dairy', '020-7100-9911', 'butter');
DELETE FROM deliveries WHERE delivery_id = 4;
SELECT COUNT(*) AS boyd_phone_records
FROM deliveries
WHERE supplier = 'Boyd Dairy';Example explained
Line 1Boyd Dairy's phone number exists in the database only because one delivery row happens to mention it.
Line 2The DELETE is a statement about delivery 4, yet it silently removes the supplier's contact detail too.
Line 3COUNT(*) returns 0: the supplier still exists in the real world and the database can no longer say so.
Line 4Nothing was violated, so no error is raised; the loss is invisible until someone needs the number.
A supplier with nothing to insert
Recording a supplier that has not delivered yet forces a placeholder row that corrupts counts.
CREATE TABLE deliveries (
delivery_id INTEGER,
supplier TEXT,
supplier_phone TEXT,
item TEXT
);
INSERT INTO deliveries VALUES
(1, 'Alderman Foods', '020-7100-4455', 'flour'),
(NULL, 'Coleman Grain', '020-7100-2277', NULL);
SELECT COUNT(*) AS delivery_rows, COUNT(item) AS items_delivered
FROM deliveries;Example explained
Line 1delivery_id and item are NULL because there is no delivery; the row exists only to store a phone number.
Line 2COUNT(*) reports 2, so every "how many deliveries" figure taken from this table is now one too high.
Line 3COUNT(item) ignores NULLs and reports 1, so the right answer is only reachable if each query remembers to skip placeholders.
Line 4The table now means two things at once, which is why NOT NULL on delivery_id would have blocked the supplier instead.
Finding facts that already disagree
One aggregate query tells you whether a duplicated column has already split into conflicting values.
CREATE TABLE deliveries (
delivery_id INTEGER,
supplier TEXT,
supplier_phone TEXT,
item TEXT
);
INSERT INTO deliveries VALUES
(1, 'Alderman Foods', '020-7100-4455', 'flour'),
(2, 'Alderman Foods', '020-7100-4460', 'yeast'),
(3, 'Boyd Dairy', '020-7100-9911', 'butter'),
(4, 'Boyd Dairy', '020-7100-9911', 'cream');
SELECT supplier, COUNT(DISTINCT supplier_phone) AS phone_versions
FROM deliveries
GROUP BY supplier
HAVING COUNT(DISTINCT supplier_phone) > 1;Example explained
Line 1COUNT(DISTINCT supplier_phone) asks how many different values one supplier is associated with, so anything above 1 is a contradiction.
Line 2HAVING filters after aggregation, so the result lists offending subjects rather than offending rows.
Line 3Boyd Dairy is absent because its two copies still agree, which is luck rather than a guarantee.
Line 4Run this on every repeated column of a table you inherit before trusting any report built on it.
Important notes
Anomalies come from the shape of the table, not its size: a four-row table can already contradict itself, and cleaning the values afterwards does nothing to stop it recurring.
Copies the database maintains itself, such as indexes, are not anomalies; the risk is copies in base tables that only human discipline keeps in step.
Common mistakes
Treating duplication as a disk-space question and shrugging it off, then later getting two reports that read the same table and quote different phone numbers for one supplier.
Adding UNIQUE(supplier_phone) to "stop duplicate data": valid inserts start failing because one supplier legitimately has many deliveries, while a supplier with two conflicting numbers still passes.
Relying on the team always writing UPDATE ... WHERE supplier = '...': one forgotten WHERE clause, one CSV import or one hand-edited row splits the fact again, and no constraint reports it.
Try it yourself
Change, predict, then run
Build the deliveries table from the main example, change supplier_phone on exactly one Alderman Foods row, then write the query that lists each supplier with its number of distinct phone numbers. Now insert a supplier that has signed up but delivered nothing, and note which columns you were forced to leave NULL.
Open the SQL workspaceCheck your understanding
An order_lines table repeats each customer's shipping address on every line. A colleague proposes UNIQUE(shipping_address) so the address "cannot get out of sync". What is wrong with that reasoning?
- UNIQUE is not enforced for NULL values, so lines with a missing address escape the check.
- UNIQUE only applies to rows inserted after it is declared, so the existing conflicting rows survive.
- One customer legitimately repeats the same address across many lines, so UNIQUE rejects valid rows, and it still permits one customer to carry two different addresses.
- UNIQUE would work if it were declared on (customer_id, shipping_address) instead.
Show answer
The constraint aims at the wrong relationship. Repetition of the address value is correct here, because one customer has many lines, so UNIQUE blocks legitimate inserts, and nothing in it prevents line 1 and line 2 of the same customer from showing different addresses, which is the actual anomaly. The NULL option is tempting because NULLs really are exempt from UNIQUE in most engines, but that is incidental: with shipping_address declared NOT NULL the constraint would still reject valid rows and still allow the contradiction.