database-schema-designerlisted

# 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` ta