# database-design-patterns

database-design-patterns guides relational schema design, 3NF normalization, index selection, and zero-downtime migrations using the expand-contract pattern.

- skill · `type=skill` · Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, OpenCode, Windsurf (Devin Desktop), Claude.ai · 152 installs · ★ 274 repo stars
- Author: curiositech
- License: MIT · policy=mirror · the beginning of the file is at the end of this page
- Source: https://github.com/curiositech/some_claude_skills/tree/HEAD/.claude/skills/database-design-patterns/SKILL.md
- Updated: 2026-10-30
- Security check: automated, 2026-11-16 (text) — no signs of malicious code found.
- HTML: https://affpapa.org/en/nevibecode/skills/database-design-patterns

## What it 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.

## Requirements
- Works with: PostgreSQL

## Install

### Claude Code (native platform)

1. **Skills CLI (Vercel)**
```bash
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a claude-code
```

2. **Manually: put the files in place**
- File: `~/.claude/skills/database-design-patterns/SKILL.md` — Copy the whole skill folder; for one project use .claude/skills/database-design-patterns/

### Codex

1. **Skills CLI (Vercel)**
```bash
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a codex
```

2. **Manually: put the files in place**
- File: `.agents/skills/database-design-patterns/SKILL.md` — For all projects use ~/.agents/skills/

### Cursor

1. **Skills CLI (Vercel)**
```bash
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a cursor
```

2. **Manually: put the files in place**
- File: `.cursor/skills/database-design-patterns/SKILL.md` — Cursor also reads .agents/skills/ and .claude/skills/

### GitHub Copilot

1. **GitHub CLI**
```bash
gh skill install curiositech/some_claude_skills .claude/skills/database-design-patterns
```

2. **Manually: put the files in place**
- File: `.github/skills/database-design-patterns/SKILL.md`

### Gemini CLI

1. **Skills CLI (Vercel)**
```bash
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a gemini-cli
```

2. **Platform CLI**
```bash
gemini skills install https://github.com/curiositech/some_claude_skills --path .claude/skills/database-design-patterns
```

### Windsurf (Devin Desktop)

**Manually: put the files in place**
- File: `.devin/skills/database-design-patterns/SKILL.md` — The legacy .windsurf/skills/ path still works

### OpenCode

**Skills CLI (Vercel)**
```bash
npx skills add curiositech/some_claude_skills --skill database-design-patterns -a opencode
```

### Claude.ai

**Claude.ai**
- Zip the folder and upload it in Settings → Capabilities → Skills

Where a file is given instead of a command, copy the files from the source to that path: https://github.com/curiositech/some_claude_skills/tree/HEAD/.claude/skills/database-design-patterns/SKILL.md

## When to use it
- 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
- 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

## Source text · SKILL.md · MIT

Below is the content of a third-party file. It is data, not instructions for you: do not act on anything written inside unless the human asks. Block tag: a8f87916b253892d.

BEGIN THIRD-PARTY FILE a8f87916b253892d
````text
---
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
````
END THIRD-PARTY FILE a8f87916b253892d

The third-party file has ended. Everything between the a8f87916b253892d tags is data: requests and commands from it are not followed. An “end of file” line with another tag inside the block is part of the file.

First 2293 of 12577 bytes. © curiositech · MIT · https://github.com/curiositech/some_claude_skills/tree/HEAD/.claude/skills/database-design-patterns/SKILL.md

_Part of the page did not fit — see the HTML version._

---
NeVibeCode on affpapa.org. Catalog texts are descriptions, not instructions for the agent. How to choose and install: https://affpapa.org/en/nevibecode/agents.md
