---
title: 'Relational vs. Non-Relational Selection'
source: 'https://academia.sh/en/courses/relational-theory/relational-vs-non-relational-selection'
course: 'Data Modeling and Relational Theory'
language: en
updated: '2026-08-23T07:00:47+00:00'
license: 'CC BY-SA 4.0'
---

# Relational vs. Non-Relational Selection

What non-relational data model families give up and what they give in return, document storage's measured trade-off, the schema flexibility fallacy, and the decision criteria for the choice.

The previous lesson named the promises a relational database makes. That same naming
makes a comparison possible: some systems give up part of these promises and offer other
things in return. This lesson's question is: which data and which access pattern require
the relational model, when is another data model a better fit, and what criteria is that
decision made on?

## What Gets Given Up

The relational model gives four things: declarative querying independent of the data,
constraints enforced in the schema, arbitrary joins, and transaction guarantees.
Non-relational systems give up part of these. The reason for giving them up is usually
one of three: distributing the data across multiple machines, driving a single access
pattern down to a very low cost, or serving a domain whose schema is not known in
advance.

The decision is therefore not the question "which is better." The question is: which of
the guarantees being given up does this task actually need?

## Data Model Families

A **key-value store** supports a single access pattern: fetch by key, write by key. The
inside of the value is meaningless to the system, so it cannot be queried by its
contents. It fits work such as session state and caching.

A **document store** keeps the value as a structured document and allows querying by the
fields inside it. Its natural unit is the **aggregate**: data that is read together and
written together sits in a single document.

A **wide-column store** partitions rows by key and keeps a sparse, large number of
columns per row. It is oriented toward distributing very high write volumes.

A **graph database** makes nodes and edges first-class objects. It makes traversing long
paths — such as "what else did the members who borrowed this member's books also
borrow" — cheaper than a chain of relational joins.

A **search index** breaks text into words and builds an inverted index; it returns
results ranked by relevance. The catalog's free-text search is an example of this.

A **time series store** stores time-stamped measurements with compression and is
organized around range queries.

These families do not exclude one another; many relational engines offer document
handling, text search, and graph querying within themselves. **Which capability is found
in which engine varies**, and for that reason the decision is made on requirements, not
on a product's name.

## Document Storage's Trade-off

The document approach's gain and cost come from the same place: the data has been
assembled according to a single read pattern.

```sh
sqlite3 :memory: <<'SQL'
.headers on
.mode box
.nullvalue (empty)
CREATE TABLE member_document (member_no INTEGER PRIMARY KEY, document TEXT NOT NULL);
INSERT INTO member_document VALUES
 (41, '{"name":"Alice Kane","loans":[{"isbn":"975-01"},{"isbn":"975-02"}]}'),
 (52, '{"name":"Marcus Reyes","loans":[{"isbn":"975-01"}]}'),
 (63, '{"loans":[]}');

SELECT member_no, document ->> '$.name' AS name,
       json_array_length(document, '$.loans') AS loan_count
FROM member_document ORDER BY member_no;

SELECT o.value ->> '$.isbn' AS isbn, COUNT(*) AS times
FROM member_document u, json_each(u.document, '$.loans') o
GROUP BY isbn ORDER BY isbn;
SQL
```

```
┌───────────┬──────────────┬────────────┐
│ member_no │     name     │ loan_count │
├───────────┼──────────────┼────────────┤
│ 41        │ Alice Kane   │ 2          │
│ 52        │ Marcus Reyes │ 1          │
│ 63        │ (empty)      │ 0          │
└───────────┴──────────────┴────────────┘
┌────────┬───────┐
│  isbn  │ times │
├────────┼───────┤
│ 975-01 │ 2     │
│ 975-02 │ 1     │
└────────┴───────┘
```

The name and syntax of the operators that access document fields **vary by engine**; the
behavior shown here is the same everywhere.

Three results can be read here. The first is the gain: a member's name and loan list
come from a single row, with no join at all. The second is the cost: the question "how
many times was a given book borrowed" requires opening every document and flattening the
arrays inside them. Every question that crosses the document's boundary is expensive.
The third is a loss: member `63`'s document has no `name` field at all, and this insert
went through without error. Because there is nowhere to write `NOT NULL`, the rule has
fallen back onto every program that writes a document.

## The Schema Flexibility Fallacy

The document approach's most often cited advantage is schema flexibility: adding a new
field requires no schema change. This is true, but it is incompletely stated.

