Exploring SQL vs. NoSQL Databases - Interview Question

"Should I use SQL or NoSQL?" is one of the most common interview questions in system design. The correct answer is always "it depends." Here's what it depends on.

SQL Databases

SQL databases store data in tables with rows and columns. You define a schema upfront. The database enforces that schema on every write. Relationships between tables are explicit through foreign keys.

Key properties:

• Structured schema. You declare your tables, columns, and types before inserting data. Schema changes require migrations.
• ACID transactions. Atomicity, Consistency, Isolation, Durability. Every transaction either fully succeeds or fully rolls back. Critical for financial systems.
• Relational model. Data is normalized across tables and joined at query time. Relationships are first-class citizens.
• SQL query language. Standardized, powerful, and decades mature. Complex queries, aggregations, and joins are built into the language.

Popular examples:

• PostgreSQL -- Feature-rich, extensible, strong SQL compliance. My default choice for most projects.
• MySQL -- Widely deployed, battle-tested, large ecosystem.
• Amazon Aurora -- Managed PostgreSQL/MySQL with auto-scaling. Used by Amazon.com for product catalogs and orders.

NoSQL Databases

NoSQL databases break away from the tabular model. They offer flexible schemas, horizontal scalability, and data models that map more naturally to certain use cases.

Key properties:

• Flexible schema. Store data without a predefined structure. Each record can have different fields. Great for rapidly evolving data models.
• Horizontal scalability. Designed to scale out across many servers. Adding capacity means adding nodes, not upgrading hardware.
• Eventual consistency. Most NoSQL databases prioritize availability over strict consistency (BASE over ACID). Data converges to a consistent state over time.
• Specialized data models. Pick the model that fits your access pattern.

Types and examples:

• Document: MongoDB -- stores JSON-like documents. Good for content management, user profiles, catalogs.
• Key-Value: Redis -- fast in-memory lookups. Good for caching, sessions, leaderboards.
• Columnar: Cassandra -- optimized for write-heavy time-series data. Good for IoT, analytics, logging.
• Graph: Neo4j -- stores nodes and relationships. Good for social networks, recommendation engines, fraud detection.

The Comparison

Dimension	SQL	NoSQL
Data Model	Tables (rows & columns)	Document, Key-Value, Columnar, Graph
Schema	Rigid, predefined	Flexible, schema-on-read
Query Language	SQL (standardized)	Database-specific APIs
Scaling	Vertical (scale up)	Horizontal (scale out)
Consistency	Strong (ACID)	Eventual (BASE)
Joins	Native, optimized	Expensive or unsupported

ACID vs BASE

These two acronyms capture the fundamental consistency trade-off.

ACID (SQL):

• Atomicity: All operations in a transaction succeed, or none do.
• Consistency: Every transaction leaves the database in a valid state.
• Isolation: Concurrent transactions don't interfere with each other.
• Durability: Committed data survives crashes.

BASE (NoSQL):

• Basically Available: The system stays responsive even during failures.
• Soft state: Data may be temporarily inconsistent during updates.
• Eventually consistent: All replicas converge to the same state given enough time.

ACID guarantees correctness at the cost of performance and scalability. BASE trades correctness for availability and speed. Neither is universally better.

When to Choose What

Choose SQL when:

• Your data has clear relationships and you need JOINs
• You need strong transactional guarantees (banking, e-commerce checkout)
• Your schema is well-defined and stable
• You need complex queries, aggregations, and reporting

Choose NoSQL when:

• Your data model is hierarchical or document-shaped
• You need horizontal scale across regions
• Your schema evolves rapidly or varies per record
• You need low-latency reads/writes at massive scale

Choose both when: Many production systems use SQL for transactional data and NoSQL for caching, search, or analytics. This is the norm, not the exception.

The question isn't which one is better. It's which one fits your specific access patterns, consistency requirements, and scale expectations.