Relational vs. Non-Relational Selection

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.

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.