Track 2: data-layerID:
sql-vs-nosql-selection Mode: structured•Difficulty: beginner•⏱️ 15 mins
Module 3 · Data Layer · 15 min
Your app needs to store data. But which database?
The wrong database choice can cost you months of refactoring. SQL and NoSQL are fundamentally different tools for different problems. Let's learn when to use each.
01 / Concept Cards (Theory)Best Use Case: E-commerce Catalogs, User Profiles, CMS.Best Use Case: Caching, Session Token Storage, Leaderboards.Best Use Case: Chat History (Discord), IoT Sensor Telemetry, Logs.Best Use Case: Social Networks, Fraud Detection, Recommendation Engines.
SQL vs NoSQL: Two Different Worlds
Understand the core difference, test your intuition on real scenarios, then study the technical comparison.
1. Relational vs Non-Relational Paradigms
At the core of the database choice is a fundamental engineering tradeoff: Strict Consistency (ACID) vs High Availability & Horizontal Scale (BASE).
SQL — Relational (ACID)
┌────────┬──────────┬─────────┐ │ id │ name │ email │ ├────────┼──────────┼─────────┤ │ 1 │ Rahul │ r@x.com │ │ 2 │ Priya │ p@x.com │ └────────┴──────────┴─────────┘ Enforced schema. Foreign keys. ACID guarantees for financial safety.
NoSQL — Document / Key-Value (BASE)
{
"user_id": "u_101",
"name": "Rahul",
"preferences": { "dark_mode": true }
}
Dynamic schema-on-read.
BASE model: Eventual consistency.🏛️ ACID (SQL Guarantees)
- Atomicity: All operations in a transaction succeed, or all fail.
- Consistency: Data strictly satisfies all database rules & constraints.
- Isolation: Concurrent transactions don't interfere with each other.
- Durability: Committed data is saved permanently even if system crashes.
⚡ BASE (NoSQL Guarantees)
- Basically Available: Nodes remain responsive during partition failures.
- Soft-state: State can change over time without user interaction.
- Eventual Consistency: Data becomes consistent across nodes eventually.
💡 The Architectural Rule of Thumb
For most applications, a Relational Database (SQL) is the safest default choice — they have battle-tested reliability, ACID safety, and standard SQL tooling spanning over 40 years.
When Should You Explore Beyond Relational Databases?
1. Ultra-Low Latency NeededRequirements for sub-millisecond reads/writes where SQL disk I/O & lock contention become bottlenecks.
2. Unstructured / Dynamic DataData lacks rigid relational schemas or attributes change constantly per item (e.g. 500+ product categories).
3. Pure Serialization / DeserializationYou only need to store and fetch JSON/BSON documents directly without multi-table JOIN operations.
4. Massive Petabyte ScaleNeed to store astronomical data volumes requiring automated horizontal sharding out-of-the-box.
2. The 4 Categories of NoSQL Databases
NoSQL is not just one database type — it spans four distinct categories, each optimized for specific access patterns:
📄 Document StoreMongoDB, Couchbase
Stores data as JSON/BSON documents. Allows nested objects and arrays without complex JOINs.
Data Format Example (JSON):
{
"product_id": "p_9921",
"name": "Wireless Headphones",
"price": 2999,
"specs": { "noise_cancelling": true }
}🔑 Key-Value StoreRedis, Memcached, DynamoDB
O(1) lookups by key. Extremely fast in-memory or SSD-backed data retrieval.
Data Format Example (Key ➔ Value):
KEY: "session:user_101"
VAL: '{"user_id": 101, "role": "admin"}'
GET "session:user_101" ➔ 0.2ms latency📊 Wide-Column StoreApache Cassandra, HBase
Stores data in dynamic column families across huge distributed clusters with massive write throughput.
Data Format Example (Sparse Row):
RowKey: "channel_409" ├── msg_101 ➔ "Hello team!" ├── msg_102 ➔ "Meeting at 10 AM" └── msg_103 ➔ "NoSQL is fast!"
🕸️ Graph DatabaseNeo4j, Amazon Neptune
Stores Nodes (entities) and Edges (relationships). Traverses complex connected networks rapidly.
Data Format Example (Node ➔ Edge ➔ Node):
(User: Rahul) -[:FOLLOWS]➔ (User: Priya) (User: Priya) -[:WORKS_AT]➔ (Co: Google) Query: Friends who work at Google
3. Interactive Scenario Quiz
For each scenario below, pick whether SQL or NoSQL is the better fit. Click to see the answer and explanation.
🏦 A banking app tracking money transfers between accounts. Every transaction must be 100% reliable — no money can disappear.
✅ SQL is correct. Banking requires ACID transactions — if ₹500 leaves Account A, it MUST arrive in Account B. SQL databases guarantee this atomicity. NoSQL typically uses eventual consistency, which means money could temporarily "vanish" during a transfer.
📱 A social media app storing user profiles. Some users have a bio, some have a website, some have linked accounts — every profile looks different.
✅ NoSQL is correct. User profiles have wildly different fields. With SQL, you'd need dozens of nullable columns or complex join tables. NoSQL lets each document have its own shape — one user has "bio", another has "linkedin_url", and that's perfectly fine.
🛒 An e-commerce product catalog with 50,000 products across 200 categories. Each category has completely different attributes (shoes have "size", laptops have "RAM").
✅ NoSQL is correct. Each product category has unique attributes. Shoes need "size" and "material", laptops need "RAM" and "processor". In SQL, you'd need a separate table per category or a messy EAV pattern. NoSQL lets each product document define its own fields naturally.
🏥 A hospital patient records system with strict government regulations. Data must follow an exact format, and you need complex queries joining patient history, prescriptions, and billing.
✅ SQL is correct. Healthcare data requires strict schemas for regulatory compliance (HIPAA, etc.), and doctors need complex queries like "show all patients on medication X who visited in the last 30 days." SQL's JOINs and enforced schema are built for this.
🎮 A real-time gaming leaderboard with millions of score updates per second from players worldwide. Speed matters more than perfect consistency.
✅ NoSQL is correct. Millions of writes/sec requires horizontal scaling — adding more machines. NoSQL databases like Redis or DynamoDB are designed for this. SQL databases scale vertically (bigger machine), which has hard limits. For a leaderboard, it's fine if rankings are a few milliseconds behind.
4. System Design Interview Cheat Sheet
Here's a quick-reference decision table for system design interview questions:
| Criteria | SQL (PostgreSQL, MySQL) | NoSQL (MongoDB, Redis, Cassandra) |
|---|---|---|
| Data Structure | Structured, normalized, tabular | Unstructured, semi-structured, document/key-value |
| Scaling Strategy | Vertical (scale up RAM/CPU) + Read Replicas | Horizontal (scale out with sharding & partitioning) |
| ACID Compliance | Guaranteed natively across multi-table operations | Eventual consistency (Single-doc ACID in MongoDB) |
| Complex Queries & JOINs | Powerful JOINs, aggregate SQL expressions | Limited / Avoid JOINs (Denormalize data at write time) |
| Write Velocity | Moderate (disk I/O + index locks) | Extreme (LSM-trees, memory-first, append-only logs) |
| Best Production Fit | Financial systems, Order processing, Inventory, Auth | Social feeds, Real-time telemetry, Session cache, Catalogs |
02 / Practice Exercise
Build a Dual-Database Architecture
Design an e-commerce backend that uses SQL for structured order data and NoSQL for a flexible product catalog.
Interactive CanvasMode: Freeform Drag & Connect
Task: SQL + NoSQL Architecture
🎯
Scenario Prompt: You're building an e-commerce platform. User orders (structured, transactional) need a SQL database. The product catalog (flexible attributes per category) needs a NoSQL database. Connect the User to the App Server, and the App Server to both databases.
Available Components (Click to place on canvas):
🖱️Canvas is emptyClick components above to place them onto the canvas.
Drag components to arrange them freely, and click two nodes to connect them.
Drag components to arrange them freely, and click two nodes to connect them.
💡 Drag to move • Click Node A ➔ Node B to connect • Click ✕ to delete0 components placed
03 / Evaluation Rubric
System Validation Criteria
Our automated rubric checks your design for these critical rules:
Must Have Requirements (Hard Gate):
1. User connects to the App Server (not directly to any database).
2. App Server connects to SQL DB for structured, transactional order data.
3. App Server connects to NoSQL DB for flexible product catalog data.
Common Mistakes Checked:
1. User connecting directly to any database (major security risk — no auth, no validation).
Design Insight:
Many real-world systems use BOTH SQL and NoSQL. This is called Polyglot Persistence — using the right database for each specific job rather than forcing one database to do everything.