Database Design Skill

A Claude Code skill for designing normalized, performant database schemas — covering entity relationships, indexing strategies, migration workflows, and query optimization for relational and NoSQL databases.

When to Use This Skill

Choose this skill when:

• Designing a new database schema for an application
• Normalizing or denormalizing existing data models
• Planning database migrations for schema changes
• Optimizing query performance with indexes and partitioning
• Reviewing database design decisions for scalability
• Choosing between SQL and NoSQL for a specific use case

Consider alternatives when:

• You need to write specific SQL queries (use a SQL skill)
• You need database administration tasks (use a DBA skill)
• You need ORM-specific guidance (use a Prisma/TypeORM/Drizzle skill)

Quick Start

Copy
# Add to your Claude Code project
claude mcp add database-design

# Design a schema from requirements
claude "design a database schema for a multi-tenant SaaS project management app"

Copy
-- Example: Generated schema for project management
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'archived', 'deleted')),
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(org_id, name)
);

CREATE INDEX idx_projects_org_status ON projects(org_id, status);

Core Concepts

Normalization Levels

Form	Rule	When to Apply
1NF	No repeating groups, atomic values	Always
2NF	No partial dependencies on composite keys	Multi-column primary keys
3NF	No transitive dependencies	Default for most apps
BCNF	Every determinant is a candidate key	Complex relationships
Denormalized	Controlled redundancy for read performance	High-read, low-write workloads

Relationship Patterns

Copy
-- One-to-Many: User has many Posts
CREATE TABLE posts (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  title VARCHAR(255) NOT NULL
);

-- Many-to-Many: Posts have many Tags via join table
CREATE TABLE post_tags (
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

-- One-to-One: User has one Profile
CREATE TABLE profiles (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  bio TEXT,
  avatar_url VARCHAR(500)
);

-- Self-referencing: Comments with replies
CREATE TABLE comments (
  id UUID PRIMARY KEY,
  parent_id UUID REFERENCES comments(id),
  content TEXT NOT NULL
);

Indexing Strategy

Copy
-- Primary key indexes are automatic
-- Add indexes for frequently filtered/joined columns

-- Composite index for common query patterns
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index for active records only
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- GIN index for full-text search
CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', title || ' ' || body));

Configuration

Parameter	Type	Default	Description
database_type	string	"postgresql"	Target database: postgresql, mysql, sqlite, mongodb
normalization	string	"3NF"	Target normal form: 1NF, 2NF, 3NF, BCNF, denormalized
naming_convention	string	"snake_case"	Table/column naming: snake_case, camelCase, PascalCase
id_type	string	"uuid"	Primary key type: uuid, serial, ulid, cuid
timestamps	boolean	true	Add created_at/updated_at to all tables
soft_delete	boolean	false	Add deleted_at column for soft deletes
multi_tenant	boolean	false	Add tenant isolation columns

Best Practices

1. Start with 3NF and denormalize only when query performance demands it — normalization prevents data anomalies; denormalize specific tables based on measured slow queries, not assumptions.

2. Use UUIDs for primary keys in distributed systems — serial/auto-increment IDs create conflicts in multi-server setups; UUIDs are globally unique and safe for distributed architectures.

3. Index columns used in WHERE, JOIN, and ORDER BY clauses — but don't over-index; every index slows down writes. Monitor actual query patterns and add indexes for queries that appear in slow query logs.

4. Always add foreign key constraints — they enforce data integrity at the database level, preventing orphaned records; use ON DELETE CASCADE or ON DELETE SET NULL based on your business rules.

5. Design for soft deletes on user-facing data — add a deleted_at timestamp column instead of physically deleting rows; this allows recovery and audit trails while keeping queries clean with a WHERE deleted_at IS NULL filter.

Common Issues

Queries slow down as data grows — Missing indexes are the most common cause. Run EXPLAIN ANALYZE on slow queries to identify sequential scans, then add targeted indexes. Composite indexes should match your most common query patterns.

Schema migrations fail in production — Never run destructive migrations (DROP COLUMN, change type) without a rollback plan. Use multi-step migrations: add new column → backfill data → switch application code → drop old column in a later migration.

N+1 query patterns emerge from ORM usage — ORMs make it easy to write code that generates one query per related record. Use eager loading (include, populate, joinedload) for relationships you know you'll access, and monitor query counts in development.