database-schema-designer

Design schemas that survive production

Normalise first. Index for query patterns. Never sacrifice data integrity for convenience.

Design process

Step 1 — Requirements → Entities

Extract nouns from the requirement. Each noun that has attributes and participates in relationships is likely an entity. Resist making everything a single wide table.

Step 2 — Relationships

Identify cardinality before choosing the table structure:

Relationship	Implementation
1:1	Foreign key on the less-common side (or same table if always loaded together)
1:N	Foreign key on the N side
M:N	Junction table with FK to both sides; add attributes to the junction if needed

Step 3 — Normalisation

Target 3NF for transactional data:

1. 1NF — atomic values; no repeating groups; primary key identifies each row.
2. 2NF — no partial dependencies (non-key column depends on the whole PK, not a subset).
3. 3NF — no transitive dependencies (non-key column depends only on the PK, not on another non-key column).

Denormalise deliberately for read-heavy analytics tables — document the trade-off.

Standard conventions

• Primary key: id (UUID v7 for distributed systems; BIGSERIAL for single-node).
• Timestamps: created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now().
• Soft delete: deleted_at TIMESTAMPTZ nullable — add a partial index WHERE deleted_at IS NULL.
• Audit trail: separate audit_log table with entity_type, entity_id, action, actor_id, changed_at, before, after.
• Multi-tenancy: tenant_id on every tenant-scoped table; composite primary key or FK constraint; RLS policy.

Index strategy

• Index every foreign key (databases do not do this automatically).
• Add composite indexes for the most common WHERE col1 = ? AND col2 = ? patterns.
• Partial indexes for filtered queries (WHERE deleted_at IS NULL, WHERE status = 'active').
• Covering indexes (INCLUDE (col)) to avoid heap lookups on hot read paths.
• Drop indexes that are never used — they slow writes.