| name | Database Schema Design |
| description | Design normalized database schemas with tables, relationships, indexes, and migrations |
| version | 1.0 |
| tags | ["engineering","backend","design"] |
| complexity | intermediate |
| requires_tools | [] |
Process
- Identify entities — Map domain objects to tables (users, orders, products, etc.)
- Define relationships — One-to-one, one-to-many, many-to-many
- Normalize to 3NF — Eliminate redundancy, ensure each fact is stored once
- Choose primary keys — UUID vs auto-increment, composite keys when needed
- Design indexes — Based on query patterns (WHERE, JOIN, ORDER BY)
- Plan constraints — NOT NULL, UNIQUE, FOREIGN KEY, CHECK
- Write migration — CREATE TABLE statements, ALTER TABLE for existing schemas
Output Format
## Schema Design: [Feature/Module Name]
### Entity-Relationship Summary
- User (1) → (N) Order
- Order (N) ← → (N) Product (via order_items)
### Tables
#### users
| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| id | UUID | PK | |
| email | VARCHAR(255) | UNIQUE, NOT NULL | Login identifier |
| created_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | |
#### orders
| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| id | UUID | PK | |
| user_id | UUID | FK → users.id, NOT NULL | |
### Indexes
- users(email) — unique lookup
- orders(user_id, created_at) — user's recent orders
### Migration SQL
[CREATE TABLE / ALTER TABLE statements]
Guidelines
- Prefer UUIDs for primary keys in distributed systems, auto-increment for simple apps
- Always add
created_at and updated_at timestamps
- Use soft deletes (
deleted_at) for data that might need recovery
- Index foreign keys — they're used in JOINs
- Don't over-normalize — denormalize for read-heavy queries when justified
- Include rollback SQL in migrations (DROP TABLE, ALTER TABLE DROP COLUMN)
- Consider data volume and growth rate when designing indexes
