Lesson 1 · Data Storage and Management Strategies

Selecting Relational vs NoSQL Database Models

Two philosophies for storing data — integrity-first structure versus flexible, horizontally scalable performance.

Big Idea

SQL and NoSQL aren’t ‘old vs new’ or ‘slow vs fast’, they’re different bets about which is more expensive to give up: strict structure and cross-table consistency, or flexible schema and effortless horizontal scale.

Relational databases (RDBMS) are built on the mathematical foundation of set theory and the relational model, prioritizing data integrity and consistency through strict schema enforcement.

NoSQL databases (Not Only SQL) trade off that rigid consistency for horizontal scale, flexible data structures, and optimized read/write performance for specific data access patterns.

Think Of It Like

A Filing Cabinet Or A Shelf Of Boxes

A relational database is like a well-organized filing cabinet with strict labeled folders, every document has to fit a defined form, but you can cross-reference any folder against any other instantly and trust the filing is internally consistent.

A NoSQL database is more like a set of labeled boxes where each box can hold whatever shape of thing makes sense for what’s in it, faster to just toss something in without redesigning the whole cabinet, but you give up the guarantee that everything follows one strict, cross-checkable format.

Model 01

RelationalIntegrity First

Relational databases like PostgreSQL or MySQL rely on a predefined schema. Every row in a table must adhere to the same structure, and relationships between tables are enforced via Foreign Keys. This structure is ideal for transactional integrity ( ) where you cannot afford to lose data or leave it in an invalid state.

Consider a banking application where you move money between two accounts. You need to ensure that the debit from Account A and the credit to Account B both succeed, or both fail.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the system crashes halfway through, the database engine uses the transaction log to roll back the changes, ensuring no money vanishes into thin air.

Use RDBMS when your data has clear, predictable relationships and your primary requirement is avoiding anomalies.

Model 02

NoSQLScaling and Performance

NoSQL databases — such as MongoDB (Document), Cassandra (Wide-Column), or DynamoDB (Key-Value) — are designed for scenarios where the "one size fits all" schema of an RDBMS becomes a bottleneck. By relaxing the requirement for complex joins and strict cross-table constraints, these systems can distribute data across many nodes more easily.

If you are building a system that tracks user session data or real-time sensor readings, your schema might change frequently as new features are added. A document store allows you to store a flexible JSON object:

// User Profile Document
{
  "user_id": "u123",
  "preferences": {
    "theme": "dark",
    "notifications": ["email", "push"]
  },
  "last_login": "2023-10-27T10:00:00Z"
}

Because the database doesn’t care about the internal structure of that document, you can add new fields (like ai_assistant_enabled) without executing an ALTER TABLE command that locks your database for hours on a billion-row table.

Key Terms

The Vocabulary Of The Trade-off

ACID

Atomicity, Consistency, Isolation, Durability, the four guarantees a relational transaction typically provides, ensuring data stays correct even under concurrent access or a crash.

Schema

The defined structure (tables, columns, types, relationships) that data must conform to, strictly enforced in relational databases and loosely enforced or unenforced in most NoSQL databases.

JOIN

A relational query operation that combines rows from two or more tables based on a related column, the mechanism that makes normalized, non-duplicated data practical to query.

Document store

A NoSQL database that stores data as flexible, JSON-like documents rather than fixed rows, allowing different records in the same collection to have different fields.

Polyglot persistence

Using multiple different databases within one system, each chosen for the access pattern it fits best, instead of forcing every use case into a single database.

Reference

Deciding Between Models

The choice is rarely about "which technology is better" and always about "what constraints are you trying to satisfy."

Requirement	Prefer Relational (RDBMS)	Prefer NoSQL
Data Integrity	High (Financial / Auth)	Eventual Consistency
Schema	Rigid / Structured	Flexible / Dynamic
Relationships	Complex Joins	Denormalized / Embedded
Scale	Vertical (Scale-up)	Horizontal (Scale-out)

Putting It Together

Choosing SQL vs NoSQL By Access Pattern

The first question is the only one that decides SQL or not. Everything below it is choosing which NoSQL, which is why “we’ll use NoSQL” is an unfinished answer: it names the branch, not the destination.

When data grows beyond a single instance, your choice of database dictates how you handle future complexity. In upcoming modules, we’ll examine how to apply partitioning and sharding to these models to manage massive datasets.

Seen In The Wild

