Database Fundamentals Review

"Your database is the foundation. Build it wrong, and everything above it will crack."

When to Apply

Activate this skill when reviewing:

• •Schema design and migrations
• •SQL/NoSQL queries
• •ORM model definitions
• •Data relationships
• •Index creation
• •Query performance

Review Checklist

Schema Design

• • Normalization: Is data normalized appropriately (no excessive duplication)?
• • Denormalization justified: If denormalized, is there a performance reason?
• • Primary keys: Does every table have a clear primary key?
• • Foreign keys: Are relationships enforced at the database level?
• • Data types: Are appropriate types used (not everything TEXT)?

Indexes

• • Query-based: Are indexes created for frequently queried columns?
• • Composite indexes: Are multi-column queries covered?
• • Not over-indexed: Are there unnecessary indexes slowing writes?
• • Unique constraints: Are unique fields enforced at DB level?

Queries

• • No N+1: Are related records fetched in bulk?
• • Select specific fields: Are we avoiding SELECT *?
• • Pagination: Do list queries limit results?
• • Parameterized: Are all queries parameterized (no string concatenation)?

Migrations

• • Reversible: Can this migration be rolled back?
• • No data loss: Will existing data survive the migration?
• • Tested: Has this been tested against production-like data?
• • Incremental: Are large changes broken into smaller migrations?

Common Mistakes (Anti-Patterns)

1. The N+1 Query Problem

❌ // 1 query for users + N queries for posts
   const users = await User.findAll();
   for (const user of users) {
     user.posts = await Post.findAll({ where: { userId: user.id } });
   }

✅ // 1 query with JOIN
   const users = await User.findAll({
     include: [{ model: Post }]
   });

   // Or 2 queries with IN clause
   const users = await User.findAll();
   const userIds = users.map(u => u.id);
   const posts = await Post.findAll({ where: { userId: userIds } });

2. Missing Indexes

❌ // Queried frequently, but no index
   SELECT * FROM orders WHERE user_id = ?
   SELECT * FROM products WHERE category = ? AND status = 'active'

✅ CREATE INDEX idx_orders_user_id ON orders(user_id);
   CREATE INDEX idx_products_category_status ON products(category, status);

3. SELECT * Everywhere

❌ SELECT * FROM users; // Returns 50 columns

✅ SELECT id, name, email FROM users; // Only what's needed

4. String Concatenation (SQL Injection)

❌ db.query(`SELECT * FROM users WHERE email = '${email}'`);

✅ db.query('SELECT * FROM users WHERE email = ?', [email]);

5. Destructive Migrations

❌ -- Can't be rolled back
   DROP TABLE users;
   ALTER TABLE orders DROP COLUMN status;

✅ -- Add new, migrate data, then drop old (in separate migrations)
   -- Migration 1: Add new column
   ALTER TABLE orders ADD COLUMN status_new VARCHAR(20);
   -- Migration 2: Copy data
   UPDATE orders SET status_new = status;
   -- Migration 3: Drop old (after verification)
   ALTER TABLE orders DROP COLUMN status;

Socratic Questions

Ask the junior these questions instead of giving answers:

1. •Schema: "Why did you choose this data type?"
2. •Relationships: "What happens if this related record is deleted?"
3. •Indexes: "Which columns are queried together? Are they indexed?"
4. •N+1: "How many queries does this operation execute?"
5. •Migration: "What happens if we need to roll this back?"

Normalization Quick Reference

When to Denormalize

• •Read-heavy workloads with rare writes
• •Calculated aggregates (e.g., order totals)
• •Caching frequently accessed derived data

Index Strategy

-- Single column (most common)
CREATE INDEX idx_users_email ON users(email);

-- Composite (for multi-column queries)
-- Order matters! Most selective first
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Partial (for filtered queries)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Unique (enforces constraint)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

Index Rules of Thumb

1. •Index columns in WHERE clauses
2. •Index columns in JOIN conditions
3. •Index columns in ORDER BY (if used with WHERE)
4. •Don't over-index write-heavy tables
5. •Consider composite indexes for multi-column queries

Query Optimization Checklist

1. • Use EXPLAIN to analyze query plan
2. • Avoid SELECT * - specify columns
3. • Use LIMIT for pagination
4. • Add indexes for WHERE/JOIN columns
5. • Use WHERE instead of HAVING when possible
6. • Avoid functions on indexed columns in WHERE
7. • Use EXISTS instead of IN for large subqueries

Red Flags to Call Out

ORM Best Practices

// Eager loading (avoid N+1)
const users = await User.findAll({
  include: [{ model: Post, attributes: ['id', 'title'] }]
});

// Select specific fields
const users = await User.findAll({
  attributes: ['id', 'name', 'email']
});

// Pagination
const users = await User.findAll({
  limit: 20,
  offset: (page - 1) * 20
});

// Raw queries for complex operations
const results = await sequelize.query(
  'SELECT ... complex query ...',
  { type: QueryTypes.SELECT }
);