Database Schema Design

Database Schema Design

Overview

Guide the design, implementation, and optimization of database schemas with sound data modeling, safe migrations, effective indexing, and appropriate query patterns. This skill covers the full lifecycle from conceptual modeling through physical optimization, ensuring schemas that are normalized, performant, and safely evolvable.

Announce at start: "I'm using the database-schema-design skill to design the database schema."

Phase 1: Discovery and Conceptual Model

Ask these questions to understand the data requirements:

Storage Engine Decision Table

STOP after discovery — present the conceptual model (entities, relationships, cardinality) for confirmation.

Phase 2: Logical Model Design

Translate the conceptual model into tables, columns, types, and constraints.

Column Design Rules

Normalization Guide

Rule: Always start normalized. Denormalize only with measured evidence.

Denormalization Decision Table

Relationship Patterns

STOP after logical model — present the table definitions for review.

Phase 3: Physical Model and Indexing

Index Type Decision Table

Composite Index Column Order

Rule: A composite index on (A, B, C) supports queries on A, A+B, A+B+C — but NOT B alone or C alone.

Query Optimization Checklist

N+1 Detection and Prevention

-- N+1 problem (bad):
SELECT * FROM users;
-- Then for EACH user: SELECT * FROM orders WHERE user_id = ?;

-- Fixed with join:
SELECT u.*, o.* FROM users u LEFT JOIN orders o ON o.user_id = u.id;

-- Fixed with batch load:
SELECT * FROM orders WHERE user_id = ANY($1);

STOP after physical model — present indexes and optimization strategy for review.

Phase 4: Migration Strategy

Zero-Downtime Migration (Expand-Contract)

Never make a breaking change in a single migration. Use two phases:

Expand phase (backward compatible):

1. Add new column/table (nullable or with default)
2. Deploy code that writes to both old and new
3. Backfill existing data in batches
4. Deploy code that reads from new

Contract phase (after all code uses new schema):

1. Remove code that writes to old
2. Drop old column/table

Migration Safety Rules

Backfill Pattern

-- Backfill in chunks of 1000
UPDATE users SET display_name = username
WHERE display_name IS NULL
AND id IN (SELECT id FROM users WHERE display_name IS NULL LIMIT 1000);

Migration Type Decision Table

STOP after migration plan — confirm rollback strategy before finalizing.

Phase 5: Save and Transition

After explicit approval:

1. Save schema design to docs/database/ or generate migration files
2. Commit with message: docs(db): add schema design for <feature>

Transition Decision Table

ORM Guidance

ORM Best Practices

• Always review generated SQL (enable query logging in development)
• Use eager loading to prevent N+1 queries
• Write raw SQL for complex queries rather than fighting the ORM
• Use ORM migrations, not auto-sync in production
• Test query performance with realistic data volumes

Connection Pooling

• Use a connection pooler (PgBouncer, built-in pool)
• Pool size formula: connections = (CPU cores * 2) + disk spindles
• Use transaction-level pooling for most workloads
• Application servers should not open raw connections

Anti-Patterns / Common Mistakes

Anti-Rationalization Guards

• Do NOT skip the conceptual model — understand entities and relationships first
• Do NOT add indexes speculatively — measure query patterns first
• Do NOT denormalize without measured evidence of a performance problem
• Do NOT create migrations without rollback plans
• Do NOT skip the discovery phase — understand query patterns and data volume
• Do NOT drop columns or tables without expand-contract pattern in production

Documentation Lookup (Context7)

Use mcp__context7__resolve-library-id then mcp__context7__query-docs for up-to-date docs. Returned docs override memorized knowledge.

• prisma — for schema syntax, relations, or migration API
• typeorm — for entity decorators, repository patterns, or query builder
• knex — for query builder syntax, migrations, or seed files

Integration Points

Verification Gate

Before claiming the schema design is complete:

1. VERIFY all entities and relationships are modeled
2. VERIFY normalization is at least 3NF (or denormalization is justified)
3. VERIFY indexes are defined for all query patterns and FK columns
4. VERIFY migration strategy includes rollback for every step
5. VERIFY the user has approved the schema design
6. VERIFY connection pooling strategy is defined for production

Skill Type

Flexible — Adapt storage engine, normalization level, and index strategy to project needs while preserving the conceptual-to-physical modeling progression, migration safety rules, and measured-evidence-before-denormalization principle.