Chapter 20 — Exercises
Design/judgment exercises against Mercado. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.
Group A — Why & when
20.1 Explain the difference between the accidental redundancy of Chapter 19's flat table and the deliberate redundancy of denormalization. (answer in Appendix)
20.2 Give three workload signals that suggest denormalization might help.
20.3 Mercado stores unit_price on order_items even though products have a price. Give the two reasons this denormalization is correct. (answer in Appendix)
20.4 ⭐ Why should you usually try an index (Chapter 23) before denormalizing for read speed?
Group B — Techniques
20.5 Propose a precomputed-aggregate denormalization for showing each product's review count and average rating on a listing page. What columns would you add? (answer in Appendix)
20.6 Describe a summary table for "daily revenue per category" and what job would populate it.
20.7 When would you use a materialized view instead of a hand-maintained derived column? Why is the matview often preferable? (answer in Appendix)
20.8 ⭐ Give an example where collapsing two tables into one (eliminating a join) is justified — and one where it definitely isn't.
Group C — Keeping it consistent
20.9 For the products.review_count denormalization, list three ways to keep it consistent and the trade-off of each. (answer in Appendix)
20.10 Write (in outline) a trigger approach to maintain review_count when reviews are inserted/deleted.
20.11 ⭐ Which consistency strategy fits each: a real-time "items in cart" counter; a "monthly sales" dashboard; a "total lifetime orders" badge that can be a few minutes stale? Justify.
Group D — When NOT to
20.12 Why is denormalization usually a poor choice in a write-heavy banking ledger? (answer in Appendix)
20.13 A junior wants to denormalize "to make it fast" with no measurements. What's your advice, and what would you check first?
20.14 ⭐ Describe a case where denormalization was added, drifted out of sync, and caused a wrong report. How would you have prevented it?
Group E — Progressive project
20.15 Identify one read-heavy query in your domain. Decide: index, materialized view, or maintained denormalized column? Justify.
20.16 If you denormalize, write the maintenance plan (how the redundant data stays consistent) and document it as a deliberate choice.
20.17 ⭐ Identify one place where you'd store a point-in-time value (like unit_price) and explain why that's a feature, not a bug.
Self-check. If your instinct is "normalize → measure → index/matview → denormalize-and-maintain only if needed," and you always pair denormalization with a consistency plan, you're designing like a pro. Next: reusable modeling patterns.