Normalization & Denormalization

You can keep a whole shop in one big table. This feels easy at first. Later it breaks in three ways.

Normalization fixes this. You split one wide table into small tables. Each fact is then stored once.

A fact is one piece of information, for example “Asha lives in IN”.

Denormalization is the reverse. You join small tables back into one wide table, on purpose.

One wide table

Here is the shop from the earlier chapters in one table. It is called shop_wide. One row is one item on one order.

The product_id column is the number of the product, as in the entities chapter. The product_name column is its name, to keep the rows easy to read.

The key is two columns together: order_id and product_id. Chapter 3 called this a composite key.

Look at the repeats. Asha is in three rows, so her country IN is stored three times. Pen costs 5.00 in two rows, and Lamp costs 60.00 in two rows.

Two things from the shop are missing. Omar (customer 5) has no order, and order 105 has no items. This table has no place for them.

What you will learn

1. See the three ways one wide table breaks.
2. Find what depends on what.
3. Split the table in three steps (the normal forms).
4. Check that the split loses nothing.
5. See why analytics joins tables back into one, and what that costs.

An anomaly is a problem that comes from the shape of a table. The code can be perfect. A wide table has three kinds.

Chapter 1 showed that a fact stored twice gives wrong totals. Here it breaks updates, inserts and deletes.

Update anomaly

Asha moves from India (IN) to Germany (DE). Her country is stored in three rows, so you must change three rows.

This update changes order 101 only. It misses order 102:

UPDATE shop_wide
SET country = 'DE'
WHERE order_id = 101;

SELECT order_id, product_name, name, country
FROM shop_wide
WHERE customer_id = 1
ORDER BY order_id, product_id;

The two rows of order 101 say DE. The row of order 102 still says IN. Real tables have millions of rows, so a missed row is easy.

Insert anomaly

Omar is a customer, but he has no order yet. Try to add him:

INSERT INTO shop_wide (customer_id, name, country)
VALUES (5, 'Omar', 'IN');

Omar cannot exist until he buys something. A new product that nobody has ordered yet has the same problem.

Delete anomaly

Ben has one order: 103, with one Lamp. Now the shop removes order 103, and you count the rows that are left for Ben:

DELETE FROM shop_wide
WHERE order_id = 103;

SELECT COUNT(*) AS rows_for_ben
FROM shop_wide
WHERE customer_id = 2;

The name and country of Ben were stored only in that row. Removing one order also removed a customer.

All three anomalies have one cause. One row holds facts about four things: the item, the order, the customer and the product. The next section names how these facts link.

A dependency means that one value tells you another value. If you know the order, you know its customer. We write this with an arrow: order_id → customer_id.

Each arrow comes from how the business works. Here are the arrows of shop_wide:

You can test an arrow on the data. Count the different prices for each product_id in shop_wide:

SELECT product_id, COUNT(DISTINCT price) AS prices
FROM shop_wide
GROUP BY product_id
ORDER BY product_id;

Why the table breaks

Compare the arrows with the key of shop_wide. The key is order_id and product_id. But country depends on customer_id, and price depends on product_id alone.

A fact is stored in a row about something else. That is why the table repeats it, and why it breaks.

The next three sections are the three normal forms. 1NF makes the rows right. 2NF and 3NF split shop_wide.

First normal form (1NF) has one rule. Every cell holds one value. No lists in a cell, and no repeated columns like item_1 and item_2.

Here is orders_raw. Each order sits in one row, and the items are a list inside one cell:

This breaks 1NF. Three things go wrong:

• You cannot count the Pens with a normal GROUP BY.
• The database cannot check that Notebook is a real product.
• You cannot join the cell to a products table.

The fix is one row for each item. Every row has the same columns. The key is order_id and product_id together.

Order 101 is now two rows, one for each product. Now add the customer, order and product columns next to them. That gives shop_wide.

shop_wide is in 1NF, but it still has the three anomalies from section 2.

Second normal form (2NF) matters when the key has two columns or more. It says that every other column must depend on the whole key. A column that depends on only one part of the key must move out.

The key of shop_wide is order_id and product_id. Test each column: do you need both parts to know it?

Split it into three tables. Facts about the order go to orders_step2, and facts about the product go to products. The item stays in order_items.

Each price is now stored once, in products. Status is stored once for each order.

Order 105 is not here yet, because shop_wide had no row for it. You can add it now.

One repeat is left. The name and country of Asha appear in orders 101 and 102. The next form removes it.

