Chapter 21 — Exercises
Design exercises. Apply patterns to Mercado or your own domain. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.
Group A — Audit & soft delete
21.1 Design an audit-trail table + trigger outline that logs every change to products (action, who, before/after as jsonb). (answer in Appendix)
21.2 Add a soft-delete column to products and write the "delete," the "undelete," and the "list active" queries.
21.3 Why should soft deletes be paired with a filtering view? What bug does that prevent? (answer in Appendix)
21.4 ⭐ With soft deletes, a UNIQUE(email) blocks re-registering a previously-deleted user. Fix it with a partial unique index.
Group B — Hierarchies
21.5 Name the four hierarchy models and the read/write trade-off of each. (answer in Appendix)
21.6 Mercado's categories uses which model? How do you read a full subtree from it? (Chapter 11.)
21.7 ⭐ Design a closure table for Mercado's categories (ancestor, descendant, depth). Why does it make subtree reads fast, and what does it cost on writes?
21.8 For a frequently-reorganized org chart, which hierarchy model would you avoid, and why?
Group C — Tagging & polymorphic
21.9 Model "products have many tags" correctly. Why not an array or CSV column? (answer in Appendix)
21.10 A comment can belong to a product review thread OR a blog_post. Describe the three polymorphic approaches and the integrity trade-off of each. (answer in Appendix)
21.11 ⭐ Which polymorphic approach preserves foreign-key integrity, and which sacrifices it? When might the simpler one still be acceptable?
Group D — Multi-tenancy & versioning
21.12 Describe the three multi-tenancy models and when you'd choose each. (answer in Appendix)
21.13 Why is shared-schema + tenant_id risky without row-level security?
21.14 ⭐ Design a versioned document_versions table that lets you reconstruct a document's state at any past time.
Group E — Recognize the pattern
21.15 For each need, name the pattern: (a) recover accidentally-deleted records; (b) show price history; (c) a list of skills per user; (d) one DB serving many companies; (e) who edited this record and when. (answer in Appendix)
21.16 ⭐ A status column can be 'new'/'paid'/'shipped'. Compare modeling it as a CHECK list vs. a lookup table. When is each better?
Group F — Progressive project
21.17 Identify which patterns your domain needs (audit? soft delete? hierarchy? tagging? polymorphic? multi-tenant?) and apply at least two, documenting why.
21.18 ⭐ For any hierarchy in your domain, choose a model and justify it against your read/write balance.
Self-check. If you can name the right pattern for a problem and its trade-offs — and avoid the badly-reinvented versions (CSV tags, nested sets for active trees, type+id without knowing it drops FKs) — you have the designer's vocabulary. Next: evolving the schema safely.