Normalization: Where to Split and Where to Stop

Most database advice says "normalize", and most databases that cause trouble are the ones where someone did it halfway, or did it so far that every report needs eight joins. Normalization is not a goal. It is a way to make sure each fact is stored in one place, so the database cannot disagree with itself. This lesson shows the problem first, then the split, then the case where one extra copy is the correct design.

The three rules people quote can be said in plain words. Each cell holds one value (first normal form). Each column describes the whole key, not just part of it (second). Each column describes the key and nothing else (third). The usual summary is "the key, the whole key and nothing but the key". You do not need the names to use the rules. You need to ask, for every column, "what does this describe?"

A spreadsheet that became a table

Here is what a sales sheet looks like when it is pasted into a table.

CREATE TABLE sales_sheet (
  sale_id        integer PRIMARY KEY,
  customer_email text NOT NULL,
  customer_name  text NOT NULL,
  customer_city  text,
  sale_date      date NOT NULL,
  products       text NOT NULL
);
INSERT INTO sales_sheet VALUES
  (1, 'ana@example.com', 'Ana Reyes', 'Manila', '2026-08-01', 'Notebook A5, Gel Pen'),
  (2, 'ana@example.com', 'Ana Reyes', 'Manila', '2026-08-15', 'Desk Lamp'),
  (3, 'ben@example.com', 'Ben Cruz',  'Cebu',   '2026-08-03', 'Backpack, Gel Pen'),
  (4, 'ana@example.com', 'Ana Reyes', 'Manila', '2026-09-20', 'Desk Lamp, Mug'),
  (5, 'ben@example.com', 'Ben Cruz',  'Cebu',   '2026-09-14', 'Backpack');

CREATE TABLE
INSERT 0 5

It holds the data. Three things are wrong with it.

A list in one cell. "How many of each product did we sell?" needs the list split apart first.

SELECT p AS product, count(*) AS sales
FROM sales_sheet, unnest(string_to_array(products, ', ')) AS p
GROUP BY p
ORDER BY p;

   product   | sales
-------------+-------
 Backpack    |     2
 Desk Lamp   |     2
 Gel Pen     |     2
 Mug         |     1
 Notebook A5 |     1
(5 rows)

That works until someone types Gel pen or Gel Pens. A list in a cell cannot have a foreign key, so nothing checks the names.

A fact stored many times. Ana's city is on every one of her sales. Change it in one place, in a transaction we will undo, and see what the table now says.

BEGIN;
UPDATE sales_sheet SET customer_city = 'Quezon City' WHERE sale_id = 4;
SELECT customer_email, count(DISTINCT customer_city) AS cities
FROM sales_sheet GROUP BY customer_email ORDER BY customer_email;
ROLLBACK;

BEGIN
UPDATE 1
 customer_email  | cities
-----------------+--------
 ana@example.com |      2
 ben@example.com |      1
(2 rows)

ROLLBACK

Ana now lives in two cities. The database cannot tell which one is true.

A fact that cannot exist alone. A customer who has not bought anything has nowhere to live in this table.

INSERT INTO sales_sheet (customer_email, customer_name) VALUES ('cy@example.com', 'Cy Lao');

ERROR:  null value in column "sale_id" of relation "sales_sheet" violates not-null constraint
DETAIL:  Failing row contains (null, cy@example.com, Cy Lao, null, null, null).

Deleting Ben's last sale would also delete Ben. These three problems are called the update, insert and delete anomalies, and they all come from putting two different things in one table.

Split by what each column describes

The city, name and email describe the customer. The date describes the sale. A product in a sale is its own row.

CREATE TABLE sheet_customers (
  email text PRIMARY KEY,
  name  text NOT NULL,
  city  text
);
CREATE TABLE sheet_sales (
  sale_id        integer PRIMARY KEY,
  customer_email text NOT NULL REFERENCES sheet_customers (email),
  sale_date      date NOT NULL
);
CREATE TABLE sheet_sale_items (
  sale_id integer NOT NULL REFERENCES sheet_sales (sale_id),
  product text NOT NULL,
  PRIMARY KEY (sale_id, product)
);
INSERT INTO sheet_customers SELECT DISTINCT customer_email, customer_name, customer_city FROM sales_sheet;
INSERT INTO sheet_sales SELECT sale_id, customer_email, sale_date FROM sales_sheet;
INSERT INTO sheet_sale_items SELECT sale_id, unnest(string_to_array(products, ', ')) FROM sales_sheet;

CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 5
INSERT 0 8

Check that nothing was lost. The old sheet had eight product mentions in five sales.

SELECT (SELECT count(*) FROM sheet_customers) AS customers,
       (SELECT count(*) FROM sheet_sales)     AS sales,
       (SELECT count(*) FROM sheet_sale_items) AS items;

 customers | sales | items
-----------+-------+-------
         2 |     5 |     8
(1 row)

Two customers, five sales, eight items. Now Ana's city lives in one row, and changing it is one UPDATE that cannot disagree with itself. The foreign keys stop an item from pointing at a sale that is not there.

Where to stop: the price that must not change

Splitting further is not always better. Look at the shop. The price lives on products, and each order line points at it. That is normalized. It is also a bug waiting for the first price change. Watch the total of order 2, one desk lamp, when the price goes up.

BEGIN;
UPDATE products SET price = 20.00 WHERE name = 'Desk Lamp';
SELECT i.order_id, sum(i.quantity * p.price) AS total
FROM order_items AS i JOIN products AS p ON p.product_id = i.product_id
WHERE i.order_id = 2 GROUP BY i.order_id;
ROLLBACK;

BEGIN
UPDATE 1
 order_id | total
----------+-------
        2 | 20.00
(1 row)

ROLLBACK

An order placed in August now shows the October price: 20.00 instead of the 18.00 the customer paid. The price on a past order is a fact about that order, not about the product today. So store a copy of it on the order line, on purpose.

ALTER TABLE order_items ADD COLUMN unit_price numeric(8,2);
UPDATE order_items AS i SET unit_price = p.price FROM products AS p WHERE p.product_id = i.product_id;
ALTER TABLE order_items ALTER COLUMN unit_price SET NOT NULL;
UPDATE products SET price = 20.00 WHERE name = 'Desk Lamp';
SELECT i.order_id,
       sum(i.quantity * i.unit_price) AS from_snapshot,
       sum(i.quantity * p.price)      AS from_products
FROM order_items AS i JOIN products AS p ON p.product_id = i.product_id
WHERE i.order_id = 2 GROUP BY i.order_id;

ALTER TABLE
UPDATE 20
ALTER TABLE
UPDATE 1
 order_id | from_snapshot | from_products
----------+---------------+---------------
        2 |         18.00 |         20.00
(1 row)

The snapshot stays at 18.00 and the live price moves. This copy is denormalization, and it is correct. The test for any copy is "does it have to change when the original changes?" If yes, it is a duplicate and a bug. If no, it is history.

Other places to stop: a city column does not need its own table until you need to store something about a city. A status can be a text column with a CHECK rule until statuses have their own attributes, such as a label or a sort order. A summary table that is rebuilt from the real data is fine for speed. What you must not do is keep two sources of the same live fact and update both by hand.

Common mistakes

• Storing a list in one cell instead of a row per item.
• Repeating the customer's details on every order.
• Joining to the live product price to reprint an old invoice.
• Splitting every column into its own table because "that is normalization".
• Keeping a copy and forgetting to say whether it is history or a duplicate.

If you skip this, your database will hold two answers to the same question one day, or it will change the past without telling anyone. Which numbers on your old invoices would change if you updated a price right now?