Process use when you need to work with database schema design. This skill provides schema design and migrations with comprehensive guidance and automation. Trigger with phrases like "design schema", "create migration", or "model database".
npxskills add jeremylongshore/claude-code-plugins-plus-skills--skill designing-database-schemasLoading…
Design normalized relational database schemas from business requirements, entity-relationship diagrams, or existing application code. This skill produces PostgreSQL or MySQL DDL with proper data types, constraints, indexes, and relationships following normalization principles (3NF by default) with strategic denormalization where performance requires it.
psql or mysql CLI for testing schema DDLIdentify all entities (nouns) from the business requirements. Each entity becomes a table. List every attribute (property) of each entity and classify as required or optional.
Define primary keys for each table. Prefer BIGSERIAL (PostgreSQL) or BIGINT AUTO_INCREMENT (MySQL) for surrogate keys. Use UUID (via ) for distributed systems or when IDs are exposed in URLs. Natural keys are acceptable when truly immutable and unique (ISO country codes, IATA airport codes).
gen_random_uuid()Normalize the schema to Third Normal Form (3NF):
Define relationships between tables:
orders.customer_id REFERENCES customers(id).product_categories(product_id, category_id) with a composite primary key.Choose appropriate data types with precision:
NUMERIC(12,2) or INTEGER storing cents (never FLOAT/DOUBLE)TIMESTAMPTZ (PostgreSQL) with time zone for events; DATE for calendar datesVARCHAR(20) with CHECK constraint, or create an ENUM typeCITEXT (PostgreSQL) or VARCHAR(254) with CHECK constraint for format validationJSONB (PostgreSQL) for flexible schema attributes; avoid for core relational dataAdd standard columns to every table:
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() (with trigger for auto-update)deleted_at TIMESTAMPTZ for soft delete (add partial index WHERE deleted_at IS NULL)Define constraints: NOT NULL on required fields, UNIQUE on natural keys and email addresses, CHECK constraints for value validation (CHECK (price >= 0), CHECK (status IN ('active', 'inactive'))), and foreign keys with appropriate ON DELETE behavior (CASCADE, SET NULL, or RESTRICT).
Design indexes based on expected query patterns:
tsvectorApply strategic denormalization where 3NF causes unacceptable query complexity:
Generate the complete DDL script with CREATE TABLE statements in dependency order (referenced tables first), followed by indexes, triggers, and any seed data for lookup tables.
| Error | Cause | Solution |
|---|---|---|
| Circular foreign key dependency | Tables reference each other, preventing creation in any order | Use ALTER TABLE ADD CONSTRAINT after both tables are created; or redesign to eliminate the cycle with a junction table |
| Over-normalization causing excessive JOINs | Every lookup value in its own table, queries require 8+ JOINs | Denormalize low-cardinality, rarely-changing lookup values; use ENUM types for status fields instead of separate tables |
| NUMERIC precision overflow | Monetary values exceed NUMERIC(10,2) maximum | Increase precision to NUMERIC(15,2) or NUMERIC(19,4) for currencies requiring sub-cent precision |
| Schema too rigid for evolving requirements | Frequent ALTER TABLE needed as business rules change | Use JSONB columns for flexible attributes; implement the EAV (Entity-Attribute-Value) pattern for truly dynamic schemas; plan for schema evolution from the start |
| Missing index on foreign key column | JOINs on foreign key columns cause sequential scans | Always create indexes on foreign key columns; PostgreSQL does not auto-index foreign keys (unlike MySQL InnoDB) |
E-commerce schema design: Tables: customers, addresses (one-to-many from customers), products, categories (many-to-many via product_categories), orders, order_items (one-to-many from orders), payments. Money stored as NUMERIC(12,2). Soft delete on customers and products. GIN index on products.search_vector for full-text search. Composite index (customer_id, created_at DESC) on orders for order history pages.
Multi-tenant SaaS schema with row-level security: Every table includes tenant_id BIGINT NOT NULL with a foreign key to tenants. Row-level security policies enforce tenant isolation: CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id')::bigint). Composite indexes start with tenant_id for partition-like query performance.
Event sourcing schema: An events table with (aggregate_id, sequence_number) as composite primary key, event_type VARCHAR(100), payload JSONB, created_at TIMESTAMPTZ. A snapshots table stores materialized state at periodic intervals. Append-only design with no UPDATE or DELETE operations.
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Set up and use 1Password CLI (op). Use when installing the CLI, enabling desktop app integration, signing in (single or multi-account), or reading/injecting/running secrets via op.
CLI to manage emails via IMAP/SMTP. Use `himalaya` to list, read, write, reply, forward, search, and organize emails from the terminal. Supports multiple accounts and message composition with MML (MIME Meta Language).
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Set up and use 1Password CLI (op). Use when installing the CLI, enabling desktop app integration, signing in (single or multi-account), or reading/injecting/running secrets via op.
CLI to manage emails via IMAP/SMTP. Use `himalaya` to list, read, write, reply, forward, search, and organize emails from the terminal. Supports multiple accounts and message composition with MML (MIME Meta Language).