SQL vs NoSQL: Picking the Right Data Model

Relational databases (SQL)

A relational database keeps data in tables (rows and columns). The table has a fixed schema. A schema is the plan of the data: the column names and the type of each column. Tables are linked by keys. A key is a column value that identifies a row or points to a row in another table (see Lesson 5). Here, orders.user_id points to users.id.

users                         orders
┌────┬───────┐                ┌────┬─────────┬────────┐
│ id │ name  │                │ id │ user_id │ total  │
├────┼───────┤                ├────┼─────────┼────────┤
│ 42 │ Asha  │◄───────────────│ 7  │ 42      │ 1,299  │
└────┴───────┘                └────┴─────────┴────────┘

Strengths:

• Joins: a join combines rows from two tables in one query. You can ask questions you did not plan for.
• Transactions (ACID): a transaction is a group of changes that either all succeed or all fail. ACID is the set of safety promises for transactions (see Lesson 21).
• Constraints: rules that the database enforces for you. Examples: emails must be unique, and a foreign key (a column that points to a row in another table) must point to a row that exists.
• SQL: a powerful, standard language for asking questions of the data. Most developers know it.
• Mature tools: backups, monitoring, ORMs (libraries that let you use tables as objects in your code) and decades of experience.

Weaknesses:

• Schema changes on huge tables need care.
• Scaling writes beyond one machine is hard. You must split the data across machines (sharding, Lesson 25).
• Joins across many machines are slow and costly.

Examples: PostgreSQL, MySQL, SQL Server, Oracle, SQLite.

Key-value stores

This is the simplest model. A key maps to a value, like a word in a dictionary. The database does not look inside the value.

"session:abc123"  →  {"userId": 42, "expires": "..."}"cart:42"         →  [991, 405, 17]"rate:ip:1.2.3.4" →  87

• ✅ Extremely fast and easy to scale (you just spread the keys across machines).
• ❌ You can only look things up by key. "Find all carts that contain product 991" is not possible without extra work.

Examples: Redis, Amazon DynamoDB (key-value plus some document features), Memcached, etcd. Good for: sessions (login state), caches, shopping carts, feature flags (on/off switches for features), counters.

Document databases

Each record is a document. A document is usually JSON-like and can contain nested data (objects inside objects). A collection is a group of documents, like a table. Documents in the same collection do not need the same fields.

{  "_id": "order_7",  "user": { "id": 42, "name": "Asha" },  "items": [    { "productId": 991, "name": "Running shoes", "qty": 1, "price": 1299 }  ],  "status": "shipped",  "shippingAddress": { "city": "Pune", "pin": "411001" }}