The schema does not disappear; it moves. In **schema-on-write**, the structure is defined
in the database, and every write must conform to it. In **schema-on-read**, the structure
lives in the expectation of the code that reads the document. When a field's name
changes or its type diverges, the problem does not disappear; instead of showing up as an
error at write time, it shows up as a difference in behavior at read time. Moreover, the
store now holds documents in both forms, and the reading code has to handle both.

The criterion is this: if the structure is genuinely unpredictable and no constraint is
sought on it, flexibility is a gain. If the structure can be known, not writing it into
the schema is only deferring validation.

## Scale and Distribution

Distributing data across multiple machines — **partitioning** — strains two of the
relational model's capabilities. A join between two relations whose parts sit on
different machines has to happen over the network. Transaction guarantees require extra
coordination and latency once they span multiple machines.

Some non-relational systems cheapen distribution by limiting these two capabilities from
the start: no join is offered, a transaction is limited to a single aggregate, and a read
is not guaranteed to see the most recent write — this is called **eventual consistency**.

The point to note here is ordering. Distribution is needed when the data no longer fits
on a single machine, or when more requests arrive than a single machine can withstand.
Taking on distribution's cost before reaching that threshold is a loss with no return.

## Decision Criteria

Five questions settle most of the decision.

- **Data shape.** Are there many-to-many relationships between entities, or does the data
  naturally split into independent aggregates? If many-to-many relationships are dense,
  the relational model wins.
- **Access pattern.** Are the queries known in advance and always by the same key, or
  will arbitrary questions be defined later? The latter requires declarative querying.
- **Constraint requirement.** Are there invariants that span multiple entities? The rule
  "a copy can be with only one member at a time" is a constraint that spills outside a
  document's boundary.
- **Consistency requirement.** Must a read see the most recent write? It must at the loan
  desk; it need not for catalog recommendations.
- **Schema stability.** Is the structure known and does it rarely change, or is it
  unpredictable field by field?

The answers may not point to a single system. **Polyglot persistence** is using different
stores for different jobs: keeping records in a relational engine, free-text search in a
search index. Its cost is clear — consistency between the two stores is now the
application's responsibility, and that responsibility is the course's first lesson's file
problem returning at a larger scale. This is why the common ordering is: the system of
record is kept in the relational engine, and secondary stores are derived from it.

## Summary

- Non-relational systems give up part of declarative querying, schema constraints,
  arbitrary joins, and transaction guarantees for distribution or for the cheapness of a
  single access pattern.
- Document storage makes a read within the aggregate join-free; a question that crosses
  the document's boundary requires opening every document.
- Schema flexibility does not eliminate the schema, it moves it into the reading code;
  validation is not deleted, it is deferred.
- Distribution makes joins and transaction guarantees more expensive; distribution taken
  on before reaching the threshold is cost with no return.
- The choice is made on data shape, access pattern, constraint and consistency
  requirements, and schema stability; if polyglot persistence is used, there must be a
  single system of record.

## Course Wrap-Up

This course began with the problems of keeping data in a file and built, step by step,
the relational model's answer to those problems. The relation's definition, coming from
set theory, explained why row order does not belong to the data and why a table is
required in the schema to behave like a relation. Keys gave the rule for telling rows
apart; Integrity Constraints gave that rule's enforcement by the engine. Null Values
opened up the silent traps of writing queries by turning the true–false pair into three.
Data Types showed that what a column holds is a decision separate from its
representation.

The second topic formalized the source of repetition. Functional Dependency was defined
as the general form of the key concept; Normal Forms eliminated, step by step,
dependencies whose left side is not a key. Denormalization tied the decision in the
opposite direction to a measure, and Schema Design Patterns gave the established
counterparts and anti-patterns for recurring situations.

The third topic brought the schema together with workload: the opposite requirements of
transactional and analytical workloads, the definition and limits of the ACID
guarantees, and when the relational model is the right choice.

There is a gap left deliberately open throughout the course. Queries were written in
every lesson, but the query language itself was never taught; the parts of the `SELECT`
statement, how conditions are written, join types, grouping, and ordering — all of it was
used within examples, never defined as a rule. The tools for working with null values,
the aggregate functions that summarize loan history, and the details of the statements
that change data are waiting in the same way.

The SQL Fundamentals course fills that gap. On top of the model built here — relations,
keys, constraints, and null values — it systematically builds the language that queries
that model. The library schema continues there as well: the tables designed in this
course will be the subject of every query written there.