Database Design Principles

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)