database-design-patterns — Claude Code skill: how to install
database-design-patterns guides relational schema design, 3NF normalization, index selection, and zero-downtime migrations using the expand-contract pattern.
MIT 152 installs ★ 274 updated 2026-10-30
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a claude-codeWhat database-design-patterns does
The skill structures relational database schemas from initial drafts to production rollouts. It enforces normalization up to 3NF, eliminating partial or transitive dependencies. Speculative denormalization is forbidden. When queries slow down, cached columns or materialized views are considered only after EXPLAIN confirms a join bottleneck.
Index recommendations match actual query shapes: standard B-tree for scalars, GIN for JSONB, or partial indexes for common status filters. Dangerous schema changes follow the expand-contract pattern. This keeps production online through phased dual-writes and backfills. For inspecting files, the skill relies on Read, Write, Edit, Grep, and Glob.
Live query plan tuning via EXPLAIN ANALYZE falls outside this skill. Such profiling belongs in a specialized query optimizer instead. It also leaves NoSQL document modeling out of scope.
What it helps with
- normalize relational tables up to 3NF without transitive dependencies
- select index types between B-tree, GIN, GiST, partial, or covering indexes
- plan zero-downtime database migrations using expand-contract
- configure connection pooling for PgBouncer or Prisma
- choose between surrogate and composite primary keys
Example prompts for the agent
- Design a normalized 3NF schema for an e-commerce catalog with product tags
- Help me pick a partial index for active user lookups by status and email
- Plan a zero-downtime migration to rename a column in our production user table
How to install database-design-patterns
1Skills CLI (Vercel)
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a claude-code2Manually: put the files in place
~/.claude/skills/database-design-patterns/SKILL.mdCopy the whole skill folder; for one project use .claude/skills/database-design-patterns/
1Skills CLI (Vercel)
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a codex2Manually: put the files in place
.agents/skills/database-design-patterns/SKILL.mdFor all projects use ~/.agents/skills/
1Skills CLI (Vercel)
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a cursor2Manually: put the files in place
.cursor/skills/database-design-patterns/SKILL.mdCursor also reads .agents/skills/ and .claude/skills/
1GitHub CLI
gh skill install curiositech/some_claude_skills .claude/skills/database-design-patterns2Manually: put the files in place
.github/skills/database-design-patterns/SKILL.md1Skills CLI (Vercel)
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a gemini-cli2Platform CLI
gemini skills install https://github.com/curiositech/some_claude_skills --path .claude/skills/database-design-patternsManually: put the files in place
.devin/skills/database-design-patterns/SKILL.mdThe legacy .windsurf/skills/ path still works
Skills CLI (Vercel)
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a opencodeClaude.ai
Zip the folder and upload it in Settings → Capabilities → Skills
Step by step, with commands for each platform: How to install skills in Claude Code: five ways
Questions about database-design-patterns
How to install database-design-patterns in Claude Code?
Run npx skills add curiositech/some_claude_skills --skill database-design-patterns -a claude-code in your terminal.
What does database-design-patterns do?
It guides developers through relational schema modeling, 3NF normalization rules, choosing B-tree or GIN index types, and organizing zero-downtime migrations.
Does database-design-patterns handle MongoDB or EXPLAIN query tuning?
No, live query profiling via EXPLAIN ANALYZE and document databases like MongoDB are out of scope. Query plan tuning belongs in postgresql-optimization, while this skill focuses strictly on relational schema architecture.
Source text
SKILL.md · MIT
---
name: database-design-patterns
description: Schema design, normalization decisions, indexing strategies, and migration safety for relational databases. Use when designing tables, choosing between normalize vs denormalize, selecting index
types, planning zero-downtime migrations, or setting up connection pooling. Activate on "schema design", "normalization", "denormalization", "foreign key", "index strategy", "migration", "connection pooling",
"composite key", "surrogate key", "soft delete", "polymorphic association". NOT for PostgreSQL-specific query tuning or EXPLAIN analysis (use postgresql-optimization), NoSQL or document database design,
or cloud database provisioning.
allowed-tools: Read,Write,Edit,Grep,Glob
metadata:
category: Design & Creative
tags:
- database
- design
- patterns
- schema-design
- normalization
pairs-with:
- skill: drizzle-migrations
reason: Schema design decisions are implemented through ORM migration workflows
- skill: postgresql-optimization
reason: Index strategies and query patterns depend on underlying schema design choices
- skill: supabase-admin
reason: Supabase RLS policies and schema architecture implement database design patterns
---
# Database Design Patterns
Relational database schema design expert. Covers normalization decisions, index selection, migration safety, and connection pooling — the structural foundations that determine whether a database performs well at scale or becomes a maintenance burden.
## When to Use
Use for:
- Designing new schemas or refactoring existing ones
- Deciding whether to normalize or denormalize for a specific query pattern
- Choosing index types (B-tree, GIN, GiST, hash, partial, covering)
- Planning migrations that must not break running production systems
- Implementing soft deletes, polymorphic associations, or composite keys
- Configuring PgBouncer or Prisma connection pools
NOT for:
- Running EXPLAIN ANALYZE or reading query plans → use **postgresql-optimization**
- Document model design (MongoDB, DynamoDB) → use a NoSQL skill
- Database provisioning, replicas, or infrastructure → use a cloud/infra skill
- ORM-specific code generation → use the relevant ORM skill
---
## Normalize vs. Denormalize Decision Tree
```mermaid
flowchart TD
A[New data or query performance problem?] --> B{New data design?}
B -->|Yes| C[Start normalized: 3NF]
B -->|No — query too slow| D{Measured with EXPLAIN?}
D -->|No| E[Measure first. Never guess.]
D -->|Yes — proven join bottleneck| F{Read-heavy or write-heavy?}
F -->|Read-heavy, joins are the bottleneck| G[Controlled denormalization:\nmaterialized view or cached column]
F -->|Write-heavy or balanced| H[Keep normalized.\nOptimize query or add index first.]
C --> I{Any repeated groups in a row?}
I -->|Yes| J[1NF: Move to child table]
I -->|No| K{Non-key cols depend on part of PK?}
K -->|Yes| L[2NF: Extract to separate table]
K -->|No| M{Transitive dependencies?}
M -->|Yes| N[3NF: Extract lookup table]
M -->|No| O[Schema is 3NF — ship it]
```
**The rule**: Start at 3NF. Denormalize only after measuring, and only the specific join that is provably too slow. Never denormalize speculatively.
---
## Index Selection Decision Tree
```mermaid
flowchart TD
A[Which index type?] --> B{Data type and query pattern}
B -->|Equality or range on scalar| C[B-tree — default choice]
B -->|Full-text search, arrays, JSONB| D[GIN — inverted index]
B -->|Geometric / PostGIS types| E[GiST — generalized search]
B -->|Exact equality only, very high cardinality| F[Hash — rare, limited utility]
C --> G{Subset of rows frequently queried?}
G -->|Yes, e.g. status = 'active'| H[Partial index:\nWHERE status = 'active']
G -->|No| I{Query selects only indexed columns?}
I -->|Yes| J[Covering index:\nINCLUDE additional columns]
I -->|No| K[Standard B-tree index]
```
**Always index**:
- Every foreign key column (prevents full table scans on joins)
- Columns that appear in WHERE, ORDER BY, or JOIN ON clauses in frequent queries
- Composite indexes: put the most selective column first
**Consult** `references/indexing-strategies.md` when choosing between partial vs. covering indexes or tuning multi-column index column order.
---
## Migration Safety Decision Tree
```mermaid
flowchart TD
A[Schema change needed] --> B{Breaking change?}
B -->|No: add nullable column, add index| C[Single migration, safe to run]
B -->|Yes: rename column, change type, drop column| D[Expand-Contract pattern]
D --> E[Phase 1 — Expand:\nAdd new column/table, keep old]
E --> F[Deploy app: write to both old and new]
F --> G[Backfill existing rows to new column]
G --> H[Phase 2 — Contract:\nRemove old column once all reads use new]
H --> I[Deploy app: read only from new]
I --> J[Drop old column in final migration]
C --> K{Large table?}
K -->|Yes| L[CREATE INDEX CONCURRENTLY\nALTER TABLE with minimal lock]
K -->|No| M[Standard migration]
```
**Consult** `references/migration-patterns.md` for expand-contract templates, lock timeout settings, and rollback strategies.
---
## Normalization Reference
### 1NF — No Repeating Groups
Each column holds one value. No comma-separated lists in a column.
```sql
-- Bad: tags stored as CSV
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
tags TEXT -- "sql,indexing,performance"
);
-- Good: normalized to child table
CREATE TABLE article_tags (
article_id INT REFERENCES articles(id),
tag TEXT NOT NULL,
PRIMARY KEY (article_id, tag)
);
```
### 2NF — No Partial Dependencies (composite PKs only)
Every non-key column depends on the whole primary key, not just part of it.
```sql
-- Bad: product_name depends only on product_id, not on (order_id, product_id)
CREATE TABLE order_items (
order_id INT,
product_id INT,
product_name TEXT, -- should be in products table
quantity INT,
PRIMARY KEY (order_id, product_id)
);
```
### 3NF — No Transitive Dependencies
Non-key columns depend only on the primary key, not on each other.
```sql
-- Bad: zip_code determines city/state (transitive)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
zip_code TEXT,
city TEXT, -- derivable from zip_code
state TEXT -- derivable from zip_code
);
-- Good: extract lookup table
CREATE TABLE zip_codes (
zip TEXT PRIMARY KEY,
city TEXT,
state TEXT
);
```
---
## Key Design Decisions
### Surrogate vs. Composite Keys
Use surrogate keys (serial/UUID) when:
- The natural key is multi-column and would be repeated in child tables as FK
- The natural key can change (email addresses, usernames)
- The table will be referenced by many other tables
Use composite primary keys when:
- The table is a pure join/association table with no additional attributes
- The combination is truly stable and globally unique
```sql
-- Pure join table: composite PK is correct
CREATE TABLE user_roles (
user_id INT REFERENCES users(id),
role_id INT REFERENCES roles(id),
PRIMARY KEY (user_id, role_id)
);
-- Association with attributes: add surrogate key
CREATE TABLE user_project_memberships (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
project_id INT REFERENCES projects(id),
joined_at TIMESTAMPTZ DEFAULT NOW(),
role TEXT
);
```
### Soft Deletes
```sql
-- Pattern: deleted_at nullable timestamp
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;
-- Partial index makes "active" queries fast
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
WHERE deleted_at IS NULL;
-- View hides soft-deleted rows for application code
CREATE VIEW active_orders AS
SELECT * FROM orders WHERE deleted_at IS NULL;
```
**Warning**: Soft deletes complicate unique constraints. A unique email column allows only one deleted user with that email. Use partial unique indexes:
```sql
CREATE UNIQUE INDEX idx_users_email_active ON users (email)
WHERE deleted_at IS NULL;
```
### Polymorphic Associations
Two approaches — avoid the naive pattern:
```sql
-- Bad: nullable FK columns for each possible parent type
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INT REFERENCES posts(id), -- nullable
article_id INT REFERENCES articles(id), -- nullable
video_id INT REFERENCES videos(id), -- nullable
body TEXT
);
-- Good: separate association tables (referential integrity preserved)
CREATE TABLE post_comments (
comment_id INT REFERENCES comments(id),
post_id INT REFERENCES posts(id),
PRIMARY KEY (comment_id, post_id)
);
-- Or: single-table inheritance with a type column + CHECK constraint
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
parent_type TEXT NOT NULL CHECK (parent_type IN ('post', 'article', 'video')),
parent_id INT NOT NULL,
body TEXT
);
CREATE INDEX idx_comments_parent ON comments (parent_type, parent_id);
```
### Connection Pooling
PgBouncer configuration for typical web applications:
```ini
[pgbouncer]
pool_mode = transaction ; Best for short-lived web requests
max_client_conn = 1000 ; Total client connections pooler accepts
default_pool_size = 20 ; DB connections per database/user pair
server_idle_timeout = 600 ; Close idle server connections after 10 min
```
Prisma with PgBouncer — set `pgbouncer=true` in the connection URL:
```
DATABASE_URL="postgresql://user:pass@host:6432/db?pgbouncer=true&connection_limit=1"
```
**Note**: PgBouncer transaction mode does not support prepared statements, `SET`, or `LISTEN/NOTIFY`. Use session mode if your ORM requires prepared statements and pool size is manageable.
---
## Anti-Patterns
### Anti-Pattern: Premature Denormalization
**Novice**: "Joins are slow, so I'll copy data into the main table to avoid them."
**Expert**: Joins are fast when indexes exist. Copying data creates update anomalies — the same fact stored in two places that can diverge. The correct sequence is: normalize first, measure query time under real load, identify the specific join bottleneck with EXPLAIN ANALYZE, then consider a materialized view or a single cached denormalized column as a last resort.
**Detection**: Look for columns like `user_name` on an `orders` table alongside a `user_id` FK to a `users` table. If `users.name` can change, `orders.user_name` will drift.
**LLM mistake**: Training data contains many tutorials that denormalize early as a "performance optimization." These predate widespread index-aware ORMs and assume manual query writing.
---
### Anti-Pattern: Missing Indexes on Foreign Keys
**Novice**: "The database will figure out how to join — I just need the FK constraint."
**Expert**: A foreign key constraint enforces referential integrity but creates no index. A `JOIN orders ON orders.user_id = users.id` with no index on `orders.user_id` causes a full sequential scan of the `orders` table for every user. On a table with millions of rows this is catastrophic.
**Detection**:
```sql
-- Find FK columns with no index (PostgreSQL)
SELECT
tc.table_name,
kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
LEFT JOIN pg_indexes pi
ON pi.tablename = tc.table_name
AND pi.indexdef LIKE '%' || kcu.column_name || '%'
WHERE tc.constraint_type = 'FOREIGN KEY'
AND pi.indexname IS NULL;
```
**Fix**: Add an index on every FK column, always with `CONCURRENTLY` on a live table.
---
### Anti-Pattern: SELECT * in Production Queries
**Novice**: "SELECT * is fine — the database only fetches what I need."
**Expert**: `SELECT *` fetches all columns including large TEXT, JSONB, and BYTEA columns you don't use. It prevents index-only scans (the query must hit the heap even if an index covers the query). It breaks when columns are added or reordered in ORMs that rely on positional column binding. Always name columns explicitly.
**Detection**: Search application code for `SELECT *` in any query that runs in a hot path. In ORMs, check if `.findAll()` or equivalent selects all columns by default and add explicit field selection.
---
## References
- `references/indexing-strategies.md` — Consult when choosing between B-tree, GIN, GiST, hash, partial, and covering indexes; includes index-only scan prerequisites and multi-column index ordering rules.
- `references/migration-patterns.md` — Consult when planning zero-downtime migrations; covers expand-contract pattern, lock timeout settings, backfill chunking, and rollback strategies.
© curiositech · MIT · curiositech/some_claude_skills