4.4.22 · D5Databases
Question bank — Denormalization — when and why
First: the four symbols every trap here leans on
Before you touch the questions, meet the four quantities the decision rule is built from. Each is just a plain-language idea with a name.
True or false — justify
True or false: Denormalization always makes the database faster.
False — it speeds reads by removing joins, but slows writes and grows storage; on a write-heavy workload it is a net loss.
True or false: A schema that was never normalized is a denormalized schema.
False — denormalization is a deliberate optimization applied to an already-normalized design; an un-normalized schema is just a missing design step, not a choice.
True or false: If you denormalize, you no longer need to worry about update anomalies.
False — denormalization re-introduces update anomalies on purpose, so you must actively guard the copies (triggers, transactions, refresh).
True or false: Adding an index and denormalizing are two names for the same thing.
False — an index speeds reads without duplicating the fact, so it has no sync cost; denormalization duplicates the fact and must be kept in sync.
True or false: A materialized view is a form of denormalization.
True — a materialized view is a cached, pre-computed result of an expensive query, which is exactly "controlled redundancy for faster reads".
True or false: Once you store a derived column like order_total, it will stay correct because you computed it right the first time.
False — the source rows change over time; without a trigger or app-layer invariant the stored copy drifts and eventually contradicts the truth.
True or false: You should denormalize early in design "to be safe on performance".
False — normalize first, denormalize later with evidence; premature denormalization adds sync bugs before you even know which read is slow.
True or false: In an OLAP data warehouse, a heavily denormalized star schema is a design mistake.
False — for OLAP the workload is read-heavy analytics, so the read/write ratio is large and strongly favours denormalization; star schemas are denormalized by design.
True or false: The inequality can flip from "denormalize" to "don't" purely because write frequency rose.
True — raising shrinks the left side (the read/write ratio), so a formerly-good denormalization can become a net loss with no change to the query itself.
True or false: Storing tags as an inline array column instead of a junction table is denormalization.
True — it collapses a separate relationship table into a repeating/array column, trading normalized structure for fewer joins on read.
Spot the error
"Redundancy is always bad, so denormalization is bad." — what's wrong?
Redundancy is normally bad because facts can disagree, but denormalization tolerates it deliberately while guarding it, buying read speed — it's a controlled trade, not carelessness.
"We denormalized to remove joins, so we deleted the original normalized tables." — what's wrong?
The normalized tables usually remain the source of truth; the duplicated column is a fast copy derived from them, not a replacement — deleting the source destroys the ability to re-sync.
"Our writes got slower after denormalizing, so denormalization failed." — what's wrong?
Slower writes are expected and intended — the whole point is to move cost onto the write path; it "fails" only if the read savings don't outweigh that added write cost ().
"We added comment_count but didn't touch the delete path, only the insert path." — what's wrong?
A count must be decremented on delete too; guarding only inserts lets the counter drift high, an update anomaly caused by an incomplete guard.
"We put customer_name inside orders and stopped storing it in customers." — what's wrong?
You've turned a copy into the only copy, so it's no longer denormalization but a re-modelling — and now a customer's name lives per-order, making a rename touch every order row.
"The DB is slow, so let's denormalize everything." — what's wrong?
You must first measure to find the specific hot read; blanket denormalization multiplies sync logic and bugs on queries that were never the bottleneck.
Why questions
Why does denormalization move cost from the read path to the write path rather than removing cost entirely?
The fact still has to be assembled sometime; storing it pre-assembled just shifts the assembly work to write time (once per change) instead of read time (once per view).
Why is denormalization worth it precisely when you read far more than you write?
You pay the sync cost per write but save the join cost per read; when reads vastly outnumber writes, the total saved dwarfs the total added .
Why must every duplicated value be "guarded" rather than trusted?
The same fact now lives in two places, and any write that updates one but not the other creates a contradiction (update anomaly); a guard forces both to move together.
Why is a banking ledger a poor candidate for denormalizing the balance?
Writes are frequent and correctness must be exact and transactional, so is high and — the inequality fails, so you keep balance as the single source of truth.
Why can an index be a better first choice than denormalization for a slow read?
An index accelerates lookups with no duplicated fact and no sync burden, so it avoids the drift risk entirely — reach for denormalization only when even indexing can't remove the expensive join/aggregate.
Why does denormalization show up so often in distributed databases?
Cross-node joins are expensive and can violate availability, so per the CAP trade-offs systems often duplicate data locally to keep reads on a single node.
Why is a trigger a common mechanism for keeping a derived column correct?
A trigger fires automatically on every insert/update/delete of the source, so the copy is re-synced in the same transaction — no path can silently skip the update.
Edge cases
What happens to the denormalization decision when (reads equal writes)?
Substituting makes the left side , so the rule becomes , i.e. — denormalize only if a single join costs more than a single sync, otherwise stay normalized.
What if is essentially zero because a good index already makes the join trivial?
Then the right side blows up (large), the inequality almost never holds, so denormalization gives little benefit — index instead.
What if the derived value is never read (write-only log data)?
With the saved term vanishes while sync cost remains, so — denormalizing pure-write data is all cost, no benefit.
What is the risk of a materialized view refreshed only on a nightly schedule?
Reads between refreshes see stale data; that's acceptable for analytics but wrong for anything needing up-to-the-second accuracy, so match refresh cadence to the freshness requirement.
What happens if two concurrent writes both update a duplicated counter without a transaction?
A lost-update race can drop one increment, leaving the counter permanently wrong — the guard must be atomic (transactional or an atomic increment), not just present.
Is it denormalization if you compute the total on read but cache it in application memory?
Not schema denormalization — the database stays normalized; it's an application-layer cache, which shifts the same trade-off (speed vs staleness) outside the DB.
Connections
- Normalization (1NF 2NF 3NF BCNF) — the design these traps assume you started from
- Update Insert Delete Anomalies — the danger every unguarded duplicate re-invites
- Indexing — the "no-duplication" alternative several traps contrast against
- Materialized Views — cached-query denormalization, staleness edge cases
- Database Triggers — the guard mechanism behind "keep the copy correct"
- OLTP vs OLAP — why warehouses denormalize but ledgers don't
- CAP Theorem — why distributed systems duplicate to avoid cross-node joins
#flashcards/coding