Normalization and denormalization
Storing each fact once, and when to break that rule on purpose. Builds on Relational databases.
5 minute lesson
One fact and one place
Normalization is the rule from the first lesson, applied all the way: every fact is stored once, and everything else refers to it by key. Writes are simple and cannot contradict each other. The price is paid on reading, in joins.
- orders1 row
- customersjoin
- order linesjoin
- productsjoin
- normalization
- Arranging tables so that each fact is stored exactly once.
- denormalization
- Storing a fact in more than one place on purpose, to make a frequent read cheaper.
Key terms
What does a normalized design make simple, and what does it make more work?
- Writes are simple, and reads need joins
- Reads are simple, and writes need joins
- Both are simple, as each fact is copied
- Both are harder, as there are more tables
Show the answer
- Writes are simple, and reads need joins
A fact that exists once is changed in one place and cannot disagree with itself, so writes are simple and safe. A screen that shows facts from several tables has to bring them together again, and that is the work of a join on every read.
Normalization makes writes simple and pushes the work to the reads.
A counter that is read a million times
- Normalized
A forum shows the number of replies next to every thread. Normalized, that number is not stored anywhere. It is counted from the replies table each time a list is shown.
- List of 50 threads50 counts
- replies tableread each time
- Denormalized
With denormalization the thread row gets a column reply_count. The list reads it directly. The count now exists twice: as the rows themselves and as a number.
- threadsreply_count 128
- List viewreads one column
- The price
Every new reply must also add one to the counter, in the same transaction. Code that forgets it leaves a number that is wrong and stays wrong.
- New replyinsert the row
- And alsoadd 1 to the count
The same trade in both directions
Normalized
each fact once
- Writing
- One place and always right
- Reading
- Joins and counting
Denormalized
copies on purpose
- Writing
- Every copy must follow
- Reading
- One row and no work
A team considers storing the number of followers in each user row, in addition to the table of who follows whom.
When is that a good decision?
- When counting them is measured to be a slow, frequent read
- When the team expects that it might be slow some day
- When the follower table has been deleted to save space
- When the count is shown rarely and has to be exact
Show the answer
- When counting them is measured to be a slow, frequent read
A stored count costs something on every follow and unfollow, and it adds a way for the data to be wrong. That is worth paying when the read it replaces is frequent and proven slow. Before that, the count can be computed, and the design stays simple.
Denormalize a read that is frequent and measured slow, not one that might be.
What does denormalization trade, and what must be true before you do it?
Show the answer
It stores a fact a second time, such as a count or a copied name, so that a frequent read needs no join or counting. In exchange every write to the original must also update the copy, or the two disagree. Do it for a read that is measured to be slow, and be clear about how the copy is kept right, ideally in the same transaction.
Recap
- Normalized data stores each fact once: simple, safe writes and reads that need joins.
- Denormalized data keeps copies or totals: cheap reads and writes that must update every copy.
- Start normalized, and denormalize a measured slow read on purpose.