SQL versus NoSQL data stores

01A family name is not a guarantee

A shop stores products, orders and reviews. Choosing a database begins with the operations those records must support.

• Relational tables can store an Order row and related OrderLine rows. SQL is a query language commonly used to retrieve and combine such records.
• A document can store one product's display fields together as one object.
• A key-value store retrieves a value by a known key, such as a session ID.
• Wide-column models commonly group rows under a partition key and order them by additional keys, supporting planned range queries.
• Graph models store relationships that queries follow between records.

NoSQL is an umbrella label for several of these models, not one alternative with one guarantee. Relational systems can distribute data, and some NoSQL systems support multi-record transactions and current reads. Check the concrete operation, supported records and failure behavior instead of assuming SQL means “cannot scale” or NoSQL means “no transactions.”

02Start with queries and invariants

Write the shop's requests and correctness rules first:

1. Product page: load one product and a bounded page of reviews. Do not assume every review can be embedded forever in one growing product record.
2. Purchase: accept an order only if the saved available quantity covers the requested quantity and that quantity is reserved for this order. Preserve one logical purchase across request retries.
3. Search: find matching products, then load current records before making freshness-sensitive purchase decisions.

A rule that must always hold, such as no negative stock after accepted allocation, is an invariant. An access pattern describes the keys, filters and ordering a request needs.

A join combines related records while reading. A secondary index maps a searchable field to matching records; it speeds selected reads but must be maintained on relevant writes.

For transactions, check which records can commit together and how concurrent updates are isolated. ACID transactions group changes with atomicity, consistency, isolation and durability guarantees; the later ACID chapter examines them. Neither a SQL interface nor a NoSQL label specifies the implementation's transaction participants by itself.

03Worked example: orders and browsing

Follow purchase request K8 for product 42:

1. Browsing returns display fields. A document or relational query can provide title, attributes and image references. Its cached stock hint is not a reservation.
2. The buyer submits account, product, quantity and K8. The service verifies identity and the requested operation.
3. The purchase service checks stock and records the purchase. In a supported transaction, check that available stock is at least the requested quantity, subtract it, and save the order, lines and K8's original request/result together. The stock check and subtraction must be one guarded change, so another buyer cannot spend the same units between them. If the required changes cannot share a transaction, explain an explicit reservation workflow rather than pretending independent writes are atomic.
4. A matching retry looks up K8. It returns the saved order. A different quantity under that key conflicts instead of silently creating another purchase.
5. Search receives product changes later. Even if it still displays one available item, checkout must use the current stock-allocation rule.

A relational schema might include Order(orderId,accountId,requestKey), OrderLine(orderId,productId,quantity) and Inventory(productId,available). A transactional document/key-value system can also work if its supported operations cover the same records and conditions. Putting display fields together does not extend a transaction to stock stored elsewhere.

Now let one product accumulate millions of reviews. Loading or updating one ever-growing object becomes expensive and concentrates traffic. Store review pages separately when needed. The correct choice follows the actual read and update, not a family-wide claim that one model makes all queries cheap.

04Choose the smallest defensible data architecture

Start with the smallest data architecture that meets the actual requirements:

• Use one capable familiar store when its queries, guarantees and growth limits fit. Do not add a second store merely because a different model has a favorable benchmark.
• Add specialized search only for a concrete need. It also adds delayed updates, permission checks, deletion handling, backup and repair work.
• For every copied view, identify its original records. State acceptable delay and how to rebuild the copy after loss.
• Test realistic popularity. One viral product can overload a partition in either family. Distributing unrelated records does not divide that one operation.
• Compare equal correctness. A faster option that allows an oversell does not meet the same purchase requirement.

Show record keys and one read/write trace before naming database products. Verify indexed queries, payload sizes, concurrent updates and cross-partition costs against the chosen implementation.