Beginner System design concept · How Systems Actually Work · 16 mins read

Data Storage Fundamentals

Choose storage based on data shape, consistency needs, and read-write patterns rather than database hype.

Open the interactive lesson See it in Design Quora

SQL vs NoSQL

Learn what actually drives the SQL vs NoSQL decision — data shape, relationships, consistency needs, and access patterns — and why 'NoSQL for scale' is a shallow interview answer.

Intuition

You need to store data for a new feature and someone asks: SQL or NoSQL? The wrong instinct is to treat it as a hype or scalability question — 'NoSQL is for big data, SQL is legacy.' Teams that pick a document store for highly relational data end up re-implementing joins in application code; teams that force flexible, rapidly evolving payloads into rigid relational schemas drown in migrations. The mismatch between data shape and database model creates pain that no amount of hardware fixes. The database choice shapes everything downstream: how you model entities, what queries are cheap or expensive, what consistency guarantees you get for free, and how painful schema changes are. Interviewers use 'SQL vs NoSQL' as a reasoning test — they want to hear you interrogate access patterns and relationships, not recite marketing labels. Getting this decision wrong early is one of the most expensive mistakes to reverse in production.

Mental Model

Start from the data, not the database. Ask four questions: (1) What shape is the data — uniform rows with fixed columns, or nested, varying structures? (2) How related is it — many entities connected by joins, or self-contained aggregates read and written whole? (3) What consistency do you need — multi-row invariants like 'money leaves A and arrives at B' that demand ACID transactions, or independent writes where each record stands alone? (4) What are the access patterns — ad-hoc queries with arbitrary filters, or a small set of known lookups by key? Relational databases win when relationships, invariants, and query flexibility dominate. NoSQL wins when data is self-contained, the schema evolves fast, and access patterns are a fixed set of lookups you can design the storage layout around. Think of it like: SQL is a warehouse with labeled shelves and a card catalog: everything has a fixed slot, and the catalog lets you find items by any attribute — but rearranging the shelving is a project. NoSQL is a set of specialized storage lockers: a key-value locker hands you exactly what you deposited when you show the claim ticket, a document locker stores each item in its own sealed box with whatever contents you like. Lockers are faster to deposit into and expand, but there is no catalog — you can only retrieve things the way the locker system was designed to retrieve them.

Building Blocks

Relational Model: Data is stored in tables of rows with a fixed, enforced schema, and entities are connected through foreign keys and joins. The schema guarantees every row has the expected shape, and the query engine can combine tables arbitrarily at read time. This is the model behind PostgreSQL, MySQL, and SQLite — optimized for correctness, relationships, and query flexibility.
Key-Value Stores: The simplest NoSQL model: an opaque value stored under a unique key, with get/put/delete as essentially the whole API. There are no queries over the value's contents — the database treats it as a blob. Redis, DynamoDB (at its core), and Memcached follow this model; it excels when every access is 'give me the thing with this ID'.
Document Stores: Values are structured documents (usually JSON) instead of opaque blobs, so the database can index and query fields inside them. Each document is a self-contained aggregate — an order with its line items embedded — read and written as a unit. MongoDB and Couchbase are the canonical examples; the model fits data that varies in shape and is accessed whole.
Wide-Column Stores: Data is organized as rows identified by a key, but each row can have a large, sparse, varying set of columns grouped in column families. Writes and reads are optimized for known access patterns laid out physically by row key. Cassandra and HBase follow this model, originally built for massive append-heavy workloads like time-series and messaging history.
Graph Databases: Entities are nodes and relationships are first-class edges with their own properties, so traversals ('friends of friends who like X') are native operations instead of recursive joins. Neo4j is the best-known example. The model exists because relationship-heavy queries that take many joins in SQL are single traversals in a graph.
Schema-on-Write vs Schema-on-Read: Relational databases enforce schema-on-write: every insert must conform to the declared table structure, catching bad data at the door. Most NoSQL systems use schema-on-read: anything goes in, and the application interprets structure when reading. The flexibility speeds iteration but moves the burden of data validation from the database to every reader.

Definitions

Schema

The declared structure of the data — tables, columns, and types in SQL; implicit or validated document shape in NoSQL.

In SQL the schema is enforced by the database on every write; violating rows are rejected.
In schemaless stores the schema lives in application code, so every version of every service must agree on it.
Schema migrations in SQL are explicit DDL operations; in document stores, 'migration' usually means handling multiple document versions at read time.

Join

