# Database Design Principles

> Apply database design patterns when creating schemas, writing migrations, designing queries, managing indexes, or working with transaction boundaries. Covers normalization, query optimization, connection pooling, and migration safety.

- Skill: `irahardianto/database-design-principles-3` (Agent Skill)
- Install (CLI): `npx skillmds@latest add irahardianto/database-design-principles-3`
- Raw SKILL.md: https://api.skillmd.com/api/skills/irahardianto/database-design-principles-3/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: irahardianto (https://skillmd.com/u/irahardianto)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/irahardianto/database-design-principles-3

---


## Database Design Principles

### Schema Design

**Normalization:**
- Start with 3NF (Third Normal Form) and denormalize only when measured performance requires it
- Each table should represent a single entity
- Avoid storing derived/calculated data unless caching is explicitly needed

**Naming Conventions:**
- Tables: plural, snake_case (e.g., `users`, `task_assignments`)
- Columns: singular, snake_case (e.g., `created_at`, `user_id`)
- Foreign keys: `{referenced_table_singular}_id` (e.g., `user_id`, `task_id`)
- Indexes: `idx_{table}_{columns}` (e.g., `idx_users_email`)
- Constraints: `{type}_{table}_{columns}` (e.g., `uq_users_email`, `fk_tasks_user_id`)

**Required columns for all tables:**
- `id` — primary key (UUID preferred, auto-increment acceptable)
- `created_at` — timestamp, set on insert, never updated
- `updated_at` — timestamp, updated on every modification

### Migrations

**Safety Rules:**
- **Never drop columns in production** without a deprecation period
- **Never rename columns directly** — add new, migrate data, drop old
- **Always make migrations reversible** — include both up and down
- **Test migrations on a copy of production data** before applying

**Migration Strategy:**
1. Additive changes first (add column, add table)
2. Backfill data
3. Update application code
4. Remove old columns/tables in a future migration

### Queries

**Performance:**
- **Always use parameterized queries** (never string concatenation)
- **Index columns** used in WHERE, JOIN, and ORDER BY clauses
- **Avoid SELECT \*** — specify only needed columns
- **Watch for N+1 queries** — use JOINs or batch loading
- **Set query timeouts** to prevent long-running queries from blocking

**Transactions:**
- Use transactions for operations that modify multiple rows/tables
- Keep transactions as short as possible
- Handle deadlocks with retry logic
- Never hold transactions open during user interaction or external API calls

### Database Design Checklist

- [ ] Schema follows naming conventions?
- [ ] All tables have `id`, `created_at`, `updated_at`?
- [ ] Foreign keys have proper constraints and indexes?
- [ ] Queries are parameterized (no SQL injection)?
- [ ] Indexes exist for frequent query patterns?
- [ ] Migrations are reversible?
- [ ] Transactions are short and focused?
- [ ] N+1 queries avoided?

### Related Principles
- Security Principles @.claude/rules/security-principles.md (SQL injection prevention)
- Performance Optimization Principles @.claude/skills/performance-optimization-principles/SKILL.md (query performance)
- Error Handling Principles @.claude/rules/error-handling-principles.md (transaction error handling)