SQL vs NoSQL in System Design: How to Choose the Right Database

-- This example shows WHY atomicity matters in a financial system.
-- Without ACID, a crash between these two statements would lose money.

BEGIN TRANSACTION;

  -- Step 1: Deduct $500 from Alice's account
  UPDATE bank_accounts
  SET    balance = balance - 500
  WHERE  account_holder = 'Alice'
    AND  balance >= 500;   -- Guard: never allow negative balance

  -- Step 2: Credit $500 to Bob's account
  UPDATE bank_accounts
  SET    balance = balance + 500
  WHERE  account_holder = 'Bob';

  -- Only reaches here if BOTH updates succeeded
  -- If the server crashes between step 1 and step 2,
  -- the entire transaction is rolled back automatically.
COMMIT;

-- What you'd see if Alice has insufficient funds:
-- The guard condition (balance >= 500) causes 0 rows to be updated.
-- COMMIT still runs, but since no rows changed, the state is safe.
-- In production you'd check ROW_COUNT() and ROLLBACK explicitly.

-- Contrast: in a NoSQL system without transactions (e.g., early MongoDB),
-- you'd have to implement this logic in application code — which is
-- error-prone and not atomic by default.

// WHY a document model wins here:
// Each product category has COMPLETELY different attributes.
// A TV has resolution, refresh rate, panel type.
// A T-shirt has size, color, material, fit.
// Forcing these into a single SQL table would require 50+ nullable columns
// or a complex EAV (Entity-Attribute-Value) schema that's painful to query.

// MongoDB document for an electronics product
const televisionDocument = {
  _id: 'prod_tv_samsung_qn90b',
  category: 'electronics',
  name: 'Samsung QN90B 65-inch QLED',
  price_usd: 1299.99,
  stock_count: 47,
  // These attributes are SPECIFIC to televisions — no other category has them
  attributes: {
    screen_size_inches: 65,
    resolution: '4K',
    panel_type: 'QLED',
    refresh_rate_hz: 120,
    smart_tv: true,
    hdmi_ports: 4
  },
  tags: ['bestseller', 'holiday-sale'],
  created_at: new Date('2024-01-15')
};

// MongoDB document for an apparel product
// Notice: completely different attributes — no wasted null columns
const tshirtDocument = {
  _id: 'prod_shirt_nike_dri_fit_lg',
  category: 'apparel',
  name: 'Nike Dri-FIT Training T-Shirt',
  price_usd: 34.99,
  stock_count: 312,
  // These attributes only make sense for clothing
  attributes: {
    size: 'Large',
    color: 'Navy Blue',
    material: '100% Polyester',
    fit: 'Standard',
    gender: 'Men'
  },
  tags: ['sports', 'summer'],
  created_at: new Date('2024-02-03')
};

// Access pattern: always fetch by product ID — single document lookup.
// db.products.findOne({ _id: 'prod_tv_samsung_qn90b' })
// Returns the FULL product in one round trip. No joins needed.

// If you needed complex cross-category analytics:
// 'Top 10 best-selling products across all categories this month'
// — NOW a SQL warehouse (like Redshift or BigQuery) makes more sense.
// This is why large systems use BOTH databases for different jobs.

# WHY Cassandra wins for a user activity feed:
# Access pattern: 'Give me the last 50 events for user_id X'
# This is ALWAYS a lookup by user_id. Never a cross-user query.
# Cassandra's partition key ensures all of a user's events
# live on the SAME node — one network hop, sub-millisecond reads.

# Cassandra table definition (CQL — Cassandra Query Language)
create_table_statement: |
  CREATE TABLE user_activity_feed (
    user_id     UUID,          -- Partition key: all rows for one user on one node
    event_time  TIMESTAMP,     -- Clustering key: rows sorted by time automatically
    event_type  TEXT,          -- 'video_watched', 'post_liked', 'comment_added'
    content_id  UUID,          -- The piece of content this event relates to
    metadata    MAP<TEXT,TEXT>, -- Flexible key-value bag — no schema migration needed
    PRIMARY KEY (user_id, event_time)
  ) WITH CLUSTERING ORDER BY (event_time DESC);  -- Latest events first

# Reading the last 50 events for a user:
read_query: |
  SELECT event_type, event_time, content_id, metadata
  FROM   user_activity_feed
  WHERE  user_id = 9a8b7c6d-...
  LIMIT  50;
  -- This hits ONE partition on ONE node.
  -- At 1 million users, this query is EQUALLY fast as at 1000 users.
  -- That's horizontal scaling working as intended.

# What you CAN'T do efficiently in Cassandra:
bad_query_example: |
  -- This would require scanning ALL partitions on ALL nodes:
  SELECT * FROM user_activity_feed
  WHERE  event_type = 'video_watched'
    AND  event_time > '2024-01-01';
  -- NEVER design a Cassandra query like this.
  -- If you need this query, use a SQL analytical database instead.

