SQ
SQL Architecture Patterns
SQL schema design patterns for migrations, normalization, and multi-tenant data modeling
Details
Language / Topic
SQLCategory
Architecture
Rules
balanced
- Design schemas in 3NF (Third Normal Form) by default — denormalize specific tables only after profiling proves that join cost outweighs storage and consistency complexity.
- Use surrogate primary keys (`id BIGSERIAL` or `id UUID DEFAULT gen_random_uuid()`) rather than natural keys for tables with mutable business identifiers.
- Add `created_at TIMESTAMPTZ DEFAULT NOW()` and `updated_at TIMESTAMPTZ DEFAULT NOW()` audit columns to every table — maintain `updated_at` via a trigger or ORM hook.
- Use `FOREIGN KEY` constraints with explicit `ON DELETE` and `ON UPDATE` actions (`RESTRICT`, `CASCADE`, `SET NULL`) — document the choice in a migration comment.
- Apply multi-tenant isolation with a `tenant_id` column on every tenant-scoped table and enforce it with Row Level Security policies — never filter by `tenant_id` in application code alone.
- Use migrations (Flyway, Liquibase, Alembic) for all schema changes — never apply DDL directly to production; every change must be reproducible and rollback-tested.
- Design junction tables for many-to-many relationships with a composite primary key on both foreign keys: `PRIMARY KEY (user_id, role_id)` — add a surrogate key only when the junction has its own attributes.