SQL vs NoSQL: How to Choose in a System Design Interview

"Would you use SQL or NoSQL here?" comes up in nearly every system design interview, and weak answers pick a side on reputation. The answer interviewers want is about where the cost lands. A relational database makes writes simple and pays at read time with joins. A document store makes the common read a single lookup and pays at write time, every time a duplicated fact changes. Choosing is matching that trade to the queries the feature actually has to answer.

The problem it solves

Every application stores entities that refer to each other: customers and their invoices, users and their posts. There are two basic ways to lay that out.

Relational (SQL): each kind of entity gets a table with fixed columns, and each fact is stored exactly once. An invoice row holds a customer id, not the customer's name. A query reassembles the full picture with joins, and transactions let a write that touches several tables succeed or fail as a whole. The database enforces the schema: a row missing a required column is rejected.

Document (NoSQL): each object is stored whole, with its nested parts inside it, often as JSON. The invoice document carries the customer's name and every line item. Reading it is one lookup and no join. Fields can differ from document to document, and the database does not check them.

"NoSQL" also covers key-value, wide-column and graph databases, and the trade-off below applies to them in the same shape: data laid out for one access pattern, with duplication instead of joins.

The intuition

Normalised data is cheap to change and costs work to read. Renaming a customer updates one row, and every invoice sees the new name at once. But showing an invoice joins three tables.

Denormalised data is cheap to read and costs work to change. The invoice document is ready to display, but the customer's name is copied into every invoice she has, and a rename must find and rewrite every copy. Miss one, and the data disagrees with itself.

Two more differences matter in an interview:

• Schema. "Schemaless" does not mean the data has no shape. The shape moves out of the database and into application code, where nothing enforces it. That is a real benefit when records genuinely vary, such as inspection notes whose fields depend on the vehicle, and a cost everywhere else.
• Transactions. Relational databases were built around multi-row, multi-table transactions. Several document databases now offer multi-document transactions as well, as of September 2026, but the model is designed so the common write touches one document, which is atomic on its own.

Horizontal scaling is where document and key-value stores earned their reputation: a lookup by key goes to one shard, while a join across shards is expensive. Relational databases scale too, with replicas and sharding, so "NoSQL scales" is not an answer on its own.

Watch it run

The animation shows the same invoice in two shapes and asks which side pays: writes, or reads. Relational comes first: fixed columns, and each fact stored exactly once. The database enforces the shape, so a row with no total is simply rejected. A read joins the tables back together to produce the whole invoice, and a write can be all-or-nothing across both tables, so the totals always reconcile. Then a document store keeps the whole object together instead, so the read needs no join at all: one lookup returns everything. Fields may vary between records, and this inspection has ones no van check has. The price: the customer's name is now duplicated into every document that mentions her. She changes her name, and every copy has to be found and updated. "Schemaless" only moves the schema into application code, where nothing checks it. So the garage runs both: strict columns for money, documents for the shape that genuinely varies.

The code

The relational side runs on SQLite 3.37 through Python's sqlite3; the document side is a toy model, not a real document database. First the schema at work: an invoice without a total is rejected, and a join rebuilds invoice 10 from three tables:

import copy
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
  CREATE TABLE invoice (id INTEGER PRIMARY KEY,
                        customer_id INTEGER NOT NULL REFERENCES customer(id),
                        total REAL NOT NULL CHECK (total >= 0));
  CREATE TABLE line (invoice_id INTEGER REFERENCES invoice(id), part TEXT, price REAL);
  INSERT INTO customer VALUES (1, 'Ada Ruiz');
  INSERT INTO invoice VALUES (10, 1, 180.0), (11, 1, 95.0);
  INSERT INTO line VALUES (10, 'brake pads', 120.0), (10, 'labour', 60.0), (11, 'oil change', 95.0);
