Database Design

Create a junction table with foreign keys referencing both entities and a composite primary key on the pair. For example, an enrollments table linking students and courses with student_id and course_id columns, plus indexes on each foreign key.

Denormalize for read-heavy workloads where joins hurt performance, such as caching aggregate counts or flattening frequently joined columns. Normalize first for write-heavy workloads and data integrity, then denormalize selectively using triggers or materialized views.

Embed when data is accessed together, the relationship is one-to-few, and documents stay under the 16MB limit. Reference when data is accessed independently, changes frequently, or the relationship is one-to-many or many-to-many.

Use the expand-migrate-contract pattern: add the new column as nullable, update application code to write both columns, backfill existing rows, then drop old columns after all instances are updated. Avoid adding NOT NULL columns without defaults on large tables.

A standard unique index blocks reusing a value from a soft-deleted row. Use a partial unique index with WHERE deleted_at IS NULL so only active rows must be unique, or a composite index on the value plus deleted_at.

Use DECIMAL(p,s) or NUMERIC for exact decimal arithmetic on monetary values. Never use FLOAT or DOUBLE, which introduce rounding errors. Use TIMESTAMPTZ rather than TIMESTAMP for timezone-aware timestamps.