What is database normalization

When the same information is written again and again

In the last lesson we linked two tables through a shared column to answer a question. This time, from the other direction, let's see why tables are split like that in the first place. Suppose that every time you take an order, you add a row to a single table and write the customer's name and phone number in it as well.
When the same customer orders several times, that customer's name and number are written again, identically, on every row. In the table below, Minsu appears three times, once for each of three orders.

Tap a customer name. You will see at a glance how many rows repeat that customer's information word for word.

The same information is stamped onto row after row. Wasted space is one problem, but the bigger trouble comes when something changes. If Minsu changes phone numbers, you have to find and fix every row with Minsu on it, and if you miss even one, the same person ends up with two numbers. From the table alone, there is no way to tell which one is right.
That is not the only trouble. A new customer who has not ordered yet has no row to go in, so you cannot even record their name; and deleting Haneul's only order wipes out Haneul's phone number along with it. These mismatches that appear when you update, insert, or delete are called anomalies.

Split off the part that repeats

The fix is simpler than you might expect: take the repeating part out and give it a table of its own. Instead of writing the customer's name and phone number on every order, you write them once, one row per person, in a customers table that holds only customers.
The orders table then loses the whole block of customer information and keeps only the columns that belong to the order itself, such as the order number. In effect, you work out which facts belong to what and put each one where it belongs.

Tap split. The repeated customer information comes away from the orders table and gathers in the customers table, one row per customer.

The customer information is now in one place. If Minsu changes numbers, you fix one row in the customers table and you are done. Whether there are a hundred orders or a thousand, there is only one row to fix, so nothing gets missed and no one ends up with two numbers. A new customer can be added to the customers table before ordering anything, and deleting an order leaves the customer's details intact.
But a new worry appears. With the customer information pulled out of the orders table, how do you know whose order is whose?

Link them again through a shared column

This is where the join from the last lesson comes in. When you split a table, you cannot simply tear the parts away; you leave one column that both tables share. The customers table keeps the customer number, name, and phone number, and the orders table keeps just the customer number in place of the name.
That customer number is the thread that ties the two tables together. Read the number on an order, look up the same number in the customers table, and you know at once whose order it is.

Tap the customer number on an order row. A thread runs to the row in the customers table with the same number and shows whose order it is.

A single number has linked the two tables. Even though each fact is written in only one place, you can follow the thread and put the pieces together whenever you like. The repetition is gone and the places to fix have shrunk to one, yet a join still brings together every piece of information you need.
This is the heart of normalization. In practice, the idea is set out as a series of rules called normal forms, starting with keeping only one value in each cell and refining the tables step by step. But even a good thing can be overdone. What happens if you split tables too finely?

Split too far, and you may merge on purpose

If you split everything finely just to avoid repetition, the number of tables keeps growing: addresses in an address table, membership tiers in a tier table, a separate table for every little thing.
Then, to see one customer properly, you have to join three or four tables together. Joins are not free, so the more tables there are to link, the longer the answer takes. The repetition has shrunk, but reading has slowed down.

Tap fine split and deliberate merge in turn. The finer the split, the more tables there are to link; merging brings back a little repetition but shows everything in one read.

Splitting and merging trade off against each other. A fine split leaves no repetition and stays tidy, but every read is slowed by linking tables. So information that is always read together is sometimes deliberately merged into one table; this is called denormalization.
It comes at a price. The same information now lives in several places again, so you need some way of keeping those copies in step whenever it changes. Where writes are frequent, you lean toward normalization; where reads far outnumber writes, you lean toward denormalization, and so you strike a balance.

Summary

To sum up: writing the same information over and over in one table wastes space, and the information falls out of step when you update, insert, or delete. So you take the repeating part out into a separate table and link the tables again through a shared column.
Each fact is kept in only one place and brought together with a join when needed. If splitting too finely makes things slow, you may merge on purpose, and striking that balance is part of normalization too.

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

Now you have a way to judge how to split tables and where to stop. Keeping each fact in one place means fewer chances for things to fall out of step when you make changes.
Sometimes, though, several tables must be changed together. Take recording an order while reducing the stock: if only one of the two goes through before things stop, the tables disagree. The next lesson, on transactions, covers how to bundle several changes into one unit so that either all of them happen or none of them do.