database-fundamentals

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