Database Schema Designer Skill

A Claude Code skill for generating production-ready database schemas from natural language descriptions — complete with tables, relationships, indexes, constraints, migrations, and seed data.

When to Use This Skill

Choose this skill when:

• Designing a complete database schema from a product description
• Generating Prisma, Drizzle, or raw SQL schema definitions
• Creating migration files for schema changes
• Reviewing an existing schema for performance and design issues
• Generating seed data for development and testing environments
• Visualizing entity relationships in a schema

Consider alternatives when:

• You need to optimize specific queries (use a query optimization skill)
• You need database administration or monitoring (use a DBA skill)
• You only need to add one table or column (just write the migration directly)

Quick Start

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

# Design a schema from description
claude "design a schema for an e-commerce platform with users, products, orders, and reviews"

Copy
-- Generated: E-commerce Schema
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(20) DEFAULT 'customer' CHECK (role IN ('customer', 'admin', 'vendor')),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  vendor_id UUID NOT NULL REFERENCES users(id),
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  description TEXT,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  stock_count INTEGER DEFAULT 0 CHECK (stock_count >= 0),
  status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'archived')),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  status VARCHAR(20) DEFAULT 'pending'
    CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
  total_cents INTEGER NOT NULL,
  shipping_address JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE order_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents INTEGER NOT NULL
);

-- Indexes for common query patterns
CREATE INDEX idx_products_vendor ON products(vendor_id) WHERE status = 'active';
CREATE INDEX idx_orders_user ON orders(user_id, created_at DESC);
CREATE INDEX idx_order_items_order ON order_items(order_id);

Core Concepts

Schema Generation Process

Step	Action	Deliverable
1. Entity Extraction	Identify entities from description	Entity list with attributes
2. Relationship Mapping	Define how entities connect	ERD with cardinality
3. Column Definition	Types, constraints, defaults	Full column specifications
4. Index Design	Query-driven index strategy	Index creation statements
5. Migration	Versioned schema changes	Migration files
6. Seed Data	Realistic test data	Insert statements or factories

ORM Schema Generation

Copy
// Prisma schema output
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  role      Role     @default(CUSTOMER)
  orders    Order[]
  reviews   Review[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}

model Product {
  id         String     @id @default(uuid())
  name       String
  slug       String     @unique
  priceCents Int        @map("price_cents")
  status     ProductStatus @default(DRAFT)
  orderItems OrderItem[]
  reviews    Review[]

  @@index([status])
  @@map("products")
}

Configuration

Parameter	Type	Default	Description
output_format	string	"sql"	Schema format: sql, prisma, drizzle, typeorm, knex
database	string	"postgresql"	Target database: postgresql, mysql, sqlite
id_strategy	string	"uuid"	Primary key type: uuid, serial, ulid, cuid
timestamps	boolean	true	Auto-add created_at and updated_at columns
soft_delete	boolean	false	Add deleted_at column pattern
generate_indexes	boolean	true	Auto-generate indexes for foreign keys and common queries
generate_seed	boolean	false	Generate seed data alongside schema
generate_migration	boolean	true	Output as a migration file with up/down

Best Practices

1. Store monetary values as integers (cents) — never use floating-point types for money; price_cents INTEGER avoids rounding errors and makes arithmetic reliable across all databases.

2. Use CHECK constraints for status enums — database-level constraints catch invalid values that application-level validation might miss, providing a safety net even when data is modified directly.

3. Design foreign keys with explicit ON DELETE behavior — choose CASCADE (delete children), SET NULL (orphan children), or RESTRICT (prevent deletion) based on business rules; never leave it as the default silent failure.

4. Generate partial indexes for filtered queries — if you always query WHERE status = 'active', a partial index CREATE INDEX ... WHERE status = 'active' is smaller and faster than indexing all rows.

5. Version your schema changes as migrations — never modify the schema directly in production; create versioned migration files that can be reviewed, tested, and rolled back.

Common Issues

Schema gets too normalized for read-heavy workloads — Joining five tables for every page load hurts performance. Identify your most common read queries and denormalize specifically for those patterns, using materialized views or computed columns.

Seed data doesn't reflect production patterns — Synthetic seed data with uniform distributions doesn't reveal performance issues. Generate seed data with realistic distributions: most users have 0-2 orders, a few have 100+; products follow a Pareto distribution.

Migration order causes foreign key errors — Tables must be created before their references. Sort migration statements by dependency order: independent tables first, then tables with foreign keys. Most migration tools handle this automatically but verify the output.