Databases

Databases are the foundation of almost every system you'll design. Understanding when to use different types of databases and their trade-offs is essential knowledge for any system design interview.

SQL vs NoSQL: The Basics

The choice between SQL and NoSQL databases is one of the most fundamental decisions in system design.

SQL Databases

Relational databases like PostgreSQL, MySQL, and SQL Server excel at:

• Structured data: When you know your schema upfront
• ACID transactions: When data consistency is critical
• Complex queries: JOINs across multiple tables
• Strong consistency: When you can't afford stale reads

NoSQL Databases

Non-relational databases like MongoDB, Cassandra, and DynamoDB excel at:

• Flexible schemas: When your data structure evolves
• Horizontal scaling: When you need to scale across many machines
• High throughput: When you need massive read/write capacity
• Eventual consistency: When some staleness is acceptable

When to Use SQL

Use SQL When

• You have complex relationships between entities
• You need ACID transactions (e.g., financial systems)
• Your query patterns are complex and varied
• Data integrity is critical

SQL Example: E-commerce

Users table:
- user_id (primary key)
- email
- name

Orders table:
- order_id (primary key)
- user_id (foreign key)
- total_amount
- status

Order_Items table:
- item_id (primary key)
- order_id (foreign key)
- product_id (foreign key)
- quantity

When to Use NoSQL

Use NoSQL When

• You need to scale horizontally to handle massive traffic
• Your data structure is flexible or evolves frequently
• You have simple query patterns (usually key-value lookups)
• You can tolerate eventual consistency

NoSQL Example: Social Media Feed

{
  "feed_id": "user_123_feed",
  "posts": [
    {
      "post_id": "p_456",
      "author": "user_789",
      "content": "Hello world!",
      "timestamp": 1642345678,
      "likes": 42
    }
  ]
}

Types of NoSQL Databases

Key-Value Stores

Examples: Redis, DynamoDB, Memcached

Best for:

• Caching
• Session storage
• Simple lookups by key

Document Stores

Examples: MongoDB, CouchDB

Best for:

• Flexible schemas
• Nested data structures
• Content management

Wide-Column Stores

Examples: Cassandra, HBase

Best for:

• Time-series data
• High write throughput
• Distributed across regions

Graph Databases

Examples: Neo4j, Amazon Neptune

Best for:

• Highly connected data
• Social networks
• Recommendation engines

Database Scaling Strategies

Vertical Scaling (Scale Up)

• Add more CPU, RAM, or storage to your existing database
• Simple but has limits
• Good for early-stage growth

Horizontal Scaling (Scale Out)

• Add more database servers
• Required for massive scale
• Introduces complexity

Replication

Create copies of your database for:

• Read replicas: Distribute read load
• Disaster recovery: Backup in case of failure
• Geographic distribution: Reduce latency

Sharding

Split your data across multiple databases:

• By user ID: Users 1-1M on shard 1, 1M-2M on shard 2
• By geography: US data on US servers, EU data on EU servers
• By time: Current year on fast storage, archives on slow storage

Common Interview Patterns

User Data → SQL

User profiles, authentication, and account settings typically go in SQL for ACID guarantees.

Activity Data → NoSQL

Feeds, logs, and analytics typically go in NoSQL for scale.

Caching Layer → Redis

Frequently accessed data gets cached in Redis for sub-millisecond latency.

Database Trade-offs

What's Next

Now that you understand databases, let's look at how caching can dramatically improve your system's performance and reduce database load.