SQL vs NoSQL at a high level
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)"SQL or NoSQL for this project?" is a favourite design question. The interviewer is not looking for a winner: they check that you match the database to the data and to the queries.
Relational vs document, key-value, column and graph stores
NoSQL is a family of databases that do not use the relational table as their main structure. They store data as documents, key-value pairs, wide columns or graphs, each shaped around a different access pattern.
ShopLite's order 5001 can be stored three ways: as rows in two linked tables, as one nested document, or as a key-value pair. The program prints all three.
Example 1 · The same order as rows, a document and a key-value pair
import json
# Relational: two tables linked by order_idprint("orders: (5001, 'Priya', '2025-09-01')")print("order_item: (5001, 'Keyboard', 2), (5001, 'Mouse', 1)")
# Document: one nested object, items embeddeddoc = { "_id": 5001, "customer": "Priya", "items": [{"name": "Keyboard", "qty": 2}, {"name": "Mouse", "qty": 1}],}print(f"document: {doc['_id']} -> customer {doc['customer']}, {len(doc['items'])} embedded items")print(" as JSON:", json.dumps(doc))
# Key-value: an opaque value looked up by key onlykv = {"order:5001:status": "SHIPPED"}print("key-value: order:5001:status ->", kv["order:5001:status"])orders: (5001, 'Priya', '2025-09-01')
order_item: (5001, 'Keyboard', 2), (5001, 'Mouse', 1)
document: 5001 -> customer Priya, 2 embedded items
as JSON: {"_id": 5001, "customer": "Priya", "items": [{"name": "Keyboard", "qty": 2}, {"name": "Mouse", "qty": 1}]}
key-value: order:5001:status -> SHIPPEDWhat this code does
- The relational form needs a join to show an order with its items; the document form reads it in one fetch because the items are embedded.
- The key-value form is the fastest for one exact question ("what is the status of order 5001?") but cannot answer "all orders by Priya" without another structure.
A relational (SQL) database stores rows in tables with a fixed schema, links them with keys and joins them in queries; MySQL and PostgreSQL are examples. NoSQL databases fall into four main kinds:
- Document (MongoDB, Couchbase): JSON-like documents; an order with its items in one document. Good for nested, varied data.
- Key-value (Redis, DynamoDB in its simplest use): get and put a value by key; extremely fast. Good for caches, sessions, counters.
- Wide-column (Cassandra, HBase): rows with flexible columns, partitioned across many machines. Good for huge write volumes such as logs and sensor data.
- Graph (Neo4j): nodes and edges with properties. Good for relationships many levels deep: friends of friends, recommendations, fraud rings.
Predict the output
Pick the NoSQL type
Read the need, then pick the type you would name aloud.
Round 1 of 3
Which type fits the product catalogue best?
Schema flexibility and vertical vs horizontal scaling
Sharding splits the rows of one logical database across several machines by a key, such as the order id, so each machine stores and serves only a part of the data. It is how data scales out horizontally.
ShopLite has too many orders for one server. The program spreads order ids over four servers with a hash and prints how many orders each server gets.
Example 2 · Horizontal scaling with hash sharding
import zlib
def shard_for(key, shards): # crc32 is stable between runs (Python's built-in hash() of a str is not). return zlib.crc32(key.encode()) % shards # same key -> same shard, always
users = ["priya", "rahul", "arun", "meena", "kiran", "farah", "joseph", "lakshmi"]shards = 3servers = [[] for _ in range(shards)]for user in users: servers[shard_for(user, shards)].append(user)for i, members in enumerate(servers): print(f"Shard {i}: {members}")
print("Lookup 'meena' goes only to shard", shard_for("meena", shards))print("With 4 shards, 'meena' would move to shard", shard_for("meena", 4))Shard 0: ['kiran'] Shard 1: ['priya', 'rahul', 'meena', 'lakshmi'] Shard 2: ['arun', 'farah', 'joseph'] Lookup 'meena' goes only to shard 1 With 4 shards, 'meena' would move to shard 0
What this code does
- Each user's data lives on one machine chosen by hashing the key, so reads and writes for a user touch one shard and the load spreads across machines (with only 8 users the split is uneven; with millions it evens out).
- Changing the shard count moves many keys, which is why real systems use consistent hashing or pre-split ranges. A query across all users, or a join between users on different shards, must contact every shard.
A relational table has a schema on write: every row has the declared columns, and changing them needs an ALTER TABLE. Many NoSQL stores are schema on read: each document can have different fields, and the application interprets them. That flexibility speeds up early development but moves validation into code.
Vertical scaling (scale up) means a bigger machine: simple, but it has a ceiling and is a single point of failure. Horizontal scaling (scale out) means more machines sharing the data through sharding and replication (copies on several machines). Relational databases scale reads well with replicas; sharding them is possible but harder, because joins and transactions across shards are costly.
Predict the output
Shards and scaling
Work out the answer before you pick.
Round 1 of 2
Which shard holds order 1007?
The CAP theorem in simple terms
CAP is a theorem about databases spread over several machines: during a network partition, the system can stay consistent or stay available, but not both. Partitions do happen, so the real choice is CP or AP.
ShopLite keeps orders on two servers, and the network between them breaks. Should the shop refuse the writes it cannot copy, or keep accepting them? The program runs both choices.
Example 3 · CP vs AP during a network partition
class Wallet: def __init__(self): self.replica_a = self.replica_b = 100 # balance on two replicas self.partitioned = False
def write(self, balance, prefer_consistency): if self.partitioned and prefer_consistency: return "CP: write rejected (cannot reach replica B)" self.replica_a = balance if not self.partitioned: self.replica_b = balance return "AP: write accepted on A only" if self.partitioned else "write accepted on A and B"
def read_from_b(self, prefer_consistency): if self.partitioned and prefer_consistency: return "CP: read refused (B may be stale)" return f"read from B = {self.replica_b}" + (" (possibly stale)" if self.partitioned else "")
wallet = Wallet()print(wallet.write(80, True) + "; " + wallet.read_from_b(True))wallet.partitioned = Trueprint(wallet.write(50, True) + "; " + wallet.read_from_b(True))print(wallet.write(50, False) + "; " + wallet.read_from_b(False))wallet.partitioned = Falsewallet.replica_b = wallet.replica_a # replicas sync after the partition healsprint("Healed: " + wallet.read_from_b(False))write accepted on A and B; read from B = 80 CP: write rejected (cannot reach replica B); CP: read refused (B may be stale) AP: write accepted on A only; read from B = 80 (possibly stale) Healed: read from B = 50
What this code does
- Without a partition both replicas agree, so the system is consistent and available. During the partition the CP choice refuses requests; the AP choice answers but B returns the old balance of 80.
- After the network heals, the replicas sync and agree again (eventual consistency). A wallet balance usually wants CP; a "likes" counter can live with AP.
Consistency (C): every read sees the latest write. Availability (A): every request gets a non-error response. Partition tolerance (P): the system keeps working when the network between machines breaks. A CP system refuses or delays some requests to stay consistent; an AP system answers every request even if some answers are stale. With no partition, it can offer both.
Systems are often described as leaning CP (for example HBase, or MongoDB with majority writes) or AP (for example Cassandra with low consistency levels), but many let you tune this per query.
Predict the output
CP or AP?
Pick the behaviour that suits the data during a partition.
Round 1 of 2
Which behaviour is right for this data during a partition?
When to pick each, with examples
Polyglot persistence means using more than one kind of database in the same system, each for the job it does best, such as MySQL for orders, Redis for sessions and Elasticsearch for product search.
ShopLite needs orders and payments, a flexible product catalogue, sessions and OTPs, and recommendations. The example picks a database for each need.
Example 4 · Choosing databases for ShopLite
Needs:1. Orders and payments, refunds, wallet balance2. Product catalogue with very different fields per category3. Live courier location, updated every 5 seconds4. User login sessions and OTPs that expire in 5 minutes5. "Products your friends bought"1. Relational (MySQL/PostgreSQL) - ACID transactions, constraints, joins 2. Document store (MongoDB) - flexible nested product documents 3. Key-value / in-memory (Redis) - fast overwrite of latest position (wide-column if the full history must be stored at scale) 4. Key-value with TTL (Redis) - automatic expiry of sessions/OTPs 5. Graph database (Neo4j) - multi-hop friend relationships (or SQL joins while the data is small)
What this code does
- Each need picks the model whose strength matches it; money stays relational because transactions and constraints matter most there.
- This is one reasonable answer, not the only one. In an interview, saying why, and what you would start with (often just PostgreSQL plus Redis), matters more than naming many products.
Choosing a database starts from the data and the queries, not from fashion. Relational databases are the safe default for structured data with relationships and transactions. NoSQL fits when one need dominates: flexible documents, huge scale, very low latency or deep relationships. A good starting point:
- Money, inventory, bookings → relational (ACID transactions, constraints, joins).
- Product catalogue with varied attributes, user profiles → document store.
- Sessions, OTPs, rate-limit counters, leaderboards → key-value (Redis).
- Activity logs, IoT readings at very high write rates → wide-column; social graphs → graph database.
Order of execution
Answer "SQL or NoSQL?"
Tap the steps in the order you would say them in an interview.
Interview questions with model answers
SQL versus NoSQL questions reward balanced answers: name the trade-off, then pick for the use case. Avoid saying NoSQL is "faster" or "more modern" in general; say what it is faster at and what it gives up.
The Interview tab has the full bank, from "What is NoSQL?" to design questions such as "Which database would you use for an online shop's live order tracking?". Keep four lines ready:
- Must know: four NoSQL types with one example each.
- Must know: vertical = bigger machine; horizontal = more machines (sharding, replication).
- Must know: CAP = during a partition, choose consistency or availability.
- Must know: relational is the default for transactional, related data.
Next step
Check your understanding
12 quick questions