What normalization buys you
Normalization is usually the first topic newcomers meet in database work, and the motivation behind it is narrow: eliminate redundancy so that mistakes and inconsistencies have nowhere to hide. Store each fact once and there is only one version of it to be wrong.
A minimal product/category schema shows the pattern in PostgreSQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CREATE TABLE t_category ( id int PRIMARY KEY, cat_name text ); INSERT INTO t_category VALUES (1, 'Shoes'), (2, 'Skiing equipment'), (3, 'Plants'); CREATE TABLE t_product ( id int PRIMARY KEY, cat_id int REFERENCES t_category (id) ON UPDATE CASCADE ON DELETE CASCADE, product_name text, price numeric ); INSERT INTO t_product VALUES (1, 1, 'Blue boots', 154.98), (2, 1, 'Yellow flip flop', 32.79), (3, 2, 'Blizzard carver', 459.98), (4, 3, 'Oak', 17.95), (5, 3, 'Birch', 15.39), (6, 3, 'Elder flower', 9.75) ; |
Category names appear exactly once, in the category table. The product table holds only the foreign key. Renaming "Shoes" or "Plants" therefore costs one update instead of one per product, and a join reconstructs the full picture:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
test=# SELECT p.*, c.cat_name FROM t_category AS c, t_product AS p WHERE c.id = p.cat_id ORDER BY p.id; id | cat_id | product_name | price | cat_name ----+--------+------------------+--------+------------------ 1 | 1 | Blue boots | 154.98 | Shoes 2 | 1 | Yellow flip flop | 32.79 | Shoes 3 | 2 | Blizzard carver | 459.98 | Skiing equipment 4 | 3 | Oak | 17.95 | Plants 5 | 3 | Birch | 15.39 | Plants 6 | 3 | Elder flower | 9.75 | Plants (6 rows) |
The payoff becomes concrete when a category is edited:
|
1 2 3 4 5 6 7 8 9 |
test=# UPDATE t_category SET cat_name = 'Footware' WHERE id = 1 RETURNING *; id | cat_name ----+---------- 1 | Footware (1 row) UPDATE 1 |
One statement changed multiple rows without touching any of them. That is consistency and integrity, delivered by the schema rather than by discipline.
The cost of taking it too far
Normalization is no universal answer, and there are cases where strict adherence is effectively a bug. Consider an invoice table that lists sold products:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
test=# CREATE TABLE t_invoice ( id int PRIMARY KEY, tstamp date, units int, product_id int REFERENCES t_product (id) ); CREATE TABLE test=# \d t_invoice Table "public.t_invoice" Column | Type | Collation | Nullable | Default ------------+---------+-----------+----------+--------- id | integer | | not null | tstamp | date | | | units | integer | | | product_id | integer | | | Indexes: "t_invoice_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "t_invoice_product_id_fkey" FOREIGN KEY (product_id) REFERENCES t_product(id) |
Now suppose the same shoes were sold twice in the past, and a turnover report is requested:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
test=# SELECT * FROM t_product AS p, t_invoice AS v WHERE v.product_id = p.id; id | cat_id | product_name | price | id | tstamp | units | product_id ----+--------+------------------+-------+----+------------+-------+------------ 2 | 1 | Yellow flip flop | 32.79 | 1 | 2025-01-22 | 22 | 2 2 | 1 | Yellow flip flop | 32.79 | 2 | 2025-01-23 | 19 | 2 (2 rows) test=# SELECT sum(units * price) FROM t_product AS p, t_invoice AS v WHERE v.product_id = p.id; sum --------- 1344.39 (1 row) |
With product_id as a foreign key into the product table, the report returns 1344.39 Postgres bucks — a total that breaks as soon as prices change:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
test=# UPDATE t_product SET price = 984.34 WHERE id = 2 RETURNING *; id | cat_id | product_name | price ----+--------+------------------+-------- 2 | 1 | Yellow flip flop | 984.34 (1 row) UPDATE 1 test=# SELECT sum(units * price) FROM t_product AS p, t_invoice AS v WHERE v.product_id = p.id; sum ---------- 40357.94 (1 row) |
For a sales report, the current price of the product is the wrong number. What the report needs is the price as it stood at the moment of sale. Fully normalized, this schema destroys the historical record it was supposed to preserve.
Denormalizing deliberately
Historical data has to be captured redundantly — as the value at the time of the event. Product name and price belong in the sales (invoice) table itself:
|
1 2 3 4 5 6 7 8 9 |
test=# CREATE TABLE t_invoice ( id int4 PRIMARY KEY, tstamp date, units int, product_name text, product_price numeric ); CREATE TABLE |
Once stored that way, later changes to prices or names, or removal of a product entirely, cannot rewrite the past. These fields are a snapshot, not a reference.
The mistake is common in real applications, and worth flagging at design time — particularly when table definitions are produced by generative AI, which has no notion of which values must be frozen.
