Database Design

Database Design

Schema Design Principles

• Normalize first — eliminate redundancy, then denormalize only for proven performance needs
• Name clearly — user_id not uid, created_at not ts
• Every table needs — a primary key, created_at, updated_at
• Soft deletes — add deleted_at instead of hard deleting rows you might need to recover

Relationship Patterns

Indexing Strategy

Rule: Index columns you filter, sort, or join on frequently.

-- Index for common query patterns
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

-- Partial index for active records only
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

When NOT to Over-Index

• Every index slows down writes
• Index columns with low cardinality (boolean, status with 3 values) only if queries are very frequent
• Monitor query performance, add indexes based on actual slow queries

Query Optimization

N+1 Query Problem

// BAD — N+1: 1 query for posts + N queries for each author
const posts = await Post.findAll()
for (const post of posts) {
  const author = await User.findById(post.userId) // N queries!
}

// GOOD — 2 queries total using JOIN or eager loading
const posts = await Post.findAll({ include: [{ model: User }] })

Pagination

-- Offset pagination (simple but slow for large offsets)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 100;

-- Cursor pagination (fast for large datasets)
SELECT * FROM posts WHERE created_at < :cursor ORDER BY created_at DESC LIMIT 20;

Use cursor pagination for large tables or infinite scroll.

SQL vs NoSQL Cheatsheet

Hybrid: Use SQL as the source of truth, Redis for caching, Elasticsearch for search.

Vector Databases (AI Use Cases)

For semantic search, RAG, and embeddings:

Indexing Strategies

• HNSW — best recall, higher memory usage
• IVFFlat — lower memory, slightly lower recall
• Product Quantization — memory-efficient for very large datasets

Migration Best Practices

• Never drop columns in the same deploy as removing code that uses them — separate deploys
• Backward compatible first — add new column → deploy new code → remove old column
• Always test migrations on a copy of production data before running in prod
• Zero-downtime migrations — use NOT NULL DEFAULT carefully, add constraints after backfill

Redis Use Cases