Database Design (HLD)

The Fundamental Tradeoff

Every system design interview eventually requires a database choice. The decision between SQL and NoSQL is not about which is "better" — it is about which fits your specific requirements.

Decision Matrix

Factor	SQL (Relational)	NoSQL (Non-relational)
ACID Compliance	Full ACID guarantees	BASE model (eventual consistency)
Schema	Fixed, predefined schema	Flexible, dynamic schema
Relationships	Native JOIN support	Denormalized, embedded documents
Scaling	Vertical (scale up)	Horizontal (scale out)
Query Language	Standardized SQL	Varies (MQL, CQL, DynamoDB API)
Data Structure	Tables with rows/columns	Documents, key-value, wide-column, graph

When to Choose SQL

Choose SQL when:

• Data integrity is critical: Financial transactions, inventory management, order processing
• Complex relationships exist: Multiple entities with many-to-many relationships
• Ad-hoc queries are needed: Business analysts need flexible reporting
• ACID compliance is non-negotiable: Banking, payment systems

When to Choose NoSQL

Choose NoSQL when:

• Massive scale is required: Millions of reads/writes per second
• Schema flexibility matters: Data structure evolves rapidly
• Low latency at scale: Real-time bidding, gaming leaderboards
• Simple access patterns: Key-value lookups, document retrieval

Database Technology Selection Guide

PostgreSQL

• Best for: Complex queries, ACID transactions, geospatial data
• Use when: E-commerce, ERP systems, content management
• Strengths: JSON support, full ACID, rich indexing, extensions ecosystem
• Example: Order management system with complex joins across users, products, and orders

MySQL

• Best for: Read-heavy workloads, web applications, replication
• Use when: CMS, e-commerce platforms, SaaS applications
• Strengths: Mature ecosystem, fast reads, widespread tooling
• Example: Product catalog with frequent reads and occasional writes

MongoDB

• Best for: Document storage, rapid prototyping, content management
• Use when: User profiles, product catalogs with varying attributes, event logging
• Strengths: Flexible schema, horizontal scaling, rich query language
• Example: Social media posts with varying metadata, comments, and media

DynamoDB

• Best for: Key-value access patterns, serverless applications, gaming
• Use when: Session storage, shopping carts, user state, IoT data
• Strengths: Single-digit millisecond latency, auto-scaling, fully managed
• Example: Real-time gaming leaderboard with millions of concurrent users

Cassandra

• Best for: Write-heavy workloads, time-series data, high availability
• Use when: IoT sensor data, event logging, audit trails
• Strengths: Linear scalability, no single point of failure, tunable consistency
• Example: Smart home device telemetry collecting millions of readings per second

Redis

• Best for: Caching, session management, real-time analytics
• Use when: Rate limiting, leaderboards, pub/sub messaging
• Strengths: In-memory speed, data structures, persistence options
• Example: Session cache for authentication tokens and user preferences

Real-World Database Choices by System

E-commerce Platform

• Product catalog: MongoDB (flexible attributes per product category)
• Orders/transactions: PostgreSQL (ACID for financial data)
• Session/cache: Redis (fast session retrieval)
• Search: Elasticsearch (full-text product search)

Chat Application

• Messages: Cassandra (write-heavy, time-series pattern)
• User profiles: PostgreSQL (relational, structured)
• Online status: Redis (real-time presence tracking)
• Message search: Elasticsearch (full-text search across messages)

Analytics Pipeline

• Raw events: Cassandra or DynamoDB (high write throughput)
• Aggregated data: PostgreSQL (complex analytical queries)
• Real-time dashboards: Redis (precomputed aggregations)
• Historical data: Columnar stores like Redshift or BigQuery

Vertical Partitioning

Vertical partitioning splits a table by columns, placing different column groups in separate tables or databases. This reduces I/O by only loading relevant data.

When to use:

• Tables with many columns but queries typically access only a subset
• Large text/blob columns mixed with frequently accessed data
• Security requirements where different teams access different columns

Example: User table vertical partitioning

Original table:

users(user_id, name, email, password_hash, bio, profile_image_url, preferences_json)

Partitioned into:

user_auth(user_id, email, password_hash)  -- Auth service accesses
user_profile(user_id, name, bio, profile_image_url)  -- Profile service accesses
user_preferences(user_id, preferences_json)  -- Preference service accesses

Horizontal Partitioning (Sharding)

Horizontal partitioning splits rows across multiple database instances. Each shard contains a subset of the total data.

Sharding Strategies:

Hash-based Sharding

shard_id = hash(user_id) % number_of_shards

• Pros: Even data distribution, simple implementation
• Cons: Difficult to add/remove shards, range queries require scatter-gather
• Use when: Access patterns are uniform, data grows linearly

Range-based Sharding

if (user_id < 1000000) -> shard_0
if (user_id < 2000000) -> shard_1
...

• Pros: Range queries efficient, easy to understand
• Cons: Hotspots if access is skewed (new users write to latest shard)
• Use when: Range queries are common, data has natural ordering

