Denormalization — when and why: Foundations — Coding deep dive

Before you can reason about when to denormalize, you must own every word and symbol the parent note throws at you. This page builds each one from nothing — plain words first, then a picture, then why the topic needs it. Read top to bottom; each block leans on the one above it.

1. A table (the atom of everything)

Look at the figure. The whole grid is the table named order_items. The highlighted horizontal strip is one row — everything the database knows about one line of one order. The vertical strip is one column — the same property across every row.

Why the topic needs this: denormalization is entirely about where facts live — in which table, in which column. You cannot talk about "duplicating a column" until you can point at a column.

2. Redundancy — the same fact twice

In the figure, the customer's name "Asha" appears in two rows (two different orders). That single fact — this customer is named Asha — is written twice. The red cells are the redundant copies.

Why this matters both ways:

Danger: if Asha changes her name, you must fix both red cells. Miss one and the database now contradicts itself.
Speed: but if you want to show an order with the customer name, the name is already sitting right there — no need to go looking in another table.

That tension is the whole topic. Normalization removes redundancy; denormalization puts controlled redundancy back.

3. Normalization — one fact, one place

The figure shows the same information as before, but split: a customers table holds each customer's name once, and an orders table just points at it. The name "Asha" now appears a single time (red cell). Change it there and every order automatically sees the new value — no contradiction possible.

Why the topic needs this: denormalization is defined as "partially undoing normalization." You must know the tidy, redundancy-free starting point before you can talk about deliberately messing it up. See Normalization (1NF 2NF 3NF BCNF) for the formal levels.

4. Key and foreign key — the pointer between tables

Why the topic needs this: when tables are normalized, the foreign key is the only link back to the parent's data. To read the parent's name you must follow the pointer — that following operation is the join, defined next.

5. JOIN — re-assembling split data

Follow the red arrows in the figure. Each orders row carries customer_id. The join takes that id, walks over to the customers table, finds the matching row, and staples the name onto the order. The result on the right is the re-assembled view.

Why the topic needs this: normalization made reads require this stapling work every single time. Denormalization's entire purpose is to make the join disappear by pre-storing the name inside orders. So "the cost of a join" is the thing we are trying to save.

6. Big-O notation — "how the cost grows"

The parent writes things like $O (R \cdot S)$ and $O (1)$ . Here is what that letter means.

Read the common ones like sentences:

$O (1)$ — "constant." The work is the same no matter how big the data. (Updating one number.)
$O (N)$ — "linear." Double the data, double the work. (Counting $N$ comments.)
$O (R \cdot S)$ — work grows with the product of two sizes. (Joining two tables with no shortcut.)
$O (R lo g S)$ — much gentler than $O (R \cdot S)$ ; the $lo g$ appears when an index lets you jump to a match instead of scanning.

Why the topic needs this: the entire read-vs-write argument is "reads that were $O (N)$ or $O (R \cdot S)$ become $O (1)$ after denormalizing." Without $O$ , you cannot state that saving.

A row is one thing (one order); a column is one property (price) shared across all rows.

What does redundancy mean, and why is it dangerous?

The same fact stored in two places; dangerous because the copies can disagree (update anomaly).

What is normalization in one line?

Splitting data so every fact lives in exactly one place, removing redundancy.

What does a foreign key do?

It holds a parent row's id to point back at it — "this order belongs to customer 7."

What is a JOIN?

The operation that follows foreign keys to staple rows from separate tables into one combined row.

What does

O (1)

mean versus

O (N)

O (1)

= constant work regardless of size;

O (N)

= work grows in step with the data size.

What do

f_{r}

and

f_{w}

stand for?

Read frequency and write frequency — how often you read versus change the data.

What do

C_{j o in}

and

C_{sy n c}

measure?

Extra cost per read from joining, and extra cost per write from keeping duplicates in sync.

What does

\sum_{i} (p r i c e_{i} \times q t y_{i})

compute?

Price times quantity on each line item, summed across all lines — the order total.

What does a trigger do for us here?

Fires automatically on a change to update a duplicated value, keeping redundancy correct.

Connections

Parent: Denormalization — when and why
Normalization (1NF 2NF 3NF BCNF) — the tidy starting point these foundations describe
Update Insert Delete Anomalies — the danger redundancy re-introduces
Indexing — where the gentle $O (R lo g S)$ cost comes from
Database Triggers — the tripwire that guards copies
Materialized Views — cached-query denormalization
OLTP vs OLAP — read-heavy vs write-heavy worlds decide $f_{r} / f_{w}$
CAP Theorem — why distributed systems dodge cross-node joins

Foundations — Denormalization — when and why

1. A table (the atom of everything)

2. Redundancy — the same fact twice

3. Normalization — one fact, one place

4. Key and foreign key — the pointer between tables

5. JOIN — re-assembling split data

6. Big-O notation — "how the cost grows"

7. The symbols in the cost model

8. Anomaly, trigger, materialized view — the supporting cast

Prerequisite map

Equipment checklist

Connections