A3.2.7 · Database design
Denormalising
By the end you can
Evaluate when it is worth breaking the normal forms on purpose to make queries faster.
Normalisation gives every fact one home, which is wonderful for accuracy. But every time a query needs facts from several homes, it has to join tables, and joins cost time when tables are huge and queried constantly. deliberately puts some repeated data back, to make the commonest queries cheaper. It is always done after normalising: you break a rule knowingly, for a reason you can measure.
The trade-off#
Gains: simpler queries; faster reads, because fewer tables are joined; data stored the way the page or report wants it.
Costs: the same fact stored many times (more storage); updates and inserts must touch every copy, so the code is harder to write; and if a copy is missed the data contradicts itself.
Normalised (3NF)
- Cells stored: 4,120
- A query for item and artist reads 2 tables and needs a JOIN
- Changing an artist's email edits 1 row
Denormalised (artist copied into every item)
- Cells stored: 6,000 (+1,880)
- The same query reads 1 table, no JOIN
- Changing an artist's email edits 25 rows, and missing one leaves a contradiction
With 200 reads for every update, the denormalised design saves 200,000 table reads per 1,000 updates (one fewer per read), but costs 24 extra row edits each time an artist changes. Reads dominate here, which is the situation where denormalising pays off (a reporting or product-listing page).
Deciding#
To judge whether to denormalise, weigh these:
- Reads or writes? Data that is read constantly and rarely changes (a product catalogue, a report) is a good candidate. Data that changes often (stock levels, balances) is not.
- Is it really the joins that are slow? Try an index, a better query or caching first. Measure before you change the design.
- How bad is an inconsistency? A misspelt author name on a web page is minor; a wrong account balance is not.
- Go partial. Often only a few columns need copying, with checks that keep them in step, while the rest of the database stays normalised.
A warehouse stock table updated by every sale, and a wrong count is costly.
A reporting table read by hundreds of dashboards each minute and rebuilt once a night.
A customer address that many other tables reference and that customers update often.
A product page that always shows the product, its brand name and its category name together.
0 of 4 matched
Check yourself
Denormalising
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
4 questions · 15 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.Describe what is meant by denormalisation and why it is done.
[3]Describe: A detailed account: say what happens, step by step if it is a process.
2.Outline three disadvantages of denormalising a database.
[3]Outline: A brief account: the main points, without much detail.
3.A news website shows each article with its author’s name, thousands of times a minute, while authors rarely change their names. Suggest whether to denormalise and give a reason.
[3]Suggest: Put forward a sensible answer for the situation given, with a reason. There may be several good answers.
4.Evaluate the need to denormalise the database of an online shop that is slow at showing product pages.
[6]Evaluate: Weigh the strengths against the weaknesses (or the benefits against the costs) and reach a judgement that you back up.
In a nutshell
- Denormalisation deliberately adds redundancy to a normalised database to make queries faster, because fewer joins are needed.
- Advantages: simpler queries and faster reads, especially for reporting and read-heavy pages.
- Disadvantages: more storage, slower and more complex updates, and a risk of inconsistent copies.
- It suits data that is read far more often than it changes. It is a trade-off to decide with measurements, not a starting point.
- Often a partly denormalised design is the compromise, along with indexes, caching and better queries.