Directory-based Sharding

lookup_table = {user_id -> shard_id}

• Pros: Flexible rebalancing, any sharding key
• Cons: Lookup table is a bottleneck, single point of failure
• Use when: Rebalancing is frequent, complex routing needed

Normalization vs Denormalization

Normalization (3NF) eliminates redundancy:

orders(order_id, user_id, product_id, quantity)
users(user_id, name, email)
products(product_id, name, price)

Denormalization adds redundancy for read performance:

orders(order_id, user_id, user_name, product_id, product_name, product_price, quantity)

When to denormalize:

• Read-heavy systems where JOINs are expensive
• Data that rarely changes (product names, user names)
• When latency requirements are sub-millisecond

When to normalize:

• Write-heavy systems where updates are frequent
• Data integrity is critical
• Storage is a concern

Indexing Strategy

B-Tree Index: Default for most databases. Good for equality and range queries.

CREATE INDEX idx_orders_user_id ON orders(user_id);

Composite Index: For multi-column queries.

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);

• Column order matters: put high-selectivity columns first

Covering Index: Includes all columns needed by a query.

CREATE INDEX idx_orders_covering ON orders(user_id, order_date) INCLUDE (total_amount, status);

• Query never hits the actual table (index-only scan)

Partial Index: Indexes only a subset of rows.

CREATE INDEX idx_orders_pending ON orders(order_date) WHERE status = 'pending';

• Smaller index, faster maintenance, lower storage

Schema Design Anti-Patterns

1. Over-normalization: Too many JOINs for simple queries
2. Under-normalization: Data inconsistency when updates propagate
3. Missing foreign keys: Orphaned records accumulate
4. UUID as primary key: Index fragmentation, slower inserts vs auto-increment
5. Large JSON columns: Cannot be indexed efficiently, full table scans
6. God table: Single table with 100+ columns serving multiple services

Read Scaling: Replication

Primary-Replica Architecture

One primary handles writes; replicas handle reads. Most production systems use this pattern.

                    ┌─────────────┐
                    │   Primary   │ ← writes
                    │  (master)   │
                    └──────┬──────┘
                           │ WAL/binlog
              ┌────────────┼────────────┐
              ▼            ▼            ▼
        ┌──────────┐ ┌──────────┐ ┌──────────┐
        │ Replica1 │ │ Replica2 │ │ Replica3 │
        └──────────┘ └──────────┘ └──────────┘
              ↑            ↑            ↑
              └────────────┼────────────┘
                    reads distributed

Synchronous vs Asynchronous Replication:

Type	Consistency	Latency	Use Case
Synchronous	Strong	Higher	Financial systems
Asynchronous	Eventual	Lower	Social media, analytics
Semi-synchronous	Balanced	Medium	E-commerce

Read-after-write consistency problem:

• Write to primary, read from replica that hasn't synced yet
• Solutions: Read from primary for recent writes, session stickiness, version vectors

Write Scaling: Sharding

When to shard:

• Single database cannot handle write throughput
• Data exceeds single machine storage
• Latency requirements demand data locality

Sharding implementation layers:

Application Layer: App determines shard for each query.

def get_shard(user_id):
    return user_id % NUM_SHARDS

def get_connection(user_id):
    shard = get_shard(user_id)
    return connections[shard]

Proxy Layer: Middleware handles routing (Vitess, ProxySQL).

Database Layer: Native sharding (Cassandra, DynamoDB, CockroachDB).

Resharding challenges:

• Adding shards requires data migration
• Downtime during rebalancing (mitigate with consistent hashing)
• Temporary inconsistency during migration

Connection Pooling

Problem: Each database connection costs 5-10MB memory. 10,000 connections = 50-100GB RAM.

Solution: Connection pool

Pool Configuration:
- Min connections: 10
- Max connections: 100
- Connection timeout: 30s
- Idle timeout: 600s

Tools: PgBouncer (PostgreSQL), ProxySQL (MySQL), HikariCP (Java)

Best practices:

• Pool size = (2 * CPU cores) + effective_spindle_count
• Monitor active vs idle connections
• Set connection timeout to fail fast
• Use separate pools for read/write

Caching Layer Integration

Cache-aside pattern with database:

1. Check cache for key
2. If HIT → return cached value
3. If MISS → query database → store in cache → return value
4. On write → update database → invalidate cache

Cache invalidation strategies:

• TTL-based: Cache expires after N seconds
• Event-based: Invalidate on write (more complex, more accurate)
• Version-based: Cache key includes version number

Database Scaling Anti-Patterns

1. Premature sharding: Sharding before hitting actual limits adds enormous complexity
2. Cross-shard joins: Expensive scatter-gather queries across shards
3. Hotspot shards: Uneven data distribution from poor shard key selection
4. No connection pooling: Exhausting database connections under load
5. Missing read replicas: All reads hitting primary database
6. Over-caching: Caching data that changes frequently or is rarely accessed