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

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

| Form | Rule | Example Issue | |------|------|---------------| | 1NF | No repeating groups | tags: "js,react,node" should be separate table | | 2NF | No partial dependencies | Order item price duplicated from products | | 3NF | No transitive dependencies | Storing city AND zip code (zip determines city) |

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

| Flag | Question to Ask | |------|-----------------| | Query in a loop | "Can we fetch all this data in one query?" | | No pagination | "What if there are 1 million records?" | | SELECT * | "Do we need all 50 columns?" | | String in query | "Is this protected against SQL injection?" | | No indexes on foreign keys | "How fast are JOINs on this table?" | | DROP TABLE in migration | "How do we roll this back?" | | TEXT for everything | "Should this be an INT or DATE instead?" | | No foreign key constraints | "What prevents orphaned records?" |

---

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