Chapter 21 — Further Reading

Pattern catalogs (everyone)

  • Joe Celko, SQL for Smarties and Trees and Hierarchies in SQL for Smarties. The deep reference for hierarchy models (adjacency list, nested set, materialized path, closure) and many other patterns. Opinionated and thorough.
  • "Common database design patterns" articles — audit, soft delete, versioning, tagging, polymorphism. Read a few; the patterns recur everywhere.
  • Martin Fowler, Patterns of Enterprise Application Architecture — many patterns here (and inheritance from Chapter 18) framed from the application side.

Hierarchies (🔬 CS Student · 🏗️ DBA)

  • "Models for hierarchical data" (the classic comparison talk/posts) — the four models side by side, with the read/write trade-offs of Case Study 1.
  • Closure table tutorials — how to build and maintain ancestor–descendant tables.
  • PostgreSQL ltree extension — a built-in materialized-path type for hierarchies; a practical alternative worth knowing.

Soft delete, audit, temporal (💻 Developer · 🏗️ DBA)

  • "Soft delete: pros, cons, and pitfalls" — reinforces the filtering-view and partial-unique-index advice.
  • Audit-logging with triggers — canonical approaches, including the generic jsonb before/after table.
  • Temporal tables / "system-versioned tables" — the SQL-standard approach to history; PostgreSQL patterns and extensions for it.

Polymorphism & multi-tenancy (💻 Developer · 🏗️ DBA)

  • "Polymorphic associations in SQL" — the three approaches and the integrity trade-off (Case Study 2). Especially read critiques of the ORM type+id default.
  • "Multi-tenant data architecture" (cloud vendor whitepapers) — shared-schema vs. schema-per-tenant vs. db-per-tenant, with row-level security (Chapter 32).

Reference (this book)

  • Chapter 11 — Recursive CTEs: querying the adjacency-list hierarchy.
  • Chapter 15 — Triggers/Views: the machinery behind audit trails and soft-delete views.
  • Chapter 32 — Security: row-level security for multi-tenancy.
  • Appendix I — SQL Cookbook: pattern recipes (audit trigger, closure table, tagging).

Do, don't just read

  • Build a closure table for Mercado's categories and compare subtree-read speed to the recursive CTE (Case Study 1).
  • Add soft delete + a filtering view to a Mercado table; try to make a deleted row leak, then confirm the view prevents it.
  • Model "comments on products or posts" both ways (type+id vs. shared parent); try to create an orphan in each and see which the database stops.

Next: Chapter 22 — Schema Migration and Evolution: changing your database safely (the close of Part III).