Database Design

Design efficient, maintainable database schemas with safe migration strategies.

Design Process

┌─────────────────────────────────────────────────────────────────┐
│                    DATABASE DESIGN PROCESS                       │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  1. REQUIREMENTS      2. MODELING         3. SCHEMA             │
│  ┌─────────────┐     ┌─────────────┐     ┌─────────────┐       │
│  │ Entities    │  →  │ ER diagram  │  →  │ Tables &    │       │
│  │ Attributes  │     │ Relations   │     │ Columns     │       │
│  │ Constraints │     │ Cardinality │     │ Constraints │       │
│  └─────────────┘     └─────────────┘     └─────────────┘       │
│                                                                 │
│  4. INDEXES          5. MIGRATION        6. REVIEW              │
│  ┌─────────────┐     ┌─────────────┐     ┌─────────────┐       │
│  │ Query       │  →  │ Safe changes│  →  │ Performance │       │
│  │ patterns    │     │ Rollback    │     │ Consistency │       │
│  │ Performance │     │ Zero-down   │     │ Integrity   │       │
│  └─────────────┘     └─────────────┘     └─────────────┘       │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Core Principles

1. Data Integrity First

Enforce constraints at the database level, not just application.

-- ✅ Database enforces integrity
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
);

-- ❌ Relying only on application validation
CREATE TABLE orders (
  id INTEGER,
  user_id INTEGER,  -- no FK
  total TEXT        -- wrong type, no constraint
);

2. Normalize by Default, Denormalize with Purpose

Start with 3NF. Denormalize only when you have measured performance needs.

✅ Normalize: Eliminate redundancy, maintain consistency
✅ Denormalize: Reduce joins for read-heavy queries (with justification)
❌ Premature optimization without query patterns

3. Explicit Over Implicit

Clear naming, explicit constraints, documented decisions.

-- ✅ Explicit
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

-- ❌ Implicit
user INTEGER  -- What does this reference?
created TEXT  -- When? Timezone?

4. Plan for Evolution

Schema will change. Design for safe migrations.

✅ Additive changes preferred (add columns, tables)
✅ Nullable new columns (can deploy before backfill)
✅ Soft deletes for recoverable data
❌ Breaking changes without migration plan

Quick Decisions

Primary Key Strategy

Strategy	Use When	Pros	Cons
UUID v4	Distributed systems, security-sensitive	No collisions, unpredictable	128-bit, random = bad index
UUID v7	Modern default choice	Sortable, no collisions	Larger than integer
ULID	Need K-sortable + readable	URL-safe, time-ordered	Less common
Auto-increment	Simple apps, legacy compat	Small, sequential	Enumerable, single-point
Composite	Junction tables	Natural fit for M:N	Complex queries

Recommendation: UUID v7 or ULID for new projects. Auto-increment for simple apps.

Relationship Patterns

One-to-One:   user ←→ profile       → FK + UNIQUE on child
One-to-Many:  user ←→ orders        → FK on "many" side
Many-to-Many: users ←→ roles        → Junction table
Self-ref:     employee ←→ manager   → FK to same table
Polymorphic:  comments on any entity → See references/schema-patterns.md

When to Denormalize

Signal	Action
N+1 queries in hot path	Consider embedding
Join across 4+ tables frequently	Materialized view or redundant column
Counting relationships is slow	Store counter cache
Full-text search on joined data	Denormalize to search index

Rule: Measure first. Denormalize with documentation.

Soft Delete vs Hard Delete

Use Soft Delete	Use Hard Delete
Audit requirements	GDPR "right to erasure"
User-recoverable data	Session/temporary data
Billing/financial records	PII after retention period
Referenced by other tables	Truly ephemeral data

-- Soft delete pattern
deleted_at TIMESTAMPTZ DEFAULT NULL
-- Query non-deleted: WHERE deleted_at IS NULL
-- Create partial index for performance
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;

Standard Schema Patterns

Timestamps

Always include, always use timezone:

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
-- Add trigger or ORM hook for updated_at

Audit Columns

created_by UUID REFERENCES users(id),
updated_by UUID REFERENCES users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Soft Delete

deleted_at TIMESTAMPTZ DEFAULT NULL,
deleted_by UUID REFERENCES users(id)

Versioning

version INTEGER NOT NULL DEFAULT 1
-- Increment on update, use for optimistic locking

Index Design Checklist

□ Primary key (automatic)
□ Foreign keys (add manually in most DBs)
□ Unique constraints
□ Columns in WHERE clauses (high selectivity)
□ Columns in ORDER BY
□ Columns in JOIN conditions
□ Composite indexes for multi-column queries