""")

try:
    db.execute("INSERT INTO invoice (id, customer_id) VALUES (12, 1)")   # no total
except sqlite3.IntegrityError as e:
    print(type(e).__name__)                           # IntegrityError

INVOICE = """
  SELECT c.name, i.total, l.part, l.price
  FROM invoice i JOIN customer c ON c.id = i.customer_id
                 JOIN line l ON l.invoice_id = i.id
  WHERE i.id = ? ORDER BY l.part"""
print(db.execute(INVOICE, (10,)).fetchall())
# [('Ada Ruiz', 180.0, 'brake pads', 120.0), ('Ada Ruiz', 180.0, 'labour', 60.0)]

The same data as documents. Reading an invoice is one get, and two inspection records carry different fields without any schema change:

class DocStore:
    """Toy model of a document store: whole objects by key, no schema, no joins."""
    def __init__(self):
        self.docs, self.writes = {}, 0

    def put(self, key, doc):
        self.docs[key] = copy.deepcopy(doc)
        self.writes += 1

    def get(self, key):
        return copy.deepcopy(self.docs[key])

store = DocStore()
store.put("inv:10", {"customer": "Ada Ruiz", "total": 180.0,
                     "lines": [{"part": "brake pads", "price": 120.0}, {"part": "labour", "price": 60.0}]})
store.put("inv:11", {"customer": "Ada Ruiz", "total": 95.0,
                     "lines": [{"part": "oil change", "price": 95.0}]})
store.put("insp:7", {"vehicle": "motorcycle", "chain_slack_mm": 30, "tyre_depth_mm": 2.1})
store.put("insp:8", {"vehicle": "van", "load_bay_ok": True})
print(store.get("inv:10")["lines"][0])                # {'part': 'brake pads', 'price': 120.0}
print(sorted(store.get("insp:7")), sorted(store.get("insp:8")))
# ['chain_slack_mm', 'tyre_depth_mm', 'vehicle'] ['load_bay_ok', 'vehicle']

Now the bill. The rename is one row in SQL and one write per invoice in the document store. A crash inside a transaction leaves nothing behind in SQL; two separate document writes interrupted halfway leave a half-written invoice:

with db:
    changed = db.execute("UPDATE customer SET name = 'Ada Byrne' WHERE id = 1").rowcount
before = store.writes
for key, doc in store.docs.items():                   # every copy must be found
    if doc.get("customer") == "Ada Ruiz":
        doc = copy.deepcopy(doc)
        doc["customer"] = "Ada Byrne"
        store.put(key, doc)
print(changed, store.writes - before)                 # 1 2

try:
    with db:                                          # all or nothing
        db.execute("INSERT INTO invoice VALUES (13, 1, 40.0)")
        db.execute("INSERT INTO line VALUES (13, 'wiper', 40.0)")
        raise RuntimeError("crash before commit")
except RuntimeError:
    pass
print(db.execute("SELECT COUNT(*) FROM invoice WHERE id = 13").fetchone())   # (0,)

store.put("inv:13", {"customer": "Ada Byrne", "total": 40.0, "lines": []})
# ...crash here, before the second write that would add the line: the half-written
# document stays, because these were two separate writes.
print(store.get("inv:13"))                            # {'customer': 'Ada Byrne', 'total': 40.0, 'lines': []}

Checked on 200 seeded random garages. After a random customer is renamed both ways, every invoice rebuilt by joins must equal its stored document, and the document store must have spent one write per invoice of that customer, counted in SQL:

import random

random.seed(24)
ok = True
for _ in range(200):
    rel = sqlite3.connect(":memory:")
    rel.executescript("""
      CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
      CREATE TABLE invoice (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
      CREATE TABLE line (invoice_id INTEGER, part TEXT, price REAL);""")
    docs = DocStore()
    names = {c: f"cust{c}" for c in range(random.randint(1, 4))}
    rel.executemany("INSERT INTO customer VALUES (?, ?)", names.items())
    for inv in range(random.randint(1, 8)):
        cust = random.choice(list(names))
        lines = [(f"p{k}", float(random.randint(1, 9) * 10)) for k in range(random.randint(1, 3))]
        total = sum(p for _, p in lines)
        rel.execute("INSERT INTO invoice VALUES (?, ?, ?)", (inv, cust, total))
        rel.executemany("INSERT INTO line VALUES (?, ?, ?)", [(inv, part, p) for part, p in lines])
        docs.put(f"inv:{inv}", {"customer": names[cust], "total": total,
                                "lines": [{"part": part, "price": p} for part, p in lines]})
    victim = random.choice(list(names))
    new_name = f"renamed{victim}"
    rel.execute("UPDATE customer SET name = ? WHERE id = ?", (new_name, victim))
    old_writes = docs.writes
    for key, doc in list(docs.docs.items()):
        if doc["customer"] == names[victim]:
            doc = copy.deepcopy(doc)
            doc["customer"] = new_name
            docs.put(key, doc)
    expected_writes = rel.execute("SELECT COUNT(*) FROM invoice WHERE customer_id = ?", (victim,)).fetchone()[0]
    ok &= docs.writes - old_writes == expected_writes          # one write per duplicated copy
    for key in docs.docs:
        inv = int(key.split(":")[1])
        rows = rel.execute(INVOICE, (inv,)).fetchall()          # rebuilt by joins at read time
        doc = docs.get(key)                                     # stored whole
        ok &= [(doc["customer"], doc["total"], l["part"], l["price"])
               for l in sorted(doc["lines"], key=lambda l: l["part"])] == rows
print(ok)                                             # True

The complexity

• Reading one invoice: relational, one indexed lookup per joined table; document, one lookup by key.
• Changing a shared fact: relational, one row; document, one write per copy, k writes for a customer with k invoices, plus a way to find them.
• A new kind of question: relational answers it with new joins; a document store answers well only the access patterns it was shaped for.

Where it goes wrong

• Documents for data that is naturally relational. Many-to-many relationships turn into duplicated copies that drift apart.
• Money outside transactions. Anything that must reconcile, like balances and invoices, wants multi-entity atomicity.
• Forgetting indexes. A join without an index on the join column is a scan per row; see SQL indexes.

When it shows up in interviews

It appears early in almost every design round, right after the requirements: "what would you store this in?". Payment ledgers, orders and inventory lean relational because of transactions; chat messages, activity feeds and product catalogues with varied attributes lean towards document or wide-column stores keyed by the main access pattern. Strong answers mix them, and then discuss consistency and caching for the hot reads.

How to say it in an interview

"I start from the queries. If the data is relational, needs ad hoc queries, or has writes that must be all-or-nothing across entities, like money, I use a relational database: facts stored once, joins at read time, transactions. If a record is read whole by one key, its shape genuinely varies, and I need to spread writes across many machines, a document store fits: the read is a single lookup, and I accept that duplicated fields cost extra writes and that the schema now lives in my code. Often the answer is both, each for the part of the data it suits."