SQL vs. NoSQL: Choosing the Right Database
The choice rarely determines whether a feature is possible — it determines how much friction the team fights for the system's whole lifetime.
Beginner
TopNotchNote
Notes that matter
the real question isn't "SQL or NoSQL" — it's about the data
The choice of database rarely changes whether a feature is possible — almost any data can be forced into almost any database. What it changes is how much friction the team fights for the system's whole lifetime. Three questions drive the decision, in roughly this order of importance: is the data structured or does its shape vary record to record? What's the query pattern — a handful of predictable lookups, or ad hoc joins across many tables? And what scale of writes and storage is expected?
relational (SQL) databases
A relational database organizes data into tables with a fixed schema — every row has the same columns, and relationships between tables are expressed with foreign keys. For PlateRoute's
orders table, every order has exactly a customer ID, a restaurant ID, a total, and a status; that regularity is exactly what a relational schema is good at enforcing.The headline feature of relational databases is ACID transactions (covered in depth in ACID Transactions Explained) — strong guarantees that a multi-step write either fully happens or fully doesn't, which matters enormously for anything involving money or inventory. Examples: PostgreSQL, MySQL, Oracle.
non-relational (NoSQL) databases
NoSQL is really an umbrella term for databases that relax the fixed-schema, single-machine assumptions of relational databases in exchange for easier horizontal scaling and more flexible data shapes. Four common families:
| Family | Data shape | Good fit | Example |
|---|---|---|---|
| Key-value | An opaque value behind a unique key | Session data, shopping carts, caches | Redis, DynamoDB |
| Document | Semi-structured documents (JSON-like), fields can vary per record | Product catalogs where different item types have different attributes | MongoDB, Couchbase |
| Columnar | Data stored by column instead of by row, optimized for scanning one attribute across many rows | Analytics, time-ordered event data at huge scale | Cassandra, HBase |
| Graph | Nodes and edges representing entities and their relationships | Social graphs, recommendation engines | Neo4j |
These trade the relational model's strict structure and strong consistency guarantees for horizontal scalability and, in many cases, higher write throughput — see the CAP theorem for why that trade-off exists at all.
worked example: PlateRoute's product catalog
Say PlateRoute wants to store menu items across every restaurant on the platform. A pizza place's menu item has size and topping options; a coffee shop's has size and milk options; a grocery partner's item has a weight and an expiration date. Forcing all of that into one relational table means either a table with dozens of mostly-empty columns, or a maze of item-attribute join tables.
A document database sidesteps this cleanly — each item is stored as its own document with only the fields it actually has:
{ "itemId": "pz_104", "type": "pizza", "size": "large", "toppings": ["mushroom","olive"] }
{ "itemId": "cf_29", "type": "coffee", "size": "medium", "milk": "oat" }
This is the "impedance mismatch" that relational databases run into with irregularly shaped data, and it's the single most common reason a document store gets picked over a relational one for a catalog-shaped problem.
a decision table, not a rule
| If the data is… | …lean toward |
|---|---|
| Structured, and correctness of multi-step writes matters (payments, inventory) | Relational (SQL) |
| Simple key-to-blob lookups at very high throughput | Key-value store |
| Irregular shape across records, but queried mostly by ID | Document store |
| Enormous volume, a handful of known query patterns, append-heavy | Columnar store |
| Fundamentally about relationships between entities | Graph database |
Large real systems rarely use exactly one. PlateRoute's order-payment flow can sit on a relational database for its ACID guarantees, its catalog on a document store for flexibility, and its session/cart data on a key-value store for speed — picking per-workload rather than forcing one database to serve every workload well.