Chapter 21 — Key Takeaways

The big idea

Data modeling patterns are reusable solutions to problems that recur in every domain. Knowing them (and their trade-offs) makes you fast and steers you away from the badly-reinvented versions (CSV tags, nested sets on active trees, type+id without integrity).

The patterns

  • Audit trail — a history table populated by a trigger (so no write path bypasses it); store before/after as jsonb for a generic, any-table audit.
  • Soft delete — deleted_at marks instead of removes (recoverable, preserves references). Every query must filter WHERE deleted_at IS NULL → pair with a filtering view. Use a partial unique index so deleted rows don't block re-use of unique values.
  • Versioning / temporal — *_versions with valid_from/valid_to when past states must be queryable (heavier than audit).
  • Multi-tenancy — shared schema + tenant_id (+ row-level security) → schema-per-tenant → database-per-tenant: increasing isolation, increasing operational cost.
  • Hierarchies (four models): | Model | Reads | Writes | Use when | |---|---|---|---| | Adjacency list (parent_id) | recursive CTE | cheap | default | | Materialized path | LIKE 'p/%' fast | moderate | read-heavy, bounded depth | | Nested set | very fast | expensive (renumber) | near-static trees | | Closure table | simple join, fast | moderate + storage | read-heavy, frequent moves (Case Study 1) |
  • Tagging — the canonical M:N junction (never CSV/array for queried labels).
  • Polymorphic association (relate to one-of-many types): per-type tables or a shared parent preserve FK integrity; type+id sacrifices it (no FK possible). (Case Study 2.)
  • Lookup tables, state machines, event sourcing — also worth knowing.

Key cautions

  • Soft deletes leak without a filtering view.
  • type+id polymorphism drops foreign-key integrity — choose it only knowingly.
  • Nested sets punish frequently-edited trees.
  • Don't over-pattern (event sourcing/temporal where updated_at + an audit table suffices).

The meta-skill

Recognize the pattern, choose deliberately among its variants by your read/write/integrity needs, and avoid the reinvented-badly versions.

You can now…

  • ☐ Implement audit trails (trigger + jsonb) and soft deletes (+ filtering view, partial unique index).
  • ☐ Model versioned/temporal data when needed.
  • ☐ Choose a multi-tenancy model.
  • ☐ Pick the right hierarchy model for your read/write mix.
  • ☐ Model tagging (M:N) and polymorphic associations with eyes open on integrity.

Looking ahead

Chapter 22 — Schema Migration & Evolution. Schemas change; this closes Part III with how to evolve them safely in production — migration tools, safe vs. dangerous changes, zero-downtime (expand-contract), and version control for your database.

One sentence to carry forward: Most design problems are already solved patterns — recognize them, pick the variant that fits your read/write/integrity needs, and never reinvent tagging as CSV or polymorphism as a foreign-key-less type+id.