A relational operation that combines rows from multiple tables based on related columns, computed at query time.

Joins let you store each fact once (normalization) and reassemble views as needed.
Document stores replace joins with embedding: related data is nested inside one document and fetched in a single read.
If you find yourself simulating joins in application code over a NoSQL store, that is a signal the data was relational all along.

Normalization

The relational practice of decomposing data into separate tables so each fact is stored exactly once, eliminating update anomalies.

A normalized design stores a customer's address once; a denormalized document copies it into every order.
Denormalization trades storage and update complexity for read speed — one fetch gets everything.
The copy problem: when denormalized data changes, every copy must be updated or readers see stale values.

Access Pattern

The specific set of queries your application actually performs — which keys, filters, sorts, and aggregations, at what rates.

NoSQL data models are designed backwards from access patterns: you list the queries first, then lay out keys and documents to serve them.
SQL lets you defer this: the query planner and indexes can support new query shapes after the fact.
Unknown or evolving access patterns are a strong argument for a relational default.

Aggregate

A cluster of related objects treated as a single unit for reads and writes — the natural document boundary in document and key-value stores.

An order with its line items is a classic aggregate: you almost always want the whole thing at once.
Document databases align their consistency boundary with the aggregate — single-document writes are atomic, multi-document writes historically were not.
Choosing aggregate boundaries is the document-model equivalent of schema design; get it wrong and every read stitches documents together.

ACID Transaction

A multi-operation unit of work with atomicity, consistency, isolation, and durability guarantees — standard in relational databases, limited or optional in many NoSQL systems.

Transactions let you maintain invariants across rows and tables, like debiting one account while crediting another.
Many NoSQL systems originally offered atomicity only within a single key or document; multi-record invariants had to be handled by the application.
Modern systems blur the line — MongoDB added multi-document transactions, and NewSQL databases offer SQL with distributed scale.

Patterns

Default to Relational Until Evidence Says Otherwise — Early-stage products and most CRUD applications, where access patterns are still being discovered and relationships between entities keep emerging.
Document Model for Self-Contained Aggregates — When entities are read and written whole, relationships between them are rare, and their internal structure varies by type or evolves quickly.
Key-Value for Lookup-by-ID Workloads — When the dominant (or only) access pattern is fetch-by-primary-key at high throughput — sessions, user profiles, feature flags, shopping carts.
Graph Model for Relationship-Centric Queries — When the core questions are about connections — shortest paths, neighborhoods, degrees of separation — rather than about the entities themselves.
Polyglot Persistence with a Clear System of Record — Mature systems where different subsystems genuinely have different data shapes, after the relational default stops fitting one of them.

Strategies

Access-Pattern-First Evaluation When: Whenever a storage decision is on the table, before any product names are mentioned. How: Write down the ten queries the application will actually run, with rough rates and latency expectations. For each candidate database, ask how each query is executed: primary-key lookup, index scan, full scan, or application-side join. The database that makes the common queries cheap and the rare ones possible — not the one with the best benchmark — wins. Example: A chat app lists its queries: fetch recent messages by conversation, fetch user presence, search message text. The first is a range read by key (fits wide-column or relational), presence is key-value, and text search needs a dedicated index — so one NoSQL store for everything would serve only one of the three patterns well.
Interrogate the Consistency Requirement When: When data involves money, inventory, quotas, or any multi-record invariant. How: Ask: what happens if two related writes half-complete? If the answer is 'a customer is charged without an order existing' or 'two users get the last item in stock', you need multi-record transactions and strong consistency — the historical strength of relational databases. If records are independent, weaker guarantees are acceptable and the NoSQL menu opens up. Example: A wallet service requires 'debit A and credit B' to be atomic; the team chooses PostgreSQL transactions for the ledger even though their activity feed — independent, append-only events — lives happily in a wide-column store.
Prototype the Query, Not the Benchmark When: When both models look plausible and you need evidence rather than opinion. How: Load a realistic slice of data into both candidates and run the actual application queries, including the awkward ones — reports, backfills, and the join a PM will request next quarter. Measure query complexity and code awkwardness, not just latency: a model where every new question requires a data pipeline is slower in calendar time even if it is faster in milliseconds. Example: A team benchmarking MongoDB vs PostgreSQL for an analytics-heavy feature discovers the document model needs a nightly ETL job for every new report, while the relational version answers new questions with a SQL query — they pick PostgreSQL despite MongoDB's faster single-document reads.
Design Document Boundaries Around Read Units When: When you have committed to a document store. How: Size each document to match what the application reads together: embed what is always fetched with the parent, reference what is sometimes needed separately. Watch the copy problem — any field embedded in multiple documents needs a plan for updates. Keep documents under size limits and avoid unbounded embedded arrays that grow forever. Example: A blog platform embeds comments' count and the latest three comments in each post document for the list view, but stores full comments in separate documents keyed by post ID — so the hot read is one fetch and comment growth never bloats the post document.

