database-schema-design

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

1. Identify entities — Map domain objects to tables (users, orders, products, etc.)
2. Define relationships — One-to-one, one-to-many, many-to-many
3. Normalize to 3NF — Eliminate redundancy, ensure each fact is stored once
4. Choose primary keys — UUID vs auto-increment, composite keys when needed
5. Design indexes — Based on query patterns (WHERE, JOIN, ORDER BY)
6. Plan constraints — NOT NULL, UNIQUE, FOREIGN KEY, CHECK
7. 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