Designs normalized, constrained, migration-friendly relational schemas – entity modeling, key and type selection, indexes derived from real query patterns, and safe forward/rollback migrations.
Designs normalized, constrained, migration-friendly relational schemas - entity modeling, key and type selection, indexes derived from real query patterns, and safe forward/rollback migrations. Use when someone asks "design a schema for X", "should I normalize or denormalize this", "what should my primary key be", "how do I add this column without downtime", or "why is my unique constraint broken with soft deletes". Do NOT use for tuning a slow query on an existing schema - use sql-query-optimizer instead; for picking indexes from a live workload, use index-advisor; for sizing database connection pools, use connection-pool-tuner; for range/hash partitioning decisions, use partition-planner.This is the skill's description: the text your AI reads to decide when the skill applies.
database-schema/SKILL.md
---
name: Database Schema Designer
description: Designs normalized, constrained, migration-friendly relational schemas - entity modeling, key and type selection, indexes derived from real query patterns, and safe forward/rollback migrations. Use when someone asks "design a schema for X", "should I normalize or denormalize this", "what should my primary key be", "how do I add this column without downtime", or "why is my unique constraint broken with soft deletes". Do NOT use for tuning a slow query on an existing schema - use sql-query-optimizer instead; for picking indexes from a live workload, use index-advisor; for sizing database connection pools, use connection-pool-tuner; for range/hash partitioning decisions, use partition-planner.
---
# Database Schema Designer
A schema is the one part of a system you cannot refactor with a find-and-replace: every shortcut taken at design time becomes a locked-table migration on a hot production database later. This skill produces schemas that stay correct and fast as data grows - invalid states unrepresentable, indexes matched to real queries, and every change shippable without downtime.
## Operating procedure
Follow the steps in order. Constraints come before indexes because constraints define correctness and indexes only define speed; migrations come last because they depend on both.
### Step 1: Gather inputs
Collect these before writing any DDL. If a number is a guess, label it a guess and proceed.
1. Entities and relationships - nouns of the domain and their cardinality (1:1, 1:N, M:N).
2. The top 5-10 queries by expected frequency, with their filter and sort columns. Indexes come from this list, not from intuition.
3. Expected row counts at 1 year and 3 years per table, and read:write ratio per hot table. Default assumption if unknown: 10:1 read-heavy.
4. Multi-tenancy - single tenant, or `tenant_id` on every row?
5. Deletion semantics - hard delete, soft delete, or audit-retained?
### Step 2: Model, normalize, then denormalize only on evidence
… load the full skill through Skill MeClick to play with sound.