database-design

Database Design Guidelines

Directives only. No full scripts, schemas, or pipelines.

---

Relational Design Directives

Normalisation

• Normalise to 3NF by default; remove transitive dependencies and repeating groups
• Keep each table focused on a single entity; use junction tables for many-to-many
• Denormalise only when: read-heavy workloads justify write cost, joins are proven bottlenecks, or a dominant query pattern is stable
• Document denormalisation decisions; keep them reversible

Schema Structure

• Use UUIDs or surrogate keys when natural keys are unstable or composite
• Prefer TIMESTAMP WITH TIME ZONE for timestamps
• Add created_at and updated_at on all mutable tables
• Use ON DELETE CASCADE / ON DELETE SET NULL only when the business rule is explicit

---

Indexing Strategy

Index Types and When to Use

• Primary key — Automatic; unique per row; prefer clustered where supported
• Single-column — Unique lookups (e.g. email), foreign keys, frequently filtered columns
• Composite — Multi-column filters and sorts; order by selectivity (most selective first)
• Partial — Subset of rows (e.g. WHERE is_active = true); reduces size and maintenance
• Text search — GIN/GiST for full-text; B-tree for prefix/suffix

Indexing Rules

• Index foreign keys used in joins
• Avoid indexing low-cardinality columns alone
• Avoid over-indexing write-heavy tables
• Run ANALYZE after bulk loads; schedule VACUUM / REINDEX where needed
• Monitor usage; drop unused indexes

CREATE INDEX idx_users_active_created ON users(is_active, created_at);
CREATE INDEX idx_users_email ON users(email) WHERE is_active = true;

---

Data Integrity

Constraints

• Use NOT NULL for required fields
• Use CHECK for domain rules (e.g. status IN ('draft', 'published'))
• Use UNIQUE for natural keys
• Add cross-column checks where rules span columns (e.g. published_at required when status = 'published')

Foreign Keys