# Database Design

> Schema design, normalization, indexing, migrations, and query optimization for SQL and NoSQL

- Skill: `cosmicstack-labs/database-design` (Agent Skill)
- Install (CLI): `npx skillmds@latest add cosmicstack-labs/database-design`
- Raw SKILL.md: https://api.skillmd.com/api/skills/cosmicstack-labs/database-design/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: cosmicstack-labs (https://skillmd.com/u/cosmicstack-labs)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/cosmicstack-labs/database-design

---


# Database Design

Design efficient, scalable database schemas.

## Normalization

| Normal Form | Rule | Violation Example |
|-------------|------|-------------------|
| 1NF | Atomic columns, no repeating groups | `phone_numbers: "555-0100,555-0200"` |
| 2NF | 1NF + all non-key cols depend on full PK | Orders table with product details |
| 3NF | 2NF + no transitive dependencies | Employee with department_name (dept info in dept table) |

**When to denormalize**: Read-heavy workloads, reporting, caching layers.

## Indexing Strategy

### Index Types
| Index | Use Case | Tradeoff |
|-------|----------|----------|
| B-Tree | General purpose, equality + range | Default, usually best |
| Hash | Equality only | Fast lookups, no sorting |
| GIN | Array/JSON | Slightly slower writes |
| BRIN | Huge sorted tables | Very small size |

### Rules
- Index columns used in WHERE, JOIN, ORDER BY
- Index foreign keys
- Covering indexes for frequent queries
- Don't over-index (slows writes, uses space)
- Drop unused indexes (use pg_stat_user_indexes)

## Migrations
- One file per change, sequential naming
- Always reversible (up/down pair)
- Test on staging before production
- Run in transactions where possible
- Avoid locking tables on large tables (use pt-online-schema-change)

## Query Optimization
- EXPLAIN ANALYZE every slow query
- Look for: Seq scans, nested loops, temp files
- Common fixes: missing index, bad join order, OR conditions
- N+1 problem: batch find / includes / joins
- Limit offsets for pagination (use cursor-based)