# SQL or NoSQL — System Design Interview Prep

Source: https://www.skillbyai.com/en/system-design-interview/d-choice

> Choose by access patterns.

## Relational, key-value, document, wide-column

Relational databases (PostgreSQL, MySQL) offer transactions, joins and constraints, and scale further than many expect with read replicas and partitioning. **Key-value** stores (Redis, DynamoDB) give fast lookups by key; **document** stores (MongoDB) suit flexible nested records; **wide-column** stores (Cassandra) handle very high write throughput with partition keys designed around queries. Justify the choice by access patterns, consistency needs and scale, not fashion.

## Choosing, splitting and copying data

Database choice, sharding, replication and ID generation decide how far a design can scale.

![Four ideas: SQL versus NoSQL, sharding, replication, unique IDs.](assets/figures/system-design-interview/section-4-map.svg) — Figure 4.1 — Storage choice, sharding, replication and IDs.

## Matching storage to the problem

Typical choices, each with alternatives.

```text
payments, orders, inventory      -> relational (ACID transactions, constraints)
URL code -> long URL lookups      -> key-value (simple get by key, huge scale)
user profiles with nested data    -> document or relational with JSON columns
chat messages, activity events    -> wide-column (partition by conversation/user, time-sorted)
full-text product search          -> search index (Elasticsearch/OpenSearch) next to the main DB
analytics over billions of rows   -> columnar warehouse (BigQuery, ClickHouse, Snowflake)
```

## Mention the access pattern first

Say "we always look up by code" before saying "so a key-value store fits".

**Quiz:** Which store best fits money transfers between accounts?

- [x] A relational database with ACID transactions
- [ ] A cache without persistence
- [ ] A search index
- [ ] A CDN

*Answer:* A relational database with ACID transactions. Correctness needs transactions.