Why 'NoSQL for Scale' Is a Shallow Answer

The interview cliché says: use SQL until you hit scale, then switch to NoSQL. It survives because it contains a kernel of history — early NoSQL systems were built by companies (Google's Bigtable, Amazon's Dynamo) whose single-machine relational databases could not absorb their write volumes. But as a decision rule it fails three ways. First, modern relational databases scale far further than the cliché implies: a well-indexed PostgreSQL instance handles workloads most companies will never reach, and read replicas plus connection pooling cover the vast majority of growth. Second, scale is not one axis — you can have enormous data with trivial access patterns (key-value heaven) or modest data with brutal relational queries (graph or SQL territory); the volume alone tells you nothing about which model fits. Third, the choice is really about structure and guarantees: NoSQL systems historically traded away joins, multi-record transactions, and ad-hoc queries to make their storage layouts predictable — those tradeoffs hurt you every day whether or not you ever reach 'scale'. The answer interviewers reward names the actual drivers: data shape (uniform rows vs nested documents), relationship density (join-heavy vs self-contained aggregates), consistency requirements (multi-record invariants vs independent writes), and access patterns (ad-hoc vs known and fixed). Mention that the boundary is blurring — PostgreSQL's JSONB, MongoDB's multi-document transactions, NewSQL systems like CockroachDB — and that you would validate with real queries, and you have moved from reciting to reasoning.

Tradeoffs

Decision	Upside	Downside
Fixed Schema (SQL) vs Flexible Schema (Document Stores)	Fixed schemas catch malformed data at write time, make the database self-documenting, and let the query optimizer rely on known structure. Flexible schemas let different record types coexist, absorb evolving payloads without migrations, and speed up early iteration.	Fixed schemas make every structural change a migration, painful with large tables and multiple deploys. Flexible schemas push validation into application code, let inconsistent data accumulate silently, and make every reader handle every historical shape.
Joins and Normalization (SQL) vs Embedding and Denormalization (NoSQL)	Normalization stores each fact once, so updates are single-point and data cannot disagree with itself; joins assemble any view at read time. Embedding makes the common read a single fetch with no join cost and keeps related data physically together.	Joins get expensive as tables and row counts grow, and query performance depends on indexing discipline. Embedding duplicates data across documents, so updates must find every copy and readers may see stale values between updates.
Ad-Hoc Query Flexibility (SQL) vs Access-Pattern-Optimized Layout (NoSQL)	SQL answers questions you did not anticipate when designing the schema — critical for analytics, debugging, and product evolution. NoSQL layouts make the designed-for queries extremely fast and predictable by aligning physical storage with access patterns.	SQL's generality means unindexed queries degrade to full scans under load. NoSQL's specialization means a new access pattern can require denormalizing into new structures or scanning the entire dataset — schema redesign instead of a new query.
Built-In Transactions and Constraints (SQL) vs Application-Managed Invariants (NoSQL)	Relational databases enforce foreign keys, uniqueness, and multi-row transactions for you, making whole classes of bugs impossible. Skipping that machinery simplifies the database layer and can reduce contention on hot records.	Constraints and transactions add locking overhead and operational complexity under heavy write contention. Application-managed invariants scatter correctness logic across every service that touches the data — and the one service that forgets a check corrupts state.

Real World

System	How it's used
PostgreSQL (JSONB)	PostgreSQL is the canonical 'default relational' choice and blurs the boundary with JSONB columns that store and index semi-structured documents inside relational tables — letting teams keep joins and transactions for core entities while absorbing schema-less payloads where flexibility is genuinely needed.
MongoDB	MongoDB popularized the document model: self-contained JSON documents, indexes on nested fields, and schema that lives in the application. It thrives in catalogs, content management, and user-profile workloads where aggregates are read whole — and its later addition of multi-document transactions shows how much the pure 'NoSQL has no transactions' story has eroded.
Amazon DynamoDB	DynamoDB is a key-value and document store that forces access-pattern-first design: you declare a partition key (and optional sort key) up front, queries outside those keys require secondary indexes or scans, and single-item operations are its atomicity boundary. It powers Amazon's shopping cart — a pure lookup-by-key workload.
Apache Cassandra	Cassandra is a wide-column store descended from Bigtable and Dynamo, built for append-heavy, write-intensive workloads like time-series metrics and message history. Netflix famously used it for viewing-history data: huge write volume, reads by a known key, and no cross-record invariants.
Neo4j	Neo4j is the leading graph database, storing relationships as first-class edges so multi-hop traversals — fraud rings, recommendations, network dependency analysis — run as graph walks instead of cascades of SQL joins. It exists because relationship-centric questions are the case where the relational model genuinely struggles.

