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