SQL vs NoSQL & Data Modeling for Scale

"Would you use SQL or NoSQL here?" is one of the most predictable questions in a system design interview, and one of the most frequently answered badly. Weak answers pick a database by fashion ("NoSQL scales better") or by habit ("we always use Postgres"). Strong answers start from the workload — what the data looks like, how it is read and written, how much of it there is, and what consistency the product needs — and then choose the storage model whose strengths line up with those facts. That reasoning is what the interviewer is grading.

This subject gives you the vocabulary and the decision procedure. It covers what the relational model is good at and why it remains the correct default; the NoSQL families and the workloads each was built for; how storage engines and consistency options differ; and, most practically, how to model data for a wide-column or DynamoDB-style store where you cannot join and must design tables around queries. It ends with a worked comparison of the same e-commerce domain in Postgres and DynamoDB.

Sharding mechanics — shard keys, rebalancing, cross-shard queries — are covered in the Database Replication & Sharding subject; index internals in Database Indexing; and the consistency-under-partition framing in the CAP Theorem subject. This subject references those where the modelling decision depends on them.

The Relational Model and Why It Is Still the Default

A relational database stores data in tables with a declared schema, enforces constraints, and answers arbitrary queries with SQL. Its strengths are precisely the things that are hard to bolt on later:

• ACID transactions. A multi-row, multi-table change either fully happens or does not. Transferring money, decrementing stock while creating an order, or updating three tables on sign-up is a single BEGIN ... COMMIT.
• Joins and ad-hoc queries. You do not need to know the queries in advance. Product will ask "orders by region by product category last quarter" six months in, and a relational schema answers it with a query and maybe an index — not a re-model.
• Constraints. Foreign keys, UNIQUE, CHECK, NOT NULL push data integrity into the database, where it cannot be bypassed by a buggy code path.
• Mature tooling. Query planners, backups, replication, monitoring, ORMs, and decades of operational knowledge.

A single well-provisioned Postgres or MySQL node handles tens of thousands of transactions per second and terabytes of data, and read replicas extend that for read-heavy workloads. Most systems in most interviews fit comfortably within that envelope. The default answer is a relational database unless a specific requirement rules it out: write throughput or data volume beyond what one node plus replicas can serve, a data shape that fights the relational model (deeply nested documents, graphs), a need for a specific access primitive (full-text search, time-series rollups), or a latency/availability target that requires multi-region active-active writes.