⚠️ Avoid:
□ Indexing low-cardinality columns alone (status, boolean)
□ Over-indexing (slows writes)
□ Indexes that duplicate existing coverage

Index Types Quick Reference

Type	Use Case	Example
B-tree	Default, range queries	Most columns
Hash	Equality only	Lookup tables
GIN	Arrays, JSONB, full-text	Tags, search
GiST	Geometric, range types	PostGIS, tsrange
BRIN	Very large, naturally ordered	Time-series

Migration Safety Checklist

Before any production migration:

□ Migration is reversible (has rollback plan)
□ Tested on copy of production data
□ Estimated lock time calculated
□ Deployment can proceed if migration fails
□ New code works with old AND new schema
□ Backfill strategy for new columns

Safe Migration Patterns

Change	Safe Approach
Add column	Add as nullable, deploy code, backfill, add NOT NULL
Remove column	Stop using in code, deploy, then remove column
Rename column	Add new, copy data, update code, remove old
Add index	CREATE INDEX CONCURRENTLY (PostgreSQL)
Add constraint	Add as NOT VALID, then VALIDATE separately

Dangerous Operations

-- ⚠️ Locks table (avoid on large tables)
ALTER TABLE users ADD COLUMN name VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE users ADD CONSTRAINT ... (without NOT VALID);
CREATE INDEX idx_users_email ON users(email);  -- non-concurrent

-- ✅ Safe alternatives
ALTER TABLE users ADD COLUMN name VARCHAR(255);  -- nullable first
ALTER TABLE users ADD CONSTRAINT ... NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT ...;  -- separate transaction
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Anti-Patterns

❌ God Tables

-- Everything in one table
CREATE TABLE data (
  id SERIAL,
  type VARCHAR(50),
  json_blob JSONB
);
-- No type safety, impossible to query efficiently

❌ EAV (Entity-Attribute-Value)

-- Attributes as rows
CREATE TABLE attributes (
  entity_id INT,
  attribute_name VARCHAR(100),
  attribute_value TEXT
);
-- Impossible to enforce types, terrible performance

❌ Implicit Relationships

-- Magic strings instead of FKs
CREATE TABLE orders (
  user TEXT,  -- Is this user_id? username? email?
  product TEXT
);

❌ Over-denormalization

-- Copying everything everywhere
CREATE TABLE orders (
  user_name VARCHAR(255),     -- What if name changes?
  user_email VARCHAR(255),    -- Stale data guaranteed
  user_address TEXT,
  user_phone VARCHAR(50)
);

❌ Missing Constraints

-- Database allows invalid data
CREATE TABLE products (
  price DECIMAL,  -- Can be NULL, negative, anything
  quantity INT    -- Negative inventory?
);

ORM Integration

Prisma Conventions

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  orders    Order[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  
  @@map("users")
}

model Order {
  id        String   @id @default(uuid())
  userId    String   @map("user_id")
  user      User     @relation(fields: [userId], references: [id])
  total     Decimal
  status    OrderStatus @default(PENDING)
  
  @@map("orders")
  @@index([userId])
}

Drizzle Conventions

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

export const orders = pgTable('orders', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id),
  total: decimal('total', { precision: 10, scale: 2 }).notNull(),
  status: varchar('status', { length: 20 }).notNull().default('pending'),
}, (table) => ({
  userIdIdx: index('orders_user_id_idx').on(table.userId),
}));

Design Deliverables

When designing a new schema, produce:

1. Entity list - All entities with key attributes
2. ER diagram - Relationships and cardinality (Mermaid or similar)
3. Schema DDL - CREATE TABLE statements
4. Index plan - Expected queries and supporting indexes
5. Migration plan - If modifying existing schema

Example ER Diagram (Mermaid)

erDiagram
    users ||--o{ orders : places
    users ||--o{ addresses : has
    orders ||--|{ order_items : contains
    products ||--o{ order_items : "ordered in"
    
    users {
        uuid id PK
        string email UK
        string name
        timestamp created_at
    }
    
    orders {
        uuid id PK
        uuid user_id FK
        decimal total
        enum status
        timestamp created_at
    }

---

References:

• references/schema-patterns.md — Common schema patterns (users, multi-tenant, polymorphic, etc.)
• references/migration-strategies.md — Safe migration techniques, rollback procedures, zero-downtime patterns
• references/index-design.md — When and how to add indexes, query analysis
• references/postgresql.md — PostgreSQL-specific features, extensions, optimization
• references/prisma-patterns.md — Prisma schema design, migrations, best practices
• references/query-optimization.md — Identifying and fixing slow queries, EXPLAIN analysis