Database Design

Database Design

Production-quality relational database design: schema, migrations, indexing, security, and multi-tenancy.

Core principle: Get the schema right before the application. Fixing schema design after data accumulates is expensive and risky.

When to Use

• Designing tables for a new feature
• Reviewing a schema for normalization or performance problems
• Planning zero-downtime migrations on large tables
• Adding multi-tenancy to a single-tenant schema
• Implementing audit trails, soft deletes, or row-level security
• Choosing index strategy for slow queries

When NOT to Use

• Writing application-level SQL queries (use coding-principles skill)
• ORM configuration only
• NoSQL database design (different skill domain)

Schema Design Fundamentals

Normalization

Goal: Aim for 3NF by default. Denormalize only with measured justification.

• 1NF: No repeating groups — atomic column values, unique rows
• 2NF: All non-key columns depend on the entire primary key
• 3NF: No transitive dependencies between non-key columns

Data Type Choices

Core Design Patterns

Soft Deletes

CREATE TABLE users (
  id        UUID PRIMARY KEY,
  email     VARCHAR(255) NOT NULL,
  deleted_at TIMESTAMPTZ,          -- NULL = active
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Always filter deleted in queries
SELECT * FROM users WHERE deleted_at IS NULL;

-- Partial index for performance
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;

Audit Trail

-- Lightweight: who/when on every table
created_by_id UUID NOT NULL REFERENCES users(id),
updated_by_id UUID NOT NULL REFERENCES users(id),
created_at    TIMESTAMPTZ DEFAULT NOW(),
updated_at    TIMESTAMPTZ DEFAULT NOW()

-- Full history: separate audit log table
CREATE TABLE task_audit_log (
  id          UUID PRIMARY KEY,
  task_id     UUID NOT NULL REFERENCES tasks(id),
  operation   VARCHAR(10),          -- INSERT, UPDATE, DELETE
  before_data JSONB,
  after_data  JSONB,
  changed_by  UUID NOT NULL REFERENCES users(id),
  changed_at  TIMESTAMPTZ DEFAULT NOW()
);

Multi-Tenancy

Add organization_id to all tenant-scoped tables. Denormalize it (even into child tables) so RLS policies can be simple.

CREATE TABLE projects (
  id              UUID PRIMARY KEY,
  organization_id UUID NOT NULL REFERENCES organizations(id),
  name            VARCHAR(255) NOT NULL,
  UNIQUE(organization_id, name)  -- Scoped uniqueness
);

CREATE TABLE tasks (
  id              UUID PRIMARY KEY,
  organization_id UUID NOT NULL REFERENCES organizations(id),  -- denormalized
  project_id      UUID NOT NULL REFERENCES projects(id),
  title           VARCHAR(255)
);

Optimistic Locking (Concurrent Updates)

CREATE TABLE accounts (
  id      UUID PRIMARY KEY,
  balance DECIMAL(19,2),
  version INT DEFAULT 1
);

-- Update only succeeds if version matches
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = $1 AND version = $2;
-- If rows_affected == 0: conflict detected → retry

Migration Planning

Zero-Downtime: Expand-Contract Pattern

Never rename a column or change a type in one step on a live system.

Phase 1 (Expand):   Add new column alongside old — no downtime
Phase 2 (Backfill): Migrate data in batches of 5,000 rows
Phase 3 (Dual-write): App writes both old + new columns
Phase 4 (Cutover):  App reads new column only — deploy
Phase 5 (Contract): Drop old column after 48h stability

Batch backfill (avoids table locks):

UPDATE users SET full_name = name
WHERE id IN (SELECT id FROM users WHERE full_name IS NULL LIMIT 5000);
-- Repeat until: SELECT COUNT(*) FROM users WHERE full_name IS NULL = 0

Migration Checklist

Before:

• Rollback script tested in staging
• Backup taken and restore verified
• Table size checked (large table → more careful approach)
• Lock contention plan (NOWAIT or statement_timeout)

After:

• All constraints in place
• Indexes created successfully
• ANALYZE run to update query planner statistics
• EXPLAIN plan confirms indexes are being used

Index Strategy

Decision Matrix

Reading EXPLAIN Plans

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 123;

Index Anti-Patterns

• Over-indexing (15+ indexes per table → slow writes)
• Foreign keys without indexes (slow JOINs and cascades)
• Redundant indexes (col1,col2) AND (col1) — second is redundant
• Non-sargable predicates: WHERE YEAR(created_at) = 2025 (can't use index)

Row-Level Security (RLS)

Enforce multi-tenancy at the database layer — defense-in-depth against application bugs.

ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Users see only their org's tasks
CREATE POLICY tasks_org_isolation ON tasks
  FOR ALL TO app_user
  USING (
    organization_id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = current_setting('app.current_user_id')::uuid
    )
  );

-- Soft-delete filter
CREATE POLICY tasks_no_deleted ON tasks
  FOR SELECT TO app_user
  USING (deleted_at IS NULL);

-- Set per-request context
SELECT set_config('app.current_user_id', $1, true);

Critical: Test RLS with a non-superuser role. Superuser bypasses all RLS policies.

Type Generation

Derive TypeScript/Python types from schema to stay in sync:

// Prisma schema → TypeScript types (auto-generated)
model Task {
  id             String    @id @default(cuid())
  organizationId String
  title          String
  deletedAt      DateTime?
  createdAt      DateTime  @default(now())
}
// Generates: type Task = { id: string; organizationId: string; ... }

Common Mistakes

Verification Checklist

• All tables have UUID/CUID primary keys
• All foreign key columns have indexes
• Schema normalized to 3NF (or denormalization justified)
• NOT NULL, UNIQUE, CHECK, FK constraints in place
• created_at, updated_at on all tables
• Soft delete pattern if data needs to be recoverable
• Audit trail for regulated domains
• organization_id on all tenant-scoped tables
• RLS policies tested with non-superuser role
• Partial indexes for soft-delete filters
• EXPLAIN plans show index usage on common queries
• Zero-downtime migration pattern for large table changes
• TypeScript/Python types generated from schema