Database Design Patterns: Multi-Table Architecture

Advanced database design requires patterns that span multiple tables to handle auditing, relationship complexity, and temporal state.

---

I. Audit and Change Tracking

Audit Columns (Tier 1)

The universal default. Every table includes created_at, updated_at, created_by, and updated_by. This identifies when and who, but not the specific delta.

History Tables (Tier 2)

A parallel table stores a snapshot of the main table's row on every change via a database trigger.

• Trade-off: Increases storage and write overhead but provides a queryable audit trail for critical entities (e.g., orders, profiles).

Event Sourcing (Tier 3)

Events are the immutable source of truth; state is a projection.

• Implementation: Store events in an event_store table. Replay events to reconstruct state at any point in time. Use for financial ledgers or high-compliance domains.

---

II. Relationship Patterns

Soft Delete

Mark rows as deleted via a deleted_at timestamp rather than physical removal.

• Constraint Tip: Use partial unique indexes (e.g., UNIQUE (email) WHERE deleted_at IS NULL) to allow reuse of natural identifiers after deletion.

Polymorphic Associations

When a table (e.g., comments) can link to one of several parents (e.g., posts, videos).

• Recommended Pattern: Use Separate Foreign Key Columns for each parent type with a CHECK constraint ensuring exactly one is populated. This maintains referential integrity, which is lost in "type-tag + ID" patterns.

---

III. Performance and Scaling Patterns

Materialized Views

Stored, pre-computed aggregations refreshed periodically.

• Use Case: Expensive queries where minutes of staleness are acceptable (e.g., user_lifetime_value).
• Optimization: Use REFRESH MATERIALIZED VIEW CONCURRENTLY in Postgres to avoid blocking reads during updates.

Idempotency Table

A dedicated table to track unique request keys for side-effect-producing operations.

• Pattern: Store the idempotency_key and the result of the first successful execution. Subsequent retries with the same key return the stored result without re-executing business logic.

Outbox Pattern

Atomically write data to the database and an outbox table in a single transaction.

• Purpose: Ensures "at-least-once" delivery of events to external message brokers (Kafka, RabbitMQ) without distributed transactions.

---

IV. Temporal and Hierarchical Data

Effective-Dating

Track row values over time using effective_from and effective_until timestamps.

• Postgres Tip: Use TSTZRANGE types with exclusion constraints to prevent overlapping time windows at the database layer.

Hierarchical Storage

• Adjacency List: parent_id referencing the same table. Best for simple trees; query via Recursive CTEs.
• Ltree / Path: Storing the full path (e.g., A.B.C). Best for deep trees and prefix-based retrieval.

---

V. Strategic Alignment

Aging-well designs prioritize Correctness over Performance in the early phases.

1. Enforce constraints (FKs, CHECKs) from day one.
2. Use surrogate keys to decouple relationships from data volatility.
3. Implement auditing and soft-delete for user-controlled data.
4. Adopt the Outbox pattern for all external integrations.