When would you choose SQL vs NoSQL?

// The same shop stored two ways: normalized tables (relational) vs one document per order.
let seed = 42;
function rand(n) { seed = (seed * 48271) % 2147483647; return seed % n; } // deterministic MINSTD

// Relational: four tables, each a Map from primary key to row.
const users = new Map(), products = new Map(), orders = new Map(), items = [];
for (let u = 1; u <= 200; u++) users.set(u, { id: u, name: `user${u}` });
for (let p = 1; p <= 50; p++) products.set(p, { id: p, name: `product${p}`, stock: 100 });
for (let o = 1; o <= 1000; o++) {
  orders.set(o, { id: o, userId: 1 + rand(200) });
  const n = 1 + rand(4);
  for (let k = 0; k < n; k++) items.push({ orderId: o, productId: 1 + rand(50), qty: 1 + rand(3) });
}
const itemsByOrder = new Map(); // a secondary index on order_items(order_id)
for (const it of items) {
  if (!itemsByOrder.has(it.orderId)) itemsByOrder.set(it.orderId, []);
  itemsByOrder.get(it.orderId).push(it);
}

// Document store: each order embeds its user name and product names (denormalized).
const docs = new Map();
for (const [id, o] of orders) {
  docs.set(id, {
    _id: id, userId: o.userId, userName: users.get(o.userId).name,
    items: itemsByOrder.get(id).map((it) => ({ productId: it.productId, name: products.get(it.productId).name, qty: it.qty })),
  });
}
console.log(`Data: ${users.size} users, ${products.size} products, ${orders.size} orders, ${items.length} order lines`);

console.log("\n1) Render the page for order 17");
const lines = itemsByOrder.get(17);
const rowsRead = 1 + 1 + lines.length + lines.length; // order + user + its lines + one product per line
console.log(`  relational: ${rowsRead} row lookups across 4 tables (a join)`);
console.log(`  document:   1 read of order 17 (${docs.get(17).items.length} lines embedded)`);

console.log("\n2) Rename product 7");
let docsTouched = 0;
for (const d of docs.values()) {
  let hit = false;
  for (const it of d.items) if (it.productId === 7) { it.name = "product7-v2"; hit = true; }
  if (hit) docsTouched++;
}
products.get(7).name = "product7-v2";
console.log("  relational: 1 row updated; every join sees the new name at once");
console.log(`  document:   ${docsTouched} order documents rewritten (or the old name stays as a snapshot)`);

console.log("\n3) Checkout of 2 x product 3, crash after the stock write, before the order write");
function checkout(useTransaction) {
  const db = { stock: 100, orders: 0 };
  const steps = [(d) => { d.stock -= 2; }, (d) => { d.orders += 1; }];
  const staged = []; // with a transaction, writes become visible only at commit
  try {
    for (let i = 0; i < steps.length; i++) {
      if (i === 1) throw new Error("process crashed");
      if (useTransaction) staged.push(steps[i]); else steps[i](db);
    }
    for (const w of staged) w(db); // commit
  } catch (e) {
    staged.length = 0; // never committed, so rolled back
  }
  return `stock=${db.stock} orders_written=${db.orders}`;
}
console.log(`  single-row writes, no transaction: ${checkout(false)}  <- 2 units lost`);
console.log(`  one ACID transaction:              ${checkout(true)}  <- all or nothing`);

console.log("\n4) Spread over 4 shards; how many shards does the order 17 page touch?");
const shard = (key) => key % 4;
const touched = new Set([shard(17), shard(orders.get(17).userId)]);
for (const it of lines) touched.add(shard(it.productId));
console.log(`  relational, each table sharded by its own id: ${touched.size} shards`);
console.log(`  document, sharded by order id:               1 shard`);

console.log("\n5) Ad-hoc question nobody planned for: units sold per product, top 3");
const units = new Map();
for (const it of items) units.set(it.productId, (units.get(it.productId) || 0) + it.qty);
const top = [...units.entries()].sort((a, b) => b[1] - a[1] || a[0] - b[0]).slice(0, 3);
console.log(`  SQL GROUP BY over ${items.length} lines: ${top.map(([p, u]) => `product${p}=${u}`).join(" ")}`);
console.log(`  key-value store with no secondary index: read all ${docs.size} documents, or keep a counter from day one`);