How Real Systems Actually Choose

▪

Banks and payment processors like Stripe run their core ledger on relational databases (typically PostgreSQL or a similar system) specifically because ACID guarantees are non-negotiable when money is involved.

▪

Amazon built DynamoDB, a key-value/document NoSQL store, to handle its shopping cart and product catalog at a scale where relational sharding became a bigger operational burden than giving up cross-table JOINs.

▪

Facebook uses a graph-shaped data model (originally TAO, built on top of MySQL) because the social graph, who's friends with whom, is naturally a traversal problem that graph-style access patterns fit better than deeply normalized relational tables.

▪

Discord migrated parts of its message storage to Cassandra, a wide-column store, specifically to handle enormous write volume across billions of messages that would have required extensive sharding to sustain on a single relational cluster.

Key Points

What To Carry Forward

✓

SQL means fixed schema, strong relational guarantees (ACID), and native support for JOINs across tables.

✓

NoSQL is an umbrella term covering document, key-value, wide-column, and graph databases, each with a different natural shape and access pattern.

✓

NoSQL's schema flexibility moves consistency responsibility from the database to the application, it doesn't remove the need for structure.

✓

NoSQL databases are usually built for horizontal scale from the start, which is the real reason they often win at very high write volume, not raw per-query speed.

✓

The right choice depends on the access pattern: relational data with strict consistency needs fits SQL, high-volume flexible-shape data with simple access patterns often fits NoSQL better.

✓

Polyglot persistence, using different databases for different parts of one system, is normal at real scale, not a sign of indecision.

Common Mistakes

Where This Usually Goes Wrong

Choosing NoSQL purely because it sounds more scalable, without checking whether the actual access pattern needs relational JOINs and strict consistency.

Assuming a flexible schema means no schema, when in practice the application still needs a consistent shape, it's just unenforced by the database.

Believing relational databases can't scale, when in reality read replicas, connection pooling, and even sharding can take a well-designed relational system very far before it becomes the bottleneck.

Using a single NoSQL database for everything in a system, including data that's deeply relational, instead of considering polyglot persistence for the parts that genuinely need different guarantees.

“I tried filing my toys in flexible unlabeled boxes once. Efficient at throw-in time, a nightmare at find-it-again time. Turns out that’s the whole SQL versus NoSQL argument in miniature.”
Madhumitha Kolkar·Index 0

Try It Yourself

Two Features, Two Shapes

Pick an app you use daily and guess its data model for two features: the core account and billing data (would you want strict consistency there?) and its activity feed or notifications (does that need cross-entity JOINs, or mostly fast lookups by a known key?). Notice how the two features probably want different database shapes, even inside the same product.

Practice

Exercises

Summary

Structure vs. Flexibility

Relational databases prioritize the correctness of complex data relationships, while NoSQL databases prioritize performance and flexibility for specific, high-velocity use cases. Choosing between them requires a clear understanding of your read/write patterns and the degree of consistency your system demands. We’ll build on this by exploring how to distribute these datasets effectively in the next lesson.

Quiz Review

Check your understanding

Question 1 of 10

A candidate says 'we'll use NoSQL because it's more scalable.' What follow-up question exposes whether they actually understand the trade-off?

✓Ask what access pattern the data has: does the system need to join this data against other entities, and does it need strict cross-record consistency? If the answer is yes to either, NoSQL's scalability advantage may come at the cost of pushing hard consistency and relational logic into the application layer, which can be a worse trade than a well-sharded relational database.

The answer comes down to how many round-trips it takes to reconstruct the shape you’re actually going to render.

The Relational Approach

A normalized comments table stores each reply as a row with a parent_comment_id foreign key. Rendering a full nested thread means a recursive query or multiple joins to walk from the root comment down through every reply, and every reply-to-a-reply.

The Document Approach

A document store can embed the entire thread — root comment plus nested replies — as one nested JSON structure under a single thread_id. Fetching the thread is one read of one document, with the nesting already shaped exactly how the UI will render it.

The Trade-off You're Accepting

Embedding makes reads cheap but makes updates to a single deeply-nested reply more awkward, and duplicates data if the same comment needs to appear in more than one context. It's a bet that reads (viewing threads) vastly outnumber writes (posting a reply) — usually true for social platforms.

Model data around how it’s read, not just how it’s created. The document store isn’t "better" here — it’s shaped to match the access pattern.