# Horizontal scaling event:
scaling_scenario:
  current_nodes: 6
  peak_write_throughput: '120,000 writes/second'
  action: 'Add 2 more nodes to the cluster'
  result: 'Cassandra automatically rebalances token ranges.
           New throughput capacity: ~160,000 writes/second.
           Zero downtime. No application code changes.'
  equivalent_sql_action: 'Upgrade to a larger RDS instance (vertical),
                          causes maintenance window, costs 3x more per month.'

# This pseudocode shows HOW a ride-sharing backend routes
# different data types to the right database.
# This is the polyglot persistence pattern in action.

import psycopg2      # PostgreSQL client
import redis         # Redis client  
from cassandra.cluster import Cluster  # Cassandra client

# ─────────────────────────────────────────────
# DATABASE CONNECTIONS
# ─────────────────────────────────────────────

# PostgreSQL: user accounts, trips, payments (ACID required)
postgres_conn = psycopg2.connect(
    host='postgres-primary.internal',
    database='rideapp_core',
    user='app_service'
)

# Redis: real-time driver GPS positions (speed required, ephemeral data)
redis_client = redis.Redis(
    host='redis-cluster.internal',
    decode_responses=True
)

# Cassandra: completed trip event log (massive write volume, lookup by rider)
cassandra_session = Cluster(['cassandra-node-1', 'cassandra-node-2']).connect('rideapp')


# ─────────────────────────────────────────────
# OPERATION 1: Update driver GPS position
# Happens every 3 seconds per driver. At 50,000 active drivers = 16,000 writes/sec.
# Redis GEOADD handles this trivially. PostgreSQL would buckle under this write rate.
# ─────────────────────────────────────────────
def update_driver_location(driver_id: str, latitude: float, longitude: float):
    # Store as a geospatial point. TTL of 30 seconds — if driver goes offline,
    # their position automatically expires. No manual cleanup needed.
    redis_client.geoadd('active_drivers', [longitude, latitude, driver_id])
    redis_client.expire(f'driver:{driver_id}:location', 30)  # Auto-expire stale positions


# OPERATION 2: Find nearby drivers for a rider
# Redis GEORADIUS searches within 5km in under 1ms regardless of driver count.
def find_drivers_near_rider(rider_latitude: float, rider_longitude: float) -> list:
    nearby_drivers = redis_client.georadius(
        'active_drivers',
        rider_longitude,
        rider_latitude,
        5,           # 5 km radius
        unit='km',
        withcoord=True,
        count=10,    # Top 10 closest drivers
        sort='ASC'   # Nearest first
    )
    return nearby_drivers


# ─────────────────────────────────────────────
# OPERATION 3: Create a trip record (needs ACID)
# Rider payment authorization + trip creation must be atomic.
# If payment fails, trip must NOT be created. This is classic SQL territory.
# ─────────────────────────────────────────────
def create_trip_with_payment(rider_id: int, driver_id: int, fare_usd: float) -> int:
    cursor = postgres_conn.cursor()
    try:
        postgres_conn.autocommit = False  # Begin transaction

        # Authorize payment (deduct from rider's payment method)
        cursor.execute(
            "UPDATE rider_payment_methods SET pending_charge = pending_charge + %s "
            "WHERE rider_id = %s AND is_default = TRUE",
            (fare_usd, rider_id)
        )

        # Create the trip record — only happens if payment auth succeeded
        cursor.execute(
            "INSERT INTO trips (rider_id, driver_id, fare_usd, status, started_at) "
            "VALUES (%s, %s, %s, 'active', NOW()) RETURNING trip_id",
            (rider_id, driver_id, fare_usd)
        )
        trip_id = cursor.fetchone()[0]

        postgres_conn.commit()  # Both operations succeed or neither does
        return trip_id

    except Exception as e:
        postgres_conn.rollback()  # Payment failed — undo EVERYTHING
        raise RuntimeError(f'Trip creation failed, payment rolled back: {e}')


# ─────────────────────────────────────────────
# OPERATION 4: Log completed trip to event store
# Billions of trip events over time. Queried only by rider_id.
# Cassandra's partition-per-rider model is perfect here.
# ─────────────────────────────────────────────
def log_completed_trip_event(rider_id: str, trip_id: str, duration_minutes: int, fare_usd: float):
    cassandra_session.execute(
        """
        INSERT INTO rider_trip_history
          (rider_id, trip_completed_at, trip_id, duration_minutes, fare_usd)
        VALUES (%s, toTimestamp(now()), %s, %s, %s)
        """,
        (rider_id, trip_id, duration_minutes, fare_usd)
    )
    # This write goes to Cassandra's partition for this rider_id.
    # Scales to billions of events — query is always 'get trips for rider X'.