database-design

name	database-design
description	Use this skill when designing database schemas, choosing between SQL and NoSQL, optimizing queries, designing indexes, modeling relationships, working with vector databases, or planning data migrations. Trigger on keywords: database, schema, SQL, NoSQL, index, query optimization, data model, migration, ORM, PostgreSQL, MongoDB, Redis, vector database, N+1.

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

Relationship	Implementation
One-to-One	Foreign key on either table + UNIQUE constraint
One-to-Many	Foreign key on the "many" side
Many-to-Many	Junction/pivot table with two foreign keys

---

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

SQL	NoSQL
ACID transactions	High write throughput
Complex queries, joins	Flexible/variable schema
Data integrity critical	Horizontal scale priority
Well-defined schema	Unstructured or nested data
PostgreSQL, MySQL	MongoDB, DynamoDB, Cassandra

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:

DB	Best For
pgvector	Existing PostgreSQL stack
Pinecone	Managed, production-scale
Weaviate	Multi-modal, hybrid search
Chroma	Local dev and prototyping

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

Use Case	Pattern
Session storage	Key: session:{id}, TTL
Rate limiting	Increment counter with TTL
Caching	Key: cache:{resource}:{id}, TTL
Pub/Sub	Real-time events between services
Job queue	List with LPUSH/BRPOP
Leaderboard	Sorted Set (ZADD/ZRANGE)