How do you choose between SQL and NoSQL in a system design?

The database choice follows the data model and query pattern, not fashion. A relational database gives a mature query planner, joins, constraints, transactions and secondary indexes. A NoSQL store often gives simpler horizontal partitioning and a schema shaped around one access path, at the cost of ad hoc queries and cross-entity transactions.

Common NoSQL families

• Key-value: simple lookups by key, very fast, with limited query flexibility
• Document: nested objects and per-document reads, good for aggregates with varied shape
• Wide-column: high write throughput and range scans within a partition key
• Graph: traversals across highly connected data, less suitable for bulk aggregation
• Search engine: inverted indexes for text and faceted queries, usually a secondary store

Ask what the consistency requirement is. If the operation needs a multi-row invariant, such as transferring funds or claiming a unique username, a transactional store is simpler and safer. If the data is append-only events or a per-user document, a partitioned store can scale more naturally. If the dominant query is full-text search, an inverted index is the right tool even when the source of truth is relational.

It is normal to use more than one store. Keep a relational source of truth for money, a cache for hot reads, an object store for blobs and a search index for text. The hard part becomes keeping them in sync, which usually means an outbox or change data capture plus idempotent consumers.