SQL vs NoSQL: Model the Access Pattern, Then Pick the Store

A payments team once picked MongoDB for their billing system because a conference talk made document stores sound like the future. Billing is about as relational as data gets: customers own subscriptions, subscriptions produce invoices, invoices contain line items, and finance wants unpaid revenue per customer this month — a query that touches all four.

With no joins, that query became application code: fetch every customer, loop, fetch each one’s invoices, loop again, sum the line items in memory. “Void this invoice and reverse its line items” needed two writes to two documents with no transaction spanning them, so a crash between them left half-voided invoices that a nightly repair job hunted down. Two years in, they had rebuilt a query planner and a transaction manager by hand — badly — inside their app. The database wasn’t wrong. The match was.

“NoSQL” isn’t one thing

The framing that wrecks decisions is treating “NoSQL” as a single alternative to relational. It’s four unrelated families, each built for a different access shape. Lumping them together is like a menu that lists “Italian food” and “not-Italian food.”

• Relational (Postgres, MySQL). Normalized tables, joins, ACID transactions, and a query planner that answers questions you didn’t anticipate. The default, and it stays the default longer than most people think.
• Key-value (Redis, DynamoDB). A giant hash map: get(key) / put(key, value) in O(1). Sessions, caches, feature flags, shopping carts. No querying inside the value.
• Document (MongoDB). Stores JSON-ish documents; the whole aggregate (an order and its line items) lives in one record you read in one hit. Flexible schema, great when the aggregate is your read unit.
• Wide-column (Cassandra, Bigtable, HBase). Rows partitioned across a cluster, engineered for enormous write throughput and query-driven, denormalized tables. You design a table per query.
• Graph (Neo4j). First-class nodes and edges; traversing relationships (“friends of friends who liked X”) is cheap where the same query is a pile of self-joins in SQL.

Naming the four families is the easy part — the money is in knowing what each one costs you. The members-only continuation works through the ACID-vs-BASE trade, the mechanism that lets NoSQL scale writes (and the bill it hands you), the same billing schema modeled both the relational and the DynamoDB single-table way with runnable code, a decision walk for picking a store, the real-world systems behind each family, an interview-corner challenge, and a quiz to check yourself.