Beyond “It Scales”: A Framework for Choosing Databases in System Design Interviews

Picture this: you’ve just drawn a flawless architecture diagram on the whiteboard. The system looks robust. Then, the interviewer points to your generic database cylinder and asks the dreaded question: “Why did you choose that specific datastore?”

If your only defense is that “it scales easily,” you’ve likely lost the room.

System design interviews rarely hinge on your ability to implement a niche sorting algorithm. They test your ability to weigh engineering trade-offs. The good news is that you don’t need to be an expert in the hundreds of database technologies on the market. You only need to deeply understand a handful of core archetypes and the foundational theories that govern them.

Here is a comprehensive framework to help you navigate the database selection process with confidence.

The Foundation: The ACID vs. BASE Spectrum

Before you even look at a specific database logo, you need to understand the theoretical battleground on which they all sit. Every modern datastore makes a compromise between two conflicting design philosophies: ACID and BASE.

ACID: The “Correctness First” Approach

ACID stands for Atomicity, Consistency, Isolation, and Durability. Systems built on this paradigm prioritize strict rules and data integrity above all else.

• The Guarantee: If a transaction is processed, it is processed entirely and securely, moving the system from one valid state to another without interference from concurrent actions.
• The Trade-off: Ensuring this level of strictness usually introduces latency and makes horizontal scaling (adding more machines) incredibly complex.
• When to use it: Financial ledgers, billing systems, and inventory management. If a data discrepancy means real-world money is lost, ACID is non-negotiable.

BASE: The “Availability First” Approach

To handle the massive, global scale of modern web applications, engineers realized that strict ACID compliance was a bottleneck. Enter BASE: Basically Available, Soft state, Eventual consistency.

• The Guarantee: The system prioritizes staying online and accepting requests, even in the event of hardware failures or network partitions.
• The Trade-off: You might occasionally read stale data. The system will eventually synchronize and become consistent, but it is not guaranteed at the exact millisecond of a query.
• When to use it: Social media timelines, analytics pipelines, and recommendation engines. If a user sees a “like” count that is a few seconds out of date, the business is perfectly fine.

The Database Archetypes You Need to Know

Armed with the theory, you can now categorize the major database players. Understanding these six archetypes will cover the vast majority of interview scenarios.

1. The Relational Standard (e.g., MySQL, PostgreSQL)

These are your classic ACID-compliant workhorses. They use rigid schemas to enforce data integrity and rely heavily on tables and structured relationships.

• Architecture: Typically scaled by adding read replicas, though writes usually bottleneck at a single primary node to ensure consistency.
• The Interview Use Case: E-commerce transactions, user authentication tables, or anywhere the data has strict, unchanging relationships.

2. The Flexible Document Store (e.g., MongoDB)

Document databases abandon rigid tables in favor of storing data as JSON-like objects. This allows for immense flexibility as application requirements change.

• Architecture: Often supports native sharding (partitioning data across multiple servers) and provides strong consistency at the primary node level.
• The Interview Use Case: User profiles, content management systems, or product catalogs where the attributes might vary wildly from item to item.

3. The Wide-Column Heavyweight (e.g., Cassandra)

Built squarely on the BASE philosophy, wide-column stores are designed to ingest a massive firehose of data without breaking a sweat.

• Architecture: They utilize peer-to-peer replication with no single point of failure. Writes are incredibly cheap because they are simply appended to a log.
• The Interview Use Case: Application logging, IoT sensor telemetry, and time-series metrics. Use this when your system is writing data constantly and querying it by time range.

4. The Cloud-Native Key-Value Store (e.g., DynamoDB)

These are fully managed, highly available datastores designed to provide single-digit millisecond performance at virtually infinite scale.

• Architecture: They abstract away the infrastructure, but require you to perfectly design your partition keys. Poor key design leads to “hot partitions” and bottlenecked performance.
• The Interview Use Case: AWS-heavy architectures where operational overhead must be minimized, and access patterns (how you will query the data) are known entirely upfront.

5. The In-Memory Cache (e.g., Redis)

Caches exist because querying disk-based databases is simply too slow for frequently accessed data. By keeping data in RAM, read and write times drop to sub-millisecond levels.

• Architecture: Highly performant, but volatile. Without specific (and performance-draining) configurations, data is lost if the server restarts.
• The Interview Use Case: Session storage, API rate limiting, gaming leaderboards, and shielding your primary database from massive read spikes.

6. The Graph Navigator (e.g., Neo4j)

Relational databases struggle when you need to join multiple tables together sequentially (e.g., finding the friends of your friends’ friends). Graph databases treat the relationships between data points as first-class citizens.

• Architecture: Optimized for traversing complex webs of connected nodes rather than scanning flat rows.
• The Interview Use Case: Fraud detection rings, complex recommendation engines, and social network mapping.

Your Interview Decision Matrix

When presented with a system design prompt, don’t jump straight to naming a technology. Instead, walk your interviewer through this three-step reasoning process:

1. Analyze the Access Pattern: Are we fetching data by a single ID (Key-Value), scanning ranges of dates (Wide-Column), or traversing complex connections (Graph)?
2. Determine the Consistency Needs: Do we need strict transactional safety (Relational/ACID) or can we prioritize high availability and speed (Document/Wide-Column/BASE)?
3. Evaluate the Scale: Is this a read-heavy system where we can rely heavily on caching, or a write-heavy system that needs decentralized ingestion?

The Database Selection Summary Matrix

Use this matrix as your mental cheat sheet before walking into an interview. It synthesizes the trade-offs of the six core archetypes so you can make fast, defensible decisions.

The Takeaway

Every database was invented because its predecessors failed to solve a specific engineering problem. Relational databases couldn’t handle unstructured data gracefully. Document stores couldn’t handle massive, decentralized write throughput. Caches were born because disks were too slow.

If you understand the historical and technical constraints that gave birth to these tools, your architecture choices will never be arbitrary again. You won’t just be reciting a cheat sheet; you’ll be engineering a solution.