SQL vs NoSQL Databases

Choosing the right database is crucial for system performance and scalability.

SQL Databases (Relational)

-- Structured data with relationships
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW()
);

-- ACID transactions
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

NoSQL Database Types

// Document Store (MongoDB)
interface UserDocument {
  _id: ObjectId;
  email: string;
  name: string;
  orders: Array<{
    orderId: string;
    total: number;
    items: Array<{ productId: string; quantity: number }>;
  }>;
  preferences: {
    theme: string;
    notifications: boolean;
  };
}

// Key-Value Store (Redis)
interface KeyValueOperations {
  set(key: string, value: string, ttl?: number): Promise;
  get(key: string): Promise;
  incr(key: string): Promise;
  hset(key: string, field: string, value: string): Promise;
}

// Wide-Column Store (Cassandra)
// Optimized for write-heavy workloads
const cassandraSchema = `
  CREATE TABLE user_activity (
    user_id UUID,
    activity_date DATE,
    activity_time TIMESTAMP,
    activity_type TEXT,
    metadata MAP,
    PRIMARY KEY ((user_id, activity_date), activity_time)
  ) WITH CLUSTERING ORDER BY (activity_time DESC);
`;

// Graph Database (Neo4j)
const neo4jQuery = `
  MATCH (user:User {id: $userId})-[:FOLLOWS]->(friend:User)
  -[:POSTED]->(post:Post)
  WHERE post.createdAt > $since
  RETURN friend, post
  ORDER BY post.createdAt DESC
  LIMIT 20
`;

Decision Matrix

interface DatabaseSelection {
  useCase: string;
  recommendation: 'SQL' | 'NoSQL';
  type?: string;
  reason: string;
}

const decisionMatrix: DatabaseSelection[] = [
  {
    useCase: 'Financial transactions',
    recommendation: 'SQL',
    reason: 'ACID compliance required'
  },
  {
    useCase: 'User sessions',
    recommendation: 'NoSQL',
    type: 'Key-Value (Redis)',
    reason: 'Fast reads, simple data structure'
  },
  {
    useCase: 'Product catalog',
    recommendation: 'NoSQL',
    type: 'Document (MongoDB)',
    reason: 'Flexible schema, nested data'
  },
  {
    useCase: 'Time-series metrics',
    recommendation: 'NoSQL',
    type: 'Wide-Column (Cassandra)',
    reason: 'High write throughput, time-based queries'
  },
  {
    useCase: 'Social connections',
    recommendation: 'NoSQL',
    type: 'Graph (Neo4j)',
    reason: 'Relationship traversal'
  },
  {
    useCase: 'E-commerce orders',
    recommendation: 'SQL',
    reason: 'Complex joins, transactions'
  }
];

Database Selection

Database Selection

SQL vs NoSQL Databases

SQL Databases (Relational)

NoSQL Database Types

Decision Matrix