First exams 2027 · SL & HL

A3.2.7 · Database design

Evaluating the need for denormalizing databases

A live banking system and a weather-analytics dashboard both store data, but the right amount of normalization for one is close to wrong for the other, this statement is about weighing that decision, not just knowing the definitions.

Core for SL & HL Command term: evaluate Builds on A3.2.5 & A3.2.6

Explainer

A3.2.5 and A3.2.6 both pushed in one direction: normalize, remove redundancy, split tables apart. This statement asks the opposite question on purpose: when is a fully normalized design actually the WRONG choice? "Evaluate" means weighing both sides and reaching a judgement, not just listing pros and cons.

Df

Denormalization is doing A3.2.5 and A3.2.6 in reverse, on purpose. The redundancy those statements taught you to remove gets deliberately reintroduced, not because someone made a mistake, but because a specific query pattern will run faster with the duplicate right there than it would having to fetch it fresh from another table every time.

The trade-off, both sides

Normalization

Real example: a banking system processing live transfers, every balance must always be exactly correct, right now, for every user checking it.

Advantages

  • Minimizes data redundancy (each fact stored once, less storage, no risk of two copies disagreeing)
  • Ensures data integrity (insert, update and delete rules stay consistent and enforceable)
  • Supports scalability (new data added without restructuring what already exists)

Disadvantages

  • More complex schema (many small tables and relationships are harder to understand and maintain)
  • Slower reads (queries need joins across several tables, which costs performance)
Denormalization

Real example: a weather-analytics dashboard generating historical reports, the data was recorded once and never changes again, it only ever gets read.

Advantages

  • Improved query performance (a value already sitting in the row you need beats fetching it through a join, every time)
  • Simplified data retrieval (the table's shape ends up matching what the application actually asks for, instead of forcing it to reassemble the answer from several places)
  • Enhanced scalability for reads (more read traffic doesn't mean more relationships to chase, because there are fewer of them to begin with)

Disadvantages

  • Data redundancy (the same fact stored more than once, at real storage cost)
  • Update risk (if an update misses one of the duplicate copies, the data quietly disagrees with itself)

Where denormalization specifically pays off

Read-intensive situations

  • Read-intensive applications: when a system spends nearly all its time being queried and almost none being written to, every join it can skip is time saved on every single request.
  • Reporting and analytics: a report that has to sweep across a large dataset already does a lot of work, dragging several tables together for every row of it just adds more.
  • Data warehousing: once data has been pulled in from several source systems for analysis, keeping it pre-combined means a cross-source question doesn't have to redo that combining on every query.

Finding the actual balance

Denormalization trades a straightforward, fast-to-write query structure against the real risk of redundant data drifting out of sync. Reaching a genuine evaluation, not just a guess, means working through this in order:

1

Work out what the system spends most of its time doing: answering read requests, or absorbing a steady stream of updates? Everything else in this process depends on getting this one honestly.

2

Put the speed gain and the inconsistency risk on the same scale: does one genuinely outweigh the other for this specific system? The honest answer is rarely all-or-nothing, denormalizing just the handful of fields that actually get hammered by reads, while leaving the rest properly normalized, is often exactly right.

3

Decide, before going ahead, how the risk from step 2 actually gets managed: what checks catch a value going out of sync, who is watching whether the performance win is holding up over time, and what the actual procedure is for updating every duplicate copy together instead of just one.

Worked example

Same three questions, two systems, two different verdicts

Both databases store real, useful data. Walking through the same balancing questions gives each one a genuinely different answer, that's what "evaluate" actually looks like in practice.

Weather-analytics dashboard

Historical weather readings from hundreds of stations, joined against station and region details, to generate charts and reports. Data is recorded once and essentially never edited again.

Read or update focus?Almost entirely read, reports run constantly, updates are rare.
Benefit vs redundancy risk?High benefit: flattening station and region details into each reading avoids repeated joins on every single report. Low risk: since readings are never edited, duplicated station/region data can't drift out of sync.
Mitigation needed?Minimal, since the data is effectively read-only once recorded.
Denormalize

The performance win is large and the redundancy risk is genuinely low here, a clear case for a flattened, read-optimized design.

Live ticket-booking system

A theatre's seat-booking system, where every seat's availability must update instantly and correctly the moment it's booked or released, for every user viewing the seating map at once.

Read or update focus?Heavily update-driven, seat status changes constantly and must always be current.
Benefit vs redundancy risk?Low benefit: seat lookups are already fast on a normalized schema. High risk: duplicating seat-availability data risks two customers being shown the same seat as free, a direct double-booking.
Mitigation needed?Would be substantial if denormalized, real-time synchronization across every redundant copy, on every single booking.
Stay normalized

The redundancy risk (double-booked seats) outweighs a performance gain the system doesn't actually need here.

Check your understanding

Which system is the stronger candidate for denormalization?

Common mistake

Markers reported this pattern for A3.2.7

RecurringThe command term "evaluate" means weighing both sides and reaching a justified judgement. Candidates often answer with only one side, either listing denormalization's benefits with no mention of the redundancy risk, or the reverse, when the actual mark scheme rewards a genuine balance, tied to the specific scenario given.

✕

What a weak answer looks like: "Denormalizing the reporting database would make queries faster." True, but one-sided, no mention of the redundancy risk being weighed against that benefit, and no judgement reached.

✓

What it should say: "Denormalizing would make the reporting queries faster by removing joins, and since this data is read-only once recorded, the usual redundancy risk barely applies here, so the performance benefit clearly outweighs the cost in this specific case." States both sides AND reaches a justified verdict.

Revision questions

Past-paper style. Write an answer, then reveal the marking points and mark yourself.

Question 1 · 2 marks

[2]The weather-analytics dashboard stores each station’s name alongside every reading. Identify two problems this repetition could cause.

Question 2 · 3 marks

[3]Explain why the live banking system is kept fully normalized, referring to two specific characteristics of a normalized design.

Question 3 · 6 marks

[6]A national census database stores historical population records that are published once and never edited afterward, but are queried constantly for public statistics. Evaluate the need for denormalizing this database.

Flashcards

Active recall for this statement's key terms.