Interview

Questions interviewers ask

How do you decide between SQL and NoSQL for a new feature? Walk me through your decision process.
What are the four categories of NoSQL databases, and what access pattern is each one built for?
When would a document store be a bad choice even if the data is 'just JSON'?
People say NoSQL is for scale — do you agree? Why or why not?
How would you model an order with line items in a document store versus a relational database, and what breaks in each?

What a strong answer covers

Candidate should name the real decision drivers — data shape, relationship density, consistency and transaction needs, and known vs ad-hoc access patterns — before naming any product. They should describe all four NoSQL categories with a fitting workload each, explain the schema-on-write vs schema-on-read tradeoff, articulate the embedding-vs-joins copy problem, and dismantle 'NoSQL for scale' by separating data volume from data structure while noting that modern relational systems scale further than the cliché suggests.

Common traps

Answering 'NoSQL for scale, SQL for everything else' without interrogating data shape, relationships, or access patterns.
Claiming NoSQL has no transactions or SQL cannot store JSON — both boundaries have blurred (MongoDB multi-document transactions, PostgreSQL JSONB).
Recommending a document store for highly relational data, then hand-waving the joins that application code would have to simulate.
Ignoring the copy problem: embedding the same field in many documents without a plan for keeping the copies consistent.
Treating 'schemaless' as 'no design needed' — the schema moves into application code, where it is harder to enforce, not gone.

Quiz

Which factor should most directly drive a SQL vs NoSQL decision?

The total volume of data you expect to store
Data shape, relationship density, consistency needs, and access patterns
Which database has the best benchmark numbers
Whether the team prefers writing SQL

The model fit — uniform rows vs nested documents, join-heavy vs self-contained aggregates, invariants vs independent writes, ad-hoc vs fixed queries — determines daily pain far more than raw volume or benchmarks.

A workload consists almost entirely of 'fetch the session for this token' lookups. Which NoSQL category fits best?

Graph database
Wide-column store
Key-value store
Document store with embedded sessions

Pure get-by-key access with no need to query inside the value is exactly the key-value model (e.g., Redis); richer models add machinery the workload never uses.

Why is 'use NoSQL when you need scale' a weak answer?

NoSQL databases are actually slower than SQL at every scale
Scale is a legal requirement, not a technical one
Relational databases cannot use indexes at large scale
Volume alone says nothing about data structure or access patterns, and modern relational databases scale much further than the cliché implies

Huge data with simple key lookups differs completely from modest data with heavy joins; the decision hinges on structure and guarantees, and PostgreSQL-class systems already cover most companies' scale.

What is the 'copy problem' introduced by denormalization and embedding in document stores?

The same fact stored in many documents must be updated everywhere, or readers see stale values
Documents cannot be copied between servers
Backups take twice as long because documents are large
Indexes must be copied to every collection

Embedding duplicates data for read speed; when the underlying fact changes, every embedded copy needs updating, and any missed copy serves stale data — the update anomaly normalization exists to prevent.

When is a graph database genuinely the right tool rather than a preference?

Whenever the application has users
When core queries are multi-hop relationship traversals like 'friends of friends' or fraud-ring detection
When the data is too large for PostgreSQL
When documents need to reference other documents

Graph databases make relationships first-class edges, so neighborhood and path queries are native traversals; in SQL these become expensive recursive joins, which is the specific pain the model solves.

Indexing and Query Performance

Learn how B-tree indexes turn table scans into logarithmic lookups, why indexes speed up reads but slow down writes, and how composite index column order decides which queries an index can serve.

This section is part of the full PRISM roadmap, with worked examples, trade-off tables, interview questions and a quiz.

Unlock the full lesson

ACID and Transactions

Learn what each ACID property actually guarantees, how isolation levels trade correctness for concurrency, and the concrete anomalies — dirty reads, lost updates, partial writes — that appear when transactions are missing.