Chapter 21 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. An audit trail is best populated by: - A) Application code only - B) A trigger (so no write path can bypass it) - C) Manual entry - D) A view

Q2. A soft delete: - A) Physically removes the row - B) Marks the row (e.g., deleted_at) instead of removing it - C) Drops the table - D) Truncates

Q3. The main risk of soft deletes is: - A) Slower writes - B) Forgetting WHERE deleted_at IS NULL somewhere, so deleted rows reappear - C) Lost foreign keys - D) Data is unrecoverable

Q4. The simplest, default hierarchy model is: - A) Nested set - B) Adjacency list (parent_id) - C) Closure table - D) Materialized path

Q5. Which hierarchy model has fast reads but expensive writes (renumbering)? - A) Adjacency list - B) Nested set - C) Closure table - D) Materialized path

Q6. A closure table stores: - A) Only direct parent links - B) All ancestor–descendant pairs (fast subtree reads) - C) A path string - D) Nothing

Q7. Tagging (item has many tags) is modeled with: - A) A comma-separated column - B) An array column always - C) A junction table (M:N) - D) A trigger

Q8. The polymorphic "type + id" columns approach: - A) Enforces foreign-key integrity - B) Cannot have a real foreign key (integrity not enforced) - C) Is always best - D) Requires nested sets

Q9. Shared-schema multi-tenancy (tenant_id column) without row-level security risks: - A) Slow writes - B) Cross-tenant data leakage (forgotten filter) - C) Lost backups - D) Nothing

Q10. Which multi-tenancy model gives the strongest isolation but most operational overhead? - A) Shared schema + tenant_id - B) Schema per tenant - C) Database per tenant - D) No tenancy

True/False

Q11. Storing audit before/after rows as jsonb lets one audit table serve any table. (True / False)

Q12. Nested sets are a great choice for a tree that is reorganized frequently. (True / False)

Short answer

Q13. A comment must attach to a post or a photo. Give one approach that preserves foreign-key integrity and one that sacrifices it, and state when the simpler one is acceptable.

---

Answer key

Q1 — B. A trigger fires for all write paths (app, admin, cascades) — completeness application code can't guarantee.

Q2 — B. Mark, don't remove (recoverable; preserves references/history).

Q3 — B. A forgotten filter leaks deleted rows; mitigate with a filtering view.

Q4 — B. Adjacency list (parent_id) — simplest; queried with recursive CTEs.

Q5 — B. Nested set: fast reads, painful writes (renumbering).

Q6 — B. All ancestor–descendant pairs → simple, fast subtree joins.

Q7 — C. M:N junction table. Never CSV; array only for small read-whole lists.

Q8 — B. type+id can't have a real FK (the id points to different tables), so integrity isn't enforced.

Q9 — B. A forgotten tenant_id filter leaks data across tenants; RLS enforces it at the DB.

Q10 — C. Database per tenant — strongest isolation, highest overhead.

Q11 — True. A jsonb old_row/new_row captures any table's whole row generically.

Q12 — False. Frequent reorganization makes nested-set writes (renumbering) very expensive; use adjacency list or closure table.

Q13. Integrity-preserving: separate junctions/FKs per type (post_comments, photo_comments) or a shared parent supertype both posts and photos reference, with comments pointing to it — real foreign keys. Integrity-sacrificing: a single comments(commentable_type, commentable_id) — one table, but no FK possible. The simpler type+id approach is acceptable when integrity is enforced elsewhere (app-level), the relationship is low-risk, and the convenience outweighs the lost referential guarantee — but choose it knowingly.

Scoring: 11–13 you've got the vocabulary; 8–10 review hierarchies and polymorphism; below 8, redo Exercises B–C.