Normalization: tidying away repeats

When the same info gets written again and again

Earlier we learned to read two tables
linked by a shared column.
This time we peer into a single table.
Every time an order comes in
we add one row to the same table,
and we write the customer name and phone too.
If the same customer orders many times,
that name and number repeat identically per row.
The cells fill up with the same text.

Tap a customer name. The same customer's info, repeated identically across rows, is highlighted at a glance.

The same info is stamped on every row.
First, it wastes space,
since the same name is written over and over.
The bigger problem is elsewhere.
If the customer changes their phone
you must find and fix every row with that customer.
Miss even one
and some rows show the new number, some the old.
Same customer, yet the info disagrees.

Split off the part that repeats

The fix is surprisingly simple.
Take the repeating part out
and make a separate table for it.
The customer name and phone
should not be written per order;
write each just once in a customer-only table.
Then the order table
loses the whole block of customer info
and becomes much lighter.

Tap split. The repeated customer info lifts out of the order table and gathers into a separate customer table.

The customer info is gathered in one place.
Now if a customer changes their number
you fix just one row in the customer table.
Whether there are a hundred orders or a thousand,
there is exactly one row to fix.
Nothing to miss, nothing to disagree.
But one worry appears.
Since we pulled customer info out of the order table,
how do we know which order belongs to whom?

Re-link them by a shared column

Here the linking we learned before comes alive.
When splitting, you cannot just tear them apart.
Leave one shared column in both tables.
The customer table keeps the customer number and name;
the order table keeps only the customer number, not the name.
This customer number is the thread that ties the two.
Look at the number on the order table,
find the matching number in the customer table,
and you instantly know whose order it is.

Tap a customer number on an order row. A thread links to the customer row with the matching number, revealing whose order it is.

One number tied the two tables together.
Even though the info is written in just one place,
following the thread lets you combine it anytime.
The repetition is gone
and the places to fix shrank to one,
yet all the needed info still comes together.
This is the heart of normalization.
But even a good thing, overdone, causes trouble.
What happens if you split tables too finely?

Split too far, and you may merge back

If you split finely just to hate repetition,
the tables keep multiplying.
Address in an address table, tier in a tier table,
a separate table for every little thing.
Then, to see one customer properly,
you must link three or four tables together.
The more linking there is,
the more time it takes to read.
Repetition dropped, but the speed slowed down.

Toggle between fine split and deliberate merge. Splitting adds more tables to link; merging brings repeats back but shows it in one read.

Splitting and merging trade off against each other.
Fine splitting cuts repetition and stays tidy,
but every read is slow from linking tables.
For info that is often viewed together
you may deliberately merge it into one table.
Then a little repetition comes back,
but you read it fast in one go, no linking needed.
Merging on purpose like this
is called denormalization. It is a matter of balance.

Let's wrap up

Gathered on one line, it is this.
Writing the same info repeatedly in one table
wastes space and breeds mismatch.
So we take the repeating part out,
split it into a separate table,
and re-link them by a shared column.
Then info lives in just one place
and we combine it by following the thread when needed.
But if it gets slow from splitting too fine,
we merge on purpose. That is the balance.

Tap the key points in order to review. (repetition wastes and mismatches -> split off the repeats -> link by a shared column -> merge on purpose if split too far)

Now you hold a way of thinking
for tidying tables cleanly.
Split off repeats and link by relation,
yet merge back when it goes too far, a balance.
This applies not only to tables
but broadly whenever you tidy anything.
The knack of cutting duplication to prevent mistakes,
and of regathering to read fast when needed.
Let's carry on together to the next story.