database-design

When to Use

• Designing a new database schema
• Reviewing or refactoring existing schemas
• Planning migrations or data model changes
• Choosing data types, indexes, or constraints
• Defining naming conventions for tables, columns, or relationships

Instructions

1. Understand the Domain First

Before writing any DDL, map the business entities and their relationships. Use the conceptual → logical → physical flow:

1. Conceptual: Entities, relationships, business rules (ER diagram)
2. Logical: Attributes, keys, normal forms (independent of technology)
3. Physical: Data types, indexes, partitions, engine-specific features

2. Naming Conventions

3. Normalization Quick Reference

• 1NF: Atomic columns, no repeating groups
• 2NF: 1NF + all non-key columns depend on the whole PK
• 3NF: 2NF + no transitive dependencies (non-key → non-key)
• Denormalize only when query performance demands it, and document why

4. Data Type Selection

• Use the smallest type that fits the data range
• Prefer standardized types: TIMESTAMPTZ over TIMESTAMP, UUID or IDENTITY over natural keys
• Avoid TEXT/VARCHAR(255) as a default — size columns appropriately
• Use DECIMAL for money, not FLOAT
• Dialect matters — see references/postgresql.md for pg-specific types (arrays, JSONB, intervals), references/mysql.md / references/mariadb.md for engine quirks, and references/sqlite.md for type affinity rules

5. Indexing Strategy

1. PKs and FKs — always index FK columns
2. Query-driven — index columns in WHERE, JOIN, ORDER BY, not arbitrary columns
3. Composite indexes — leftmost prefix rule applies; order by selectivity
4. Covering indexes — include columns to avoid table heap lookups
5. Avoid over-indexing — every index slows writes

6. Constraints Over Code

Prefer database constraints over application-level validation:

CHECK (price > 0)           -- domain integrity
NOT NULL                    -- required fields
UNIQUE                      -- uniqueness
FOREIGN KEY ... ON DELETE   -- referential integrity

7. Migration Patterns

• Every migration must be reversible (have a down/rollback)
• One migration = one logical change (add column, create table, etc.)
• Never modify a migration that has already been applied to production
• Use timestamp-based or sequential naming: 20250531_create_users.sql

8. Query Design

• Avoid SELECT * — always name columns
• Use EXPLAIN ANALYZE before optimizing
• Prefer set-based operations over row-by-row (cursors)
• Use LIMIT / OFFSET for pagination, but prefer keyset pagination for large datasets
• See references/query-optimization.md for execution plans, join strategies, CTEs vs subqueries, N+1, and dialect-specific tips

9. Decision Table

Commands

# Generate migration
migrate create -ext sql -dir migrations create_users

# Run migrations
migrate -database "$DATABASE_URL" up

# Rollback last migration
migrate -database "$DATABASE_URL" down 1

# Review dry-run
migrate -database "$DATABASE_URL" up 1 --dry-run

Dialect References

Choose Your Database

Anti-Patterns

• EAV (Entity-Attribute-Value) — almost never the right choice; prefer JSONB or separate tables
• Polymorphic associations — breaks referential integrity; use concrete join tables
• Natural keys as PK — they change; use surrogate keys
• One table for everything — violates 1NF; split by entity
• Indexes on every column — write performance degrades