database-design

Database Design

This skill provides comprehensive guidance on database design, from relational data modeling to choosing between SQL and NoSQL databases.

When to Use This Skill

Use this skill when:

• Designing database schemas
• Choosing between SQL and NoSQL
• Modeling relationships between entities
• Optimizing database queries
• Planning database migrations
• Deciding on database architecture

Relational Data Modeling

The Master Trick

Underline all NOUNS and VERBS in requirements:

• Nouns → Entities or Attributes
• Verbs → Status changes or Relationships

Relationship Types

One-to-Many

USER (1) ←————→ (Many) POSTS
USER (1) ←————→ (Many) TWEETS

Solution: Add the "one" side's ID as foreign key in the "many" side

CREATE TABLE posts (
    id INT PRIMARY KEY,
    user_id INT REFERENCES users(id),  -- Foreign Key
    content TEXT,
    created_at TIMESTAMP
);

Many-to-Many

USER (Many) ←————→ (Many) SKILLS

Solution: Create a mapping/junction table

CREATE TABLE user_skills (
    id INT PRIMARY KEY,
    user_id INT REFERENCES users(id),
    skill_id INT REFERENCES skills(id)
);

LinkedIn Schema Example

┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│    USERS     │     │ USER_SKILLS  │     │   SKILLS     │
├──────────────┤     ├──────────────┤     ├──────────────┤
│ id (PK)      │────▶│ id (PK)      │◀────│ id (PK)      │
│ name         │     │ user_id (FK) │     │ name         │
│ email        │     │ skill_id (FK)│     └──────────────┘
│ profile_pic  │     └──────────────┘
└──────────────┘

Critical Rule: Never Store Lists in a Single Column

-- ❌ BAD: This causes O(n) scan operations
skills: "Java, JavaScript, Python"

-- Query becomes:
SELECT * FROM users WHERE skills LIKE '%Java%'  -- SCAN!

-- ✅ GOOD: Use junction table
CREATE TABLE user_skills (
    user_id INT REFERENCES users(id),
    skill_id INT REFERENCES skills(id),
    PRIMARY KEY (user_id, skill_id)
);

When to Split Tables

Even if columns are similar (Education vs Company), split when:

• Different attributes needed later (CGPA vs Salary)
• Avoid NULL columns
• Keep database logic out of application

SQL vs NoSQL Databases

Relational Databases (SQL)

Examples: PostgreSQL, MySQL, Oracle, SQLite

Structure:

• Tables with columns and rows
• Like spreadsheets
• Strict schema

Advantages:

ACID Explained:

A - Atomic      → All or nothing (entire transaction succeeds/fails)
C - Consistent  → Valid state to valid state
I - Isolated    → Concurrent transactions don't interfere
D - Durable     → Data persists even after system failure

Non-Relational Databases (NoSQL)

Types of NoSQL:

Document Store Example (MongoDB):

{
  "user_id": 123,
  "name": "John",
  "orders": [
    {"product": "Laptop", "price": 999},
    {"product": "Mouse", "price": 29}
  ],
  "addresses": [
    {"type": "home", "city": "NYC"},
    {"type": "work", "city": "Boston"}
  ]
}

Decision Matrix

Database Selection Guide

Choose SQL When:

• ✅ Structured data with clear relationships
• ✅ Need ACID transactions (financial, e-commerce)
• ✅ Complex queries with JOINs
• ✅ Strong consistency required
• ✅ Examples: User accounts, orders, transactions, relational data

Choose NoSQL When:

• ✅ Unstructured or semi-structured data
• ✅ High write throughput needed
• ✅ Horizontal scaling required
• ✅ Flexible schema needed
• ✅ Examples: User sessions, logs, real-time analytics, content management

Database Options

PostgreSQL (Recommended for most cases)

// Supabase (PostgreSQL with built-in auth)
import { createClient } from '@supabase/supabase-js';
const supabase = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_KEY!);

// Neon (Serverless PostgreSQL)
import { neon } from '@neondatabase/serverless';
const sql = neon(process.env.DATABASE_URL!);

// Railway/Standard PostgreSQL
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

MySQL

// PlanetScale (Serverless MySQL)
import { connect } from '@planetscale/database';
const conn = connect({ url: process.env.DATABASE_URL });

// Standard MySQL
import mysql from 'mysql2/promise';
const connection = await mysql.createConnection(process.env.DATABASE_URL);

MongoDB

// MongoDB Atlas (Cloud)
import { MongoClient } from 'mongodb';
const client = new MongoClient(process.env.MONGODB_URI!);

// Mongoose ODM
import mongoose from 'mongoose';
await mongoose.connect(process.env.MONGODB_URI!);

Redis (Caching/Sessions)

import { createClient } from 'redis';
const redis = createClient({ url: process.env.REDIS_URL });
await redis.connect();

Query Optimization

Parameterized Queries (Prevent SQL Injection)

// ✅ SAFE: Parameterized query
const result = await sql`
  SELECT * FROM users 
  WHERE email = ${userEmail} AND status = ${status}
`;

// ❌ UNSAFE: String concatenation
const result = await sql.unsafe(
  `SELECT * FROM users WHERE email = '${userEmail}'`
);

Indexing Strategy

• Index frequently queried columns
• Index foreign keys
• Use composite indexes for multi-column queries
• Monitor query performance and adjust indexes

Transaction Management

// PostgreSQL transaction example
await sql.begin(async (sql) => {
  await sql`INSERT INTO orders (user_id, total) VALUES (${userId}, ${total})`;
  await sql`UPDATE users SET balance = balance - ${total} WHERE id = ${userId}`;
  // Both succeed or both fail (ACID)
});

Connection Management

Serverless Environments (Vercel, Netlify)

• ✅ Use connection pooling libraries (@neondatabase/serverless, @planetscale/database)
• ✅ Implement connection reuse across function invocations
• ✅ Set appropriate connection timeouts
• ✅ Use HTTP-based drivers when available (Neon, PlanetScale)

Traditional Server Environments

• ✅ Use connection pools (pg.Pool, mysql2 pool)
• ✅ Set max connections based on server capacity
• ✅ Implement connection health checks
• ✅ Graceful shutdown on server termination

Error Handling

try {
  const result = await sql`SELECT * FROM users WHERE id = ${id}`;
  return result;
} catch (error) {
  console.error('Database query failed:', {
    query: 'SELECT users',
    userId: id,
    error: error.message,
    timestamp: new Date().toISOString()
  });
  
  // Retry logic for transient failures
  if (isTransientError(error)) {
    return retryQuery(() => sql`SELECT * FROM users WHERE id = ${id}`);
  }
  
  throw new Error('Unable to fetch user data. Please try again.');
}

Reference Material

For detailed examples and explanations, refer to:

• references/SYSTEM_DESIGN_MASTER_GUIDE.md - Part 2: Database Design section