Databases — SQL vs NoSQL

Choosing the data store is a core system-design decision. SQL (relational) and NoSQL each fit different needs, and interviewers want you to justify the choice by the data shape, consistency needs, and scale — not by fashion.

SQL vs NoSQL

	SQL (relational)	NoSQL
Model	Tables, fixed schema, relations	Document / key-value / column / graph
Consistency	Strong (ACID)	Often eventual (BASE), tunable
Scaling	Vertical mostly; sharding is hard	Horizontal by design
Best for	Transactions, complex queries, joins	Huge scale, flexible schema, high write throughput
Examples	PostgreSQL, MySQL	MongoDB, Cassandra, DynamoDB, Redis

Exam Tricks & Tips

• 🎯 Pick SQL when you need transactions and joins — payments, orders, anything needing ACID.
• 🎯 Pick NoSQL for scale + flexible schema — feeds, logs, catalogs, high write volume.
• 🎯 Match the NoSQL type to the access pattern — key-value (Redis), document (Mongo), wide-column (Cassandra), graph (Neo4j).
• 🎯 Justify by data shape and query pattern, not "NoSQL is web-scale."
• 🎯 You can use both (polyglot persistence) — SQL for orders, Redis for sessions, search index for text.
• ❌ Common mistake: defaulting to NoSQL for everything — most CRUD apps are served perfectly by a relational DB.

Expected pattern: Every design round asks "which database and why?"; strong answers tie the choice to consistency and query needs.

Quick recap: SQL for ACID/joins/transactions; NoSQL for horizontal scale + flexible schema. Choose by data shape and access pattern; polyglot is fine.

Cover the answer, recall, then check. 11 cards on SQL vs NoSQL.

Q1. When should you choose SQL?
A1. When you need transactions, joins, and strong ACID consistency (payments, orders).

Q2. When should you choose NoSQL?
A2. For huge scale, flexible schema, and high write throughput (feeds, logs, catalogs).

Q3. How does NoSQL scale vs SQL?
A3. NoSQL scales horizontally by design; SQL scales vertically and shards with difficulty.

Q4. SQL consistency model vs NoSQL?
A4. SQL: strong (ACID). NoSQL: often eventual (BASE), sometimes tunable.

Q5. Match NoSQL types to examples.
A5. Key-value: Redis; document: MongoDB; wide-column: Cassandra; graph: Neo4j.

Q6. How should you justify the DB choice?
A6. By data shape, query/access pattern, and consistency needs — not by fashion.

Q7. What is polyglot persistence?
A7. Using multiple stores together — e.g., SQL for orders, Redis for sessions, a search index for text.

Q8. Common database-choice mistake?
A8. Defaulting to NoSQL for everything when a relational DB serves most CRUD apps well.

Q9. Data model of SQL vs document NoSQL?
A9. SQL: tables with fixed schema and relations; document: flexible JSON-like documents.

Q10. Give two SQL database examples.
A10. PostgreSQL and MySQL.

Q11. Which store fits payments, and why?
A11. SQL — it needs ACID transactions for correctness.

"Would you use SQL or NoSQL here?" comes up in almost every system-design interview, and the wrong instinct is to pick a favourite. The right instinct is to choose based on the data and access patterns, and to justify it. Candidates who can articulate when each shines — and admit that many real systems use both — stand out immediately.

What this covers / why it matters: the difference between relational (SQL) and non-relational (NoSQL) databases, when to use each, and how to justify the choice in a design. It's a near-certain question with a clear right way to answer.

The approach

Beginner — the fundamental difference

• SQL (relational): structured data in tables with a fixed schema and relationships; strong consistency and ACID transactions; queried with SQL and joins. Examples: PostgreSQL, MySQL.
• NoSQL (non-relational): flexible schema, several models — document (MongoDB), key-value (Redis, DynamoDB), wide-column (Cassandra), graph (Neo4j); typically built to scale horizontally and favour availability/performance, sometimes trading strict consistency.

Intermediate — when to use which

Prefer SQL when	Prefer NoSQL when
Data is structured with clear relationships	Schema is flexible or evolving
You need multi-row transactions / strong consistency (payments, orders)	You need massive scale and high write throughput
Complex queries and joins matter (reporting)	Access is simple key-based lookups
Data volume is moderate	Data is huge and must shard across many nodes

Advanced — polyglot persistence and justifying the trade-off

Mature systems use both — "polyglot persistence": e.g. PostgreSQL for orders and payments (needs transactions), Cassandra for a high-volume activity feed, Redis for sessions/cache, Elasticsearch for search. The strong interview move is to tie the choice to consistency needs and access patterns: "This data needs transactional integrity, so relational; that data is huge and write-heavy with simple lookups, so a wide-column NoSQL store." Also connect it to CAP: NoSQL stores often lean AP (availability + partition tolerance) with eventual consistency, while relational systems lean CP.

Worked example

"Design the data storage for an e-commerce site."

"I'd use different stores for different needs rather than forcing one. Orders, payments and inventory go in a relational database like PostgreSQL — these need ACID transactions (you can't half-charge a customer) and involve relationships and reporting queries with joins. The product catalog could stay relational too, or move to a document store like MongoDB if product attributes vary a lot by category, since a flexible schema fits that well. User sessions and the cart go in Redis (a key-value store) for fast, ephemeral access. A high-volume activity feed or clickstream — huge write throughput, simple access, tolerant of eventual consistency — fits a wide-column store like Cassandra. And product search goes to Elasticsearch. So the answer isn't SQL or NoSQL; it's SQL where I need transactions and relationships, NoSQL where I need scale and flexibility, chosen per data type."

Justified per-component choices tied to consistency and access patterns — exactly the reasoning interviewers want.

What interviewers look for

• Choice driven by requirements, not personal preference.
• Transactions/consistency correctly mapped to relational.
• Scale/flexibility correctly mapped to NoSQL.
• Polyglot persistence — using the right store per data type.

Do's and don'ts

• Do justify the choice from data structure, consistency, and access patterns.
• Do mention that real systems often mix both.
• Don't say "NoSQL is more modern/better" — it's about fit.
• Don't put money/orders in an eventually-consistent store.
• Mnemonic: CAST — Consistency & transactions → SQL; huge scale & flexible schema → NoSQL.

Worked Example

Problem/Question: "For a new social app, would you pick a SQL or a NoSQL database? Justify."
Solution/Model answer:
SQL (relational, e.g., PostgreSQL):

• Strengths: strong ACID guarantees, complex joins, structured schema, mature tooling — ideal where consistency and relationships matter (payments, user accounts).
• Weaknesses: horizontal scaling and schema changes are harder.
  NoSQL (e.g., MongoDB document, Cassandra wide-column, Redis KV):
• Strengths: flexible/evolving schema, easy horizontal scaling, high write throughput, good for unstructured or huge-volume data (feeds, logs, sessions).
• Weaknesses: weaker/eventual consistency, limited joins, app must handle more data integrity.
  Reasoned choice (often hybrid — "polyglot persistence"): "I'd use PostgreSQL for core relational data (users, relationships, payments) where consistency matters, and a NoSQL store like Cassandra or Redis for the high-volume, denormalised feed/activity data and caching. The right answer depends on access patterns, not fashion."
  Answer/Takeaway: Choose by access pattern and consistency needs: SQL for structured, relational, transaction-critical data; NoSQL for flexible schemas, huge volume and easy horizontal scaling. Real systems often mix both (polyglot persistence) rather than picking one dogmatically.