Growth Notes

The Relational Model

Tags: web-dev concept

The Relational Model

Date: 2026-08-17

Data as tables of rows, with relationships expressed by matching values rather than by pointers. It’s from 1970, it outlived every product built on it, and the reason is that it separates what you store from how you ask about it.

The relational model represents data as relations — tables of rows, each row a tuple of values, each column a named attribute. Relationships are expressed by shared values, not by physical links.

The core structures

PRODUCTS
id   name        price   category_id
1    Serum       24.99   3
2    Cleanser     9.99   3
3    Brush       14.99   7

CATEGORIES
id   name
3    Skincare
7    Tools

PRIMARY KEY     uniquely identifies a row
                products.id

FOREIGN KEY     references another table's
                primary key
                products.category_id
                  → categories.id

CONSTRAINT      a rule the database enforces
                NOT NULL, UNIQUE, CHECK

category_id = 3 is not a pointer. There’s no address, no link. The relationship exists because the values match, and it’s resolved at query time by a join.

Why that indirection is the whole idea

Because relationships are values rather than pointers, you can ask questions nobody anticipated:

"products in Skincare"        ← designed for
"categories with no products" ← not designed
"average price per category"    for, works
"products priced above their    anyway
 category average"

In a pointer-based or document store, the queries you can answer cheaply are the ones the structure was built for. In a relational store the structure doesn’t privilege a direction — SQL vs NoSQL.

Declarative querying

You state what you want; the engine decides how:

SELECT c.name, AVG(p.price)
FROM products p
JOIN categories c ON p.category_id = c.id
GROUP BY c.name

Nothing there says which table to read first, whether to use an index, or which join algorithm. The query planner decides, and can decide differently as the data changes — Query Planning.

That separation is why the model has outlasted its implementations: your query survives a change in storage, indexing or hardware.

Integrity the database enforces

The part applications routinely try to reimplement badly:

NOT NULL       this must have a value
UNIQUE         no duplicates
FOREIGN KEY    cannot reference a missing row
               cannot delete a referenced row
CHECK          price >= 0
DEFAULT        fallback when unspecified

Enforce these in the database, not the application. Application checks have a gap between check and write that concurrent requests slip through; a constraint has no gap — Race Conditions.

The other argument is durability: application code changes, gets bypassed by a migration script, or is one of three services writing to the same table. The constraint is the only rule that applies to everyone.

NULL, which is genuinely strange

NULL means unknown, not empty, and it propagates through comparisons:

NULL = NULL      → NULL, not TRUE
NULL <> 5        → NULL, not TRUE
1 + NULL         → NULL
 
WHERE x = NULL   → matches nothing, ever
WHERE x IS NULL  → the correct form

Three-valued logic — true, false, unknown — surprises everyone once. It also affects aggregates: COUNT(column) skips nulls, COUNT(*) doesn’t.

Where the model is a poor fit

Being fair to the alternatives:

  • Deeply nested documents with no fixed shape — a document store fits better
  • Graph traversal of arbitrary depth — “friends of friends of friends” is painful in SQL and native in a graph database
  • Very high write throughput on simple key-value access
  • Genuinely schemaless data, though this is claimed far more often than it’s true

The usual failure is reaching for an alternative because the schema feels inconvenient, then reimplementing joins and constraints in application code — worse, slower, and without transactions — Transactions and ACID.

What to carry

The model is about the shape of the data, not the product. Postgres, MySQL, SQLite and SQL Server implement it differently — SQL Dialects, but keys, joins, constraints and declarative querying transfer between all of them — and to warehouses like BigQuery, which is why the model is worth understanding once rather than per-tool — Reference - SQL.

Graph View

Backlinks