• ✅ Data that is read together is stored together. One read fetches the whole order, with no joins.
• ✅ A flexible schema is easy to change over time, and it fits JSON APIs well.
• ❌ Weaker at joins and at relationships between documents.
• ❌ Copied data (like the user's name inside every order) must be kept in sync. If the name changes, you must update every copy.
• ❌ "Schemaless" really means the schema lives in your code. Inconsistent data can slowly get into the database.

Examples: MongoDB, Couchbase, Firestore, Amazon DocumentDB. Good for: product catalogues (different products have different attributes), content management (CMS), user profiles, event data.

Wide-column databases

These look like tables, but they are built to be spread across many machines and to handle a huge number of writes. Two keys organise the data. The partition key decides which machine holds a row. The clustering key decides the order of rows inside that partition. All rows with the same partition key stay together.

Partition key: channel_id     Clustering key: message_time (newest first)
┌─────────────┬─────────────────────┬──────────┬──────────────┐
│ channel_id  │ message_time        │ user     │ text         │
├─────────────┼─────────────────────┼──────────┼──────────────┤
│ general     │ 2026-12-07 10:02:11 │ asha     │ "hi all"     │
│ general     │ 2026-12-07 10:01:55 │ ravi     │ "morning!"   │
└─────────────┴─────────────────────┴──────────┴──────────────┘

• ✅ Massive scale and very fast writes. In Cassandra's design there is no single master (no one main node that all writes must go through).
• ✅ Great for data in time order for each key: messages per channel, readings per sensor, events per user.
• ❌ You must design tables around your queries. Queries that do not use the partition key are slow or not allowed.
• ❌ No joins, and limited transactions.

Examples: Apache Cassandra, ScyllaDB, HBase, Google Bigtable. Good for: chat messages, IoT (internet-connected device) sensor data, activity feeds, logs at huge scale.

Graph databases

A graph database stores data as nodes (things, such as people) and edges (the links between them, such as "follows"). Following links is fast, even many steps deep.

(Asha) ──FOLLOWS──► (Ravi) ──FOLLOWS──► (Meera)
   │                                       ▲
   └──────────LIKES──► (Post 17) ◄─WROTE───┘

Take this question: "people Asha might know: friends of her friends whom she does not follow yet". It is simple in a graph database. In SQL it needs many self-joins (joining a table to itself again and again), which is painful.

Examples: Neo4j, Amazon Neptune. Good for: social networks, recommendation engines, fraud detection (finding groups of linked accounts), knowledge graphs.

Other specialised databases

• Time-series (TimescaleDB, InfluxDB, Prometheus): built for values that change over time, such as metrics and sensor data. They compress data well and answer time-based queries fast.
• Search engines (Elasticsearch, OpenSearch): full-text search (Lesson 19).
• Analytics / columnar (ClickHouse, BigQuery, Snowflake): they store data by column. This makes huge aggregations (sums, averages, counts) over billions of rows fast.
• Vector databases: find items that are similar in meaning by comparing embeddings (lists of numbers made by a machine-learning model). See Lesson 19.

Schema-on-write vs schema-on-read

• Schema-on-write (SQL): the database checks the data when you write it. Wrong types or missing fields are rejected.
• Schema-on-read (most NoSQL): anything can be written. Your code checks and understands the data when it reads it. It must handle old and unusual formats.

Neither one means "no schema". The real question is who enforces the schema: the database or your application.

Two ways to design your data

Entity-first (typical SQL). Model the real-world things (users, orders, products) and their relationships. Keep the data normalised (each fact stored once; see Lesson 23). Then write whatever queries you need. It is flexible for questions nobody has asked yet.

Access-pattern-first (typical NoSQL). First, list the exact queries ("get order by ID", "get a user's last 20 orders", "get messages in a channel, newest first"). An access pattern is one such way of reading data. Then design the storage so each query is a single, fast lookup. It is very fast at scale. But new kinds of queries may need new tables or extra copies of data.

With DynamoDB, some teams go as far as "single-table design". They keep many kinds of items (users, orders and so on) in one table. They choose the keys with care, so related items are stored next to each other.

Myths to drop

• "NoSQL scales, SQL doesn't." Relational databases handle very large workloads on one powerful machine plus read replicas (extra copies that serve reads). They can also be sharded (Lesson 25). Many huge companies run on MySQL or PostgreSQL.
• "NoSQL is schemaless, so it is faster to build with." It is faster at the start. Later, inconsistent data can slow you down.
• "SQL can't do JSON." PostgreSQL's JSONB type stores JSON in a binary form that can be indexed. It works very well for JSON documents. So you can mix relational and document styles in one database.
• "You must choose one." Many systems use several databases, each for the job it does best. This is called polyglot persistence (polyglot means "many languages").

NewSQL / distributed SQL

A newer group of databases wants SQL + transactions + horizontal scale (adding more machines). They split the data across machines for you. Still, they look like one SQL database to your code.

Examples: Google Spanner, CockroachDB, YugabyteDB, TiDB.

The trade-offs: they are harder to run. Some writes are slower (latency is higher), because machines must agree with each other first. They can also cost more.

A quick comparison