Database Design Basics

Schema decisions are expensive to reverse once real data exists. Default to strict, explicit, and normalized — relax only with a specific reason.

Keys and identifiers

• Every table needs a primary key. Prefer a surrogate key (UUID or auto-incrementing integer) over a natural key (email, username) — natural keys change, surrogate keys don't.
• Never expose sequential integer IDs in public URLs for anything sensitive (invoices, private documents) — they're trivially enumerable. Use UUIDs or a separate public-facing slug for those.

Constraints do the validation the app forgets

• Add NOT NULL to every column that must always have a value — application-layer validation gets bypassed by scripts, migrations, and future code paths the original author never imagined; the database is the last line of defense.
• Use UNIQUE constraints for real-world uniqueness rules (one profile per user, one slug per author), not just an application-level check — a race condition between two concurrent requests will slip past an app-only check.
• Use CHECK constraints to enforce value ranges and enums at the schema level for anything security- or billing-relevant, mirroring the application's validation rather than replacing it.
• Use foreign keys with an explicit ON DELETE policy (CASCADE, RESTRICT, or SET NULL) chosen deliberately per relationship — never leave it to the default, and never let orphaned rows silently accumulate.

Normalization, pragmatically

• Normalize to remove duplicated, update-inconsistent data (the same value stored in multiple rows that must be kept in sync manually) — this is where most data-integrity bugs come from.
• Denormalize deliberately, and only for a measured read-performance reason (a computed counter, a materialized summary) — document why, and keep the source of truth authoritative so the denormalized copy can always be rebuilt.

Types

• Use the narrowest correct type: booleans for booleans, enums/check-constrained text for closed sets, timestamptz (not naive timestamp) for anything that crosses timezones.
• Never store money as floating point — integer minor units or a fixed-precision numeric type only.
• Use text over fixed-length varchar(n) unless a real length constraint from the business exists; then enforce it with a CHECK, not the type alone.

Indexes

• Index every foreign key column and every column used in a WHERE, JOIN, or ORDER BY on a frequently queried table — an unindexed foreign key turns cascading deletes and joins into full table scans as the table grows.
• Don't index everything reflexively — every index has a write-time cost. Add indexes based on actual query patterns, not speculation.

Migrations discipline

• Every schema change is a migration file, checked into version control, never a manual change against a running database — engine-specific conventions build on this baseline, not replace it.