Normalization With a Real Example: From Spreadsheet to 3NF

Normalisation is usually taught as three definitions and a warning. This part teaches it as a repair. A clinic's visits spreadsheet, six rows with medicines and prices listed inside cells, is imported as a table, and the three things that go wrong with it are made to go wrong: a doctor ends up in two rooms, a new medicine cannot be recorded, and one deleted visit erases a doctor. Then the sheet is taken apart into first, second and third normal form, the data is migrated, and the same three operations are repeated with nothing breaking.

The sheet

Section 1 imports the spreadsheet as it is, one row per visit:

CREATE TABLE visits_sheet (
    row_no           integer      NOT NULL PRIMARY KEY,
    patient_name     varchar(200) NOT NULL,
    patient_phone    varchar(30)  NULL,
    doctor_name      varchar(200) NOT NULL,
    doctor_room      varchar(20)  NULL,
    visit_date       date         NOT NULL,
    medicines        text         NULL,
    medicine_prices  text         NULL,
    doses            text         NULL
);
INSERT INTO visits_sheet VALUES
 (1, 'Maria Garcia', '+00-20-5550-0101', 'Dr. Elena Rossi',  'Room 1', '2026-09-01',
     'Paracetamol 500mg, Amoxicillin 250mg', '2.50, 6.80', '1 tablet every 8 h, 1 capsule every 12 h'),
 (2, 'David Chen',   '+00-20-5550-0102', 'Dr. Elena Rossi',  'Room 1', '2026-09-01',
     'Ibuprofen 400mg', '3.10', '1 tablet every 8 h'),
 ...

Six rows, four more like these. Everything the clinic knows is in there, and every fact is written as many times as it is used: Dr. Rossi's room three times, Paracetamol's price three times, Maria Garcia's phone twice. That repetition is the disease, and the three anomalies are its symptoms.

Three ways it breaks

Section 2 is the update anomaly. Dr. Rossi moves to Room 4 and the clerk fixes the row on screen, row 6. Then someone asks which room she is in:

SELECT doctor_name, doctor_room, count(*) AS rows_saying_so
FROM visits_sheet WHERE doctor_name = 'Dr. Elena Rossi'
GROUP BY doctor_name, doctor_room ORDER BY doctor_room;

   doctor_name   | doctor_room | rows_saying_so
-----------------+-------------+----------------
 Dr. Elena Rossi | Room 1      |              2
 Dr. Elena Rossi | Room 4      |              1

Two answers, both stored, neither marked wrong. Section 3 is the insert anomaly: the pharmacy stocks Omeprazole and there is nowhere to write it, because a row is a visit and patient_name is NOT NULL. The insert fails with null value in column "patient_name" of relation "visits_sheet" violates not-null constraint. Section 4 is the delete anomaly. Tomás Silva's visit was entered by mistake, and deleting row 5 also deletes the only mention of Dr. Hannah Weber, her Room 3, and the price of Cetirizine; the count of rows mentioning it drops to zero. Each demo runs inside a transaction and is rolled back, so the repair starts from clean data. Both engines produce the same rows for all three.

First normal form: one value per cell

The medicines cell holds a list, and so do the prices and the doses, paired by position. 1NF says every cell holds one value, so section 5 unpacks the three lists into rows:

CREATE TABLE visits_1nf AS
SELECT s.row_no, s.patient_name, s.patient_phone, s.doctor_name, s.doctor_room, s.visit_date,
       l.medicine, l.price::numeric(8,2) AS medicine_price, l.dose
FROM visits_sheet AS s
CROSS JOIN LATERAL unnest(string_to_array(s.medicines, ', '),
                          string_to_array(s.medicine_prices, ', '),
                          string_to_array(s.doses, ', ')) AS l(medicine, price, dose);
-- Its key is (row_no, medicine): one row per medicine per visit
ALTER TABLE visits_1nf ADD CONSTRAINT pk_visits_1nf PRIMARY KEY (row_no, medicine);

PostgreSQL's unnest takes several arrays and zips them by position in one call. SQL Server does the same job with CROSS APPLY STRING_SPLIT(..., N',', 1), where the third argument adds an ordinal column and the three splits are joined on it, and SELECT ... INTO in place of CREATE TABLE AS. Eight rows come out of six, and the key is now the pair: this visit, this medicine. The pairing by position is the fragile part of the whole exercise; it works because nobody ever put the prices in a different order from the medicines, and nothing ever checked.

Second and third normal form: who depends on whom

2NF says every fact must depend on the whole key. Section 6 asks the table which facts do not:

SELECT medicine, count(*) AS rows_repeating_it, min(medicine_price) AS min_price, max(medicine_price) AS max_price
FROM visits_1nf GROUP BY medicine ORDER BY medicine;

     medicine      | rows_repeating_it | min_price | max_price
