# Database Schema Design

> Design normalized, performant, evolvable database schemas.

## When to Use
- New project database design
- Adding tables/columns to existing schema
- "Design a schema for X"

## Approach
1. **Start with entities and relationships** — what are the nouns? how do they relate?
2. **Normalize to 3NF first** — then denormalize strategically for performance
3. **Every table needs:** id (PK), created_at, updated_at at minimum
4. **Name consistently** — snake_case, plural table names, singular column names
5. **Index foreign keys** — every FK column gets an index
6. **Add constraints** — NOT NULL, UNIQUE, CHECK where data integrity matters
7. **Plan for soft delete** — add `active` or `deleted_at` column, don't hard delete by default
8. **Think about migrations** — can this schema evolve without downtime?

## Schema Template
```sql
CREATE TABLE resources (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','archived','deleted')),
    owner_id INTEGER REFERENCES users(id),
    metadata TEXT DEFAULT '{}',
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX idx_resources_owner ON resources(owner_id);
CREATE INDEX idx_resources_status ON resources(status);
```

## Common Mistakes
- No indexes on columns used in WHERE clauses
- Storing JSON blobs instead of proper columns (fine for metadata, bad for queryable data)
- Not adding created_at/updated_at from the start
- Using FLOAT for money (use INTEGER cents or DECIMAL)
