# Database Design Principles

> Database design: schemas, migrations, queries, indexes, transactions. Normalization, query optimization, migration safety.

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

---


## Database Design Principles

### Schema

**Normalization:** start 3NF, denormalize only with measured perf need. One entity per table. No derived data unless explicit cache.

**Naming:** tables=plural snake_case (`users`, `task_assignments`). Columns=singular snake_case. FKs=`{table_singular}_id`. Indexes=`idx_{table}_{cols}`. Constraints=`{type}_{table}_{cols}`.

**Required columns:** `id` (bigint IDENTITY for single-DB, UUIDv7 for distributed — avoid random UUIDv4), `created_at` (timestamptz, immutable), `updated_at` (timestamptz, auto-update).

**Data type selection:** `timestamptz` over `timestamp`. `text` over `varchar(n)` unless constraint needed. `numeric` over `float` for money. `bigint` over `int` for IDs.

### Migrations

Safety: never drop prod columns without deprecation. Never rename directly (add new → migrate → drop old). Always reversible (up + down). Test on prod data copy.

Strategy: 1. additive → 2. backfill → 3. update code → 4. drop old in future migration.

### Queries

**Design-level:** ensure indexes cover query access patterns. Validate query plans during schema review. For SQL coding patterns (parameterized queries, SELECT hygiene, N+1 avoidance, timeouts), see `@.gemini/skills/sql-idioms/SKILL.md`.

**Transactions:** for multi-row/table ops. Choose appropriate isolation level. Never hold during user interaction or external calls. For locking patterns (deadlock prevention, advisory locks, SKIP LOCKED), see `@.gemini/skills/sql-idioms/SKILL.md` § Concurrency & Locking.

### Partitioning

Tables >100M rows or time-series data → range partition by time. Enables instant partition drops vs slow DELETE.

### Security at Schema Level

**Row-Level Security:** for multi-tenant data, enable RLS + FORCE to enforce database-level tenant isolation. Never rely solely on application-layer filtering. Index columns used in RLS policies.

**Least privilege:** create separate roles (readonly, writer) with specific table grants. Revoke default public access. Never use superuser for application queries.

### Connection Management

Size pool as `(CPU cores × 2) + disk spindles`. Set `idle_in_transaction_session_timeout = 30s`. In transaction-mode pooling, avoid named prepared statements (use unnamed or deallocate).

### Checklist
- [ ] Naming conventions followed
- [ ] All tables have id, created_at, updated_at
- [ ] FKs have constraints + indexes
- [ ] Indexes for freq query patterns
- [ ] Migrations reversible
- [ ] timestamptz used (not timestamp)
- [ ] PK strategy appropriate (IDENTITY vs UUIDv7)
- [ ] Partitioning evaluated for tables >100M rows
- [ ] RLS enabled for multi-tenant tables
- [ ] Least privilege roles configured
- [ ] Connection limits + idle timeouts set

### Related
- Security Principles GEMINI.md § Security Principles
- Performance @.gemini/skills/performance-optimization-principles/SKILL.md
- Error Handling GEMINI.md § Error Handling Principles