-------------------+-------------------+-----------+-----------
 Amoxicillin 250mg |                 2 |      6.80 |      6.80
 Cetirizine 10mg   |                 1 |      1.90 |      1.90
 Ibuprofen 400mg   |                 2 |      3.10 |      3.10
 Paracetamol 500mg |                 3 |      2.50 |      2.50

The price depends on the medicine alone, not on the visit, so it belongs in a medicines table. The same query on row_no shows the patient, doctor and date repeated on every medicine row of visits 1 and 6: they depend on the visit alone and belong in a visits table. 3NF says no fact may depend on another non-key fact, and section 7 finds two:

   doctor_name    | visits | distinct_rooms |  room
------------------+--------+----------------+--------
 Dr. Elena Rossi  |      3 |              1 | Room 1
 Dr. Hannah Weber |      1 |              1 | Room 3
 Dr. Kwame Mensah |      2 |              1 | Room 2

The room depends on the doctor, and the phone depends on the patient. Neither is a fact about a visit, so each moves to the table of the thing it describes. That is the whole method: for each column, ask what it is a fact about, and put it there.

The normalised tables, and the migration

Section 8 creates six tables; the two that carry the relationships:

CREATE TABLE visits (
    id          bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_visits PRIMARY KEY,
    patient_id  bigint NOT NULL CONSTRAINT fk_visits_patients REFERENCES patients (id),
    doctor_id   bigint NOT NULL CONSTRAINT fk_visits_doctors REFERENCES doctors (id),
    visit_date  date   NOT NULL,
    CONSTRAINT uq_visits_patient_doctor_date UNIQUE (patient_id, doctor_id, visit_date)
);
CREATE TABLE visit_medicines (
    visit_id     bigint NOT NULL CONSTRAINT fk_visit_medicines_visits REFERENCES visits (id),
    medicine_id  bigint NOT NULL CONSTRAINT fk_visit_medicines_medicines REFERENCES medicines (id),
    dose         varchar(100) NOT NULL,
    CONSTRAINT pk_visit_medicines PRIMARY KEY (visit_id, medicine_id)
);

rooms, doctors with a room_id, patients with a unique phone and medicines with a price complete the set. The prescription is Part 4's junction with a payload: the dose belongs to this visit and this medicine together. Section 9 migrates the sheet with INSERT ... SELECT, parents first, matching rows by the natural values the sheet has, phone for patients, name for doctors and medicines, and patient plus doctor plus date for visits, which is what the unique constraint on visits is for:

INSERT INTO visits (patient_id, doctor_id, visit_date)
SELECT p.id, d.id, s.visit_date
FROM visits_sheet AS s
JOIN patients AS p ON p.phone = s.patient_phone
JOIN doctors  AS d ON d.full_name = s.doctor_name
ORDER BY s.row_no;

   table_name    | row_count
-----------------+-----------
 rooms           |         3
 doctors         |         3
 patients        |         5
 medicines       |         4
 visits          |         6
 visit_medicines |         8

Section 10 proves nothing was lost by rebuilding the sheet from the six tables with a join and a string_agg: the same six visits, the same medicines per visit, and totals of 9.30, 3.10, 6.80, 2.50, 1.90 and 5.60. That query is what a report wants, and Part 12 makes it a view.

Three ways it no longer breaks

Section 11 repeats the anomalies. Dr. Rossi's move is one UPDATE doctors, and the question has one answer:

   doctor_name   | doctor_room | visits_saying_so
-----------------+-------------+------------------
 Dr. Elena Rossi | Room 4      |                3

Omeprazole is inserted into medicines with no visit at all, and the count goes to five. Tomás's visit and its prescription are deleted, and Dr. Hannah Weber, Room 3 and Cetirizine at 1.90 are all still there, because each of them was stored once, in the table that describes it, and the visit only pointed at them.

Frequently asked

What is database normalization in simple terms?

Storing each fact once, in the table of the thing it describes, so that changing it means changing one row. A doctor's room lives in the doctors table, a medicine's price in the medicines table, and a visit only points at them.

What are the update, insert and delete anomalies?

Update: a fact stored in several rows is changed in one, so the table now holds two versions. Insert: a fact cannot be recorded because the row it would live in needs other facts that do not exist yet. Delete: removing one row erases a fact nothing else stored.

How do I know which normal form a table is in?

1NF: every cell holds one value. 2NF: every non-key column depends on the whole key, not on part of it. 3NF: no non-key column depends on another non-key column. Two GROUP BY queries, one per rule, show the violations on real data.

Next: Part 8, naming conventions, where the same table created twice with different capitalisation gives two different answers.