database-schema-designer

Use a junction table when you need to JOIN, filter, or aggregate on individual values, require referential integrity, or have unbounded cardinality. Use a JSONB column when the set is small, bounded, varies per row, and is never queried relationally.

Denormalize only when a JOIN-based query measurably misses its latency budget, you have defined staleness tolerance, and a clear sync mechanism exists such as a trigger or materialized view. Keep normalized tables as the canonical source of truth.

Default to a shared schema with a tenant_id column for hundreds to thousands of tenants. Use schema-per-tenant for regulatory isolation at moderate tenant counts, and database-per-tenant only for contractual physical isolation requirements.

Avoid native Postgres ENUM types for category sets that may grow, shrink, or reorder, since enum values cannot be removed once shipped. Use a lookup table with a foreign key or a CHECK constraint instead.

Use separate nullable foreign keys with a CHECK constraint enforcing exactly one non-null when the target type set is small and stable. Reserve type-plus-id columns without database-level foreign keys for genuinely open-ended target sets.

Add new columns as nullable first, deploy code that writes to them, backfill existing rows, then add constraints. Always write a reversible DOWN migration and test it on staging with production-shaped data.