Third normal form (3NF) says that every column depends on the key and on nothing else. A column must not depend on another column that is not the key.

Look at orders_step2. The key is order_id. But name and country are set by customer_id.

They depend on order_id only through customer_id.

The chain is order_id → customer_id → country. A fact that depends on the key only through another column is a transitive dependency. Move it out:

Two rows are new: Omar, and order 105. The wide table had no place for them. order_items and products stay as they were.

Now each fact is stored once. Section 7 checks the split. Section 8 runs the three anomalies again.

A good split loses nothing. If you join the four tables, you get the facts of shop_wide back. This is called a lossless split.

Join the tables and count the rows. The wide table had 7 rows:

SELECT COUNT(*) AS joined_rows
FROM order_items AS oi
JOIN orders      AS o ON o.order_id    = oi.order_id
JOIN customers   AS c ON c.customer_id = o.customer_id
JOIN products    AS p ON p.product_id = oi.product_id;

This check is not a proof, but it catches lost rows.

Keep the links

Each table must keep a link to the table it belongs to. Suppose orders had no customer_id column. Then nothing would say whose order 101 is.

Here are the four final tables. The diagram shows each table’s PK (primary key) and FK (foreign key).

The four tables in SQL

Look at the FK columns: each one points at a PK.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        TEXT NOT NULL,
  country     TEXT
);
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name       TEXT NOT NULL,
  price      NUMERIC(10,2) NOT NULL
);
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers (customer_id),
  status      TEXT NOT NULL
);
CREATE TABLE order_items (
  order_id INT  NOT NULL REFERENCES orders (order_id),
  product_id INT NOT NULL REFERENCES products (product_id),
  quantity   INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

The database now refuses a row that points at nothing. Try to sell a product_id that is not in products:

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (101, 9, 1);

The three anomalies are gone

Now run the same update and the delete on four tables. Omar is already in customers. Then you read customers 1, 2 and 5:

UPDATE customers
SET country = 'DE'
WHERE customer_id = 1;

DELETE FROM order_items WHERE order_id = 103;
DELETE FROM orders      WHERE order_id = 103;

SELECT customer_id, name, country
FROM customers
WHERE customer_id IN (1, 2, 5)
ORDER BY customer_id;

We delete from order_items first, because it points at orders. From now on, Asha lives in DE and order 103 is gone.

Normal forms protect writes. Analytics mostly reads. An analyst asks a question like revenue by country, and the answer needs all four tables.

So analytics often joins the tables once, and saves the result as a wide table. This is denormalization. Here is the report table for our shop:

CREATE TABLE shop_report AS
SELECT o.order_id, c.name, c.country, o.status,
       p.name AS product_name, oi.quantity, p.price,
       oi.quantity * p.price AS line_total
FROM order_items AS oi
JOIN orders      AS o ON o.order_id    = oi.order_id
JOIN customers   AS c ON c.customer_id = o.customer_id
JOIN products    AS p ON p.product_id = oi.product_id;

SELECT * FROM shop_report
ORDER BY order_id, product_name;

Now every question is one table, with no joins. Revenue by country for paid orders:

SELECT country, SUM(line_total) AS revenue
FROM shop_report
WHERE status = 'paid'
GROUP BY country
ORDER BY revenue DESC;

The cost

The report repeats the customer and the price in many rows. Those copies can go out of date.

Dev adds his country, SG. You update customers, then look at shop_report:

UPDATE customers
SET country = 'SG'
WHERE customer_id = 4;

SELECT order_id, product_name, name, country
FROM shop_report
WHERE name = 'Dev'
ORDER BY product_name;

This is the update anomaly again. The source of truth is the table you trust when two copies disagree.

So the four tables stay the source of truth, and the report is only a copy. A pipeline rebuilds it on a schedule.

You do not pick one for the whole company. Normalize the tables where people write. Denormalize copies where people read.

Copy a column only if

• A real read needs it, and you can name that read.
• Someone owns the rebuild.
• The table shows how old it is, for example with a refreshed_at column.
• The normalized tables stay the source of truth.

Other forms exist: BCNF, 4NF and 5NF. They fix rarer cases. Many real designs stop at 3NF.

Interview: in one breath

Interviewers often ask for 1NF, 2NF and 3NF, and for when to denormalize. Give one sentence and one example for each.

Each exercise opens an ERD canvas. You draw the tables, and the review checks them against seeded rows.

Start at the top. Two exercises are harder and are marked optional.