Data Modeling & Schema Design

Application Schema Patterns

Application schemas often need tenant isolation, deletion semantics, auditability, and immutable history in addition to normalized business facts. Each pattern solves a different requirement and moves complexity among constraints, queries, operations, and recovery.

Multitenancy is an isolation decision, not merely a tenant_id column.
Tenant identity belongs in uniqueness and references when identifiers are tenant-scoped.
Soft deletion preserves a row but changes the meaning of ordinary queries.
Audit logs record actions; history tables record prior states.
Immutable ledgers append corrections instead of updating prior facts.
Retention, privacy erasure, legal hold, backup, restore, and tenant export requirements can rule out an otherwise convenient pattern.

Multitenancy deployment choices

Tenant isolation comparison
Pattern	Isolation	Operations	Customization	Typical risk
Shared schema + discriminator	Logical, row scoped	One migration fleet; efficient pooling	Low	Missing tenant predicate or unscoped key leaks data
Schema per tenant	Namespace scoped	Many schema migrations and connections	Medium	Version drift and catalog overhead
Database per tenant	Database and often resource scoped	Backup, migration, and monitoring per database	High	Fleet cost and difficult cross-tenant analytics

Tenant-scoped identity and referential integrity

CREATE TABLE tenant_products (
  tenant_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  sku VARCHAR(80) NOT NULL,
  deleted_at TIMESTAMP,
  PRIMARY KEY (tenant_id, product_id),
  UNIQUE (tenant_id, sku)
);

CREATE TABLE tenant_order_items (
  tenant_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  line_no INTEGER NOT NULL,
  product_id BIGINT NOT NULL,
  PRIMARY KEY (tenant_id, order_id, line_no),
  FOREIGN KEY (tenant_id, product_id)
    REFERENCES tenant_products(tenant_id, product_id)
);

Repeating `tenant_id` in the foreign key makes cross-tenant references structurally invalid.

Deletion and history patterns answer different questions
Pattern	Strength	Cost or trap
Hard delete	Simple current-state queries and true removal	Loses row history; dependencies need an explicit retention policy
Soft delete	Easy restore and stable references	Every query and uniqueness rule acquires active/deleted semantics
Audit event	Actor, reason, request, and action provenance	May not reconstruct exact state unless events are complete and ordered
History/version table	As-of state reconstruction	More rows, interval rules, and correction semantics
Immutable append-only facts	Strong lineage and replay	Corrections are compensating entries; current state is derived