When you start a design, how do you decide between a relational database and a NoSQL store, and how does that choice flow from the data model and access patterns rather than the other way around?

system-design · Staff-principal level · software-engineering

What the interviewer is really asking

Tests data-modeling maturity — that the candidate models around access patterns and consistency/transaction needs, picks the store to fit them (including 'relational is fine'), and resists cargo-culting NoSQL for scale it doesn't need.

What to say

• Start from access patterns and invariants, not the tech: enumerate the queries (point lookups, range scans, aggregations, multi-entity joins), the read:write ratio, and the consistency/transaction requirements (do you need ACID across entities?). The store should be chosen to serve those, so the data model comes first.
• Map needs to families: relational (Postgres) when you have rich relationships, ad-hoc queries, and need transactions/strong consistency — and note it scales far further than people assume; document/wide-column NoSQL (DynamoDB, Cassandra) when access is a few known key-based patterns at very high scale and you can denormalize and pre-join into the model; specialized stores (search, time-series, graph) when the access pattern is itself specialized.
• Acknowledge the trade-offs honestly: NoSQL buys horizontal scale and predictable latency by demanding you model for specific queries up front (single-table design, denormalization) and giving up joins and often multi-item transactions; polyglot persistence (right store per workload) is valid but each store is operational cost. Default to relational unless a requirement forces otherwise.

What to avoid

• Pick NoSQL reflexively 'because it scales', without evidence the workload needs it or that relational can't handle it — cargo-culting scale you don't have.
• Choose the store first and then bend the data model and access patterns to fit it, instead of modeling the access patterns and choosing the store to serve them.
• Ignore the consistency/transaction requirement — reaching for an eventually-consistent store when the domain genuinely needs ACID across entities (payments, inventory) is a correctness bug, not a scaling trade-off.

Example answers

Strong: I model the access patterns before I name a database: what are the exact queries, the read:write ratio, and do I need transactions across entities. If the domain has rich relationships and ad-hoc querying — and especially if it needs ACID, like payments — I default to Postgres, because relational scales much further than its reputation and the query flexibility is worth a lot early on when patterns are still changing.

Weak: I'd use a NoSQL database like MongoDB because it scales horizontally and doesn't need a fixed schema, which is more flexible as the product grows.