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

| 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) |

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: jamestorrevillas
• Source: jamestorrevillas/dev-skills
• License: MIT

Install and usage instructions live in the source repository linked above.