The relational model in ten minutes

The relational model is one idea from a 1970 paper, and it is worth understanding properly because everything in the next eight modules is a consequence of it.

The idea: store facts in tables, keep each fact in exactly one place, and connect tables by matching values rather than by pointers.

That last clause is the part people skim, and it is the whole thing.

Facts in one place

Here is the model being violated, which is the clearest way to see it:

loans
┌────┬──────────────┬──────────────┬─────────────────┬────────────┐
│ id │ member_name  │ member_email │ book_title      │ due_date   │
├────┼──────────────┼──────────────┼─────────────────┼────────────┤
│  1 │ Asha Kulkarni│ asha@ex.com  │ Malgudi Days    │ 2026-10-14 │
│  2 │ Asha Kulkarni│ asha@ex.com  │ Train to Pakistan│ 2026-10-21│
│  3 │ Asha Kulkarni│ asha@exx.com │ Godaan          │ 2026-10-28 │
└────┴──────────────┴──────────────┴─────────────────┴────────────┘

Asha's email is stored three times, and row 3 disagrees with the others. Somebody updated one row.

Which is correct? There is no answer. The data has no opinion, because the same fact was written in three places and nothing kept them in step.

Three specific problems, and they have names you will meet again in module 8:

Update anomaly. Changing Asha's email means finding every row. Miss one and the data contradicts itself, exactly as above.

Insertion anomaly. You cannot record a new member until they borrow something, because members only exist as a side effect of loans.

Deletion anomaly. Delete Asha's last loan and you lose her email address entirely. You did not mean to delete a member; you meant to delete a loan.

The fix: one table per kind of thing

members                          books
┌────┬───────────────┬──────────┐ ┌────┬───────────────────┐
│ id │ name          │ email    │ │ id │ title             │
├────┼───────────────┼──────────┤ ├────┼───────────────────┤
│  1 │ Asha Kulkarni │ asha@... │ │ 10 │ Malgudi Days      │
│  2 │ Ravi Shinde   │ ravi@... │ │ 11 │ Train to Pakistan │
└────┴───────────────┴──────────┘ └────┴───────────────────┘

loans
┌────┬───────────┬─────────┬────────────┐
│ id │ member_id │ book_id │ due_date   │
├────┼───────────┼─────────┼────────────┤
│  1 │     1     │   10    │ 2026-10-14 │
│  2 │     1     │   11    │ 2026-10-21 │
└────┴───────────┴─────────┴────────────┘

Asha's email is in one row. Change it there and every loan sees the new value immediately, because the loans never held a copy — they hold her id.

All three anomalies are gone. You can add a member with no loans. You can delete a loan without losing a member. There is no second copy to disagree.

Keys

Two kinds, and the distinction runs through the whole course.

A primary key identifies a row uniquely within its table. members.id. Every table should have one, and module 4 argues at length for a meaningless number rather than an email address or a phone number — because business values change, and a primary key that changes is a primary key that breaks everything referring to it.

A foreign key is a column holding another table's primary key. loans.member_id holds a members.id.

CREATE TABLE loans (
  id        bigint PRIMARY KEY,
  member_id bigint NOT NULL REFERENCES members(id),
  book_id   bigint NOT NULL REFERENCES books(id),
  due_date  date   NOT NULL
);

REFERENCES is doing real work: the database will now refuse a loan whose member_id does not exist, and refuse to delete a member who still has loans. That property is called referential integrity, and it is enforced against every client, forever, regardless of which program is writing.

Connected by values, not pointers

This is the part worth being precise about, because it explains both the power and the cost.

In most programming languages, one object refers to another by a pointer — a memory address. Following it is free, and it only works inside that one running program.

In the relational model, loans.member_id = 1 is a value. Nothing is connected until you ask for it to be:

SELECT m.name, b.title, l.due_date
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN books   b ON b.id = l.book_id;

JOIN … ON is where the connection happens — at query time, every time.

Three consequences:

You can combine in ways nobody designed for. Members to loans, loans to books, books to authors, and any path between them. Nothing had to be anticipated. This is the property the families lesson called the most underrated one.

It has a cost. A join is work — matching rows at run time — which is why indexes and EXPLAIN (module 7) exist, and it is the main thing document databases trade away when they embed data instead.

The data outlives any program. Values in tables mean something on their own. A schema written in 1995 is still readable; a heap of pointers from a 1995 process is not.

Relations are sets: two things that surprise people

The model is built on mathematical sets, which produces two behaviours worth knowing before they confuse you.

Rows have no order. A table is a set of rows. Without ORDER BY the database may return rows in any order, and that order can change when the data grows or the plan changes.

SELECT title FROM books;                    -- no promised order
SELECT title FROM books ORDER BY title;     -- promised

Code that relies on "they came back in insertion order" works until the day it does not. If order matters, say so.

Duplicate rows are possible, and usually a mistake. Strictly, a relation is a set and has no duplicates. SQL permits them, which is one of the places SQL departs from the theory. A primary key is what prevents them, and a table without one will eventually contain the same row twice.

NULL, briefly

The model needed a way to say "no value here", and the answer is NULL. It is not zero and not an empty string — it means unknown or not applicable.

SELECT * FROM loans WHERE returned_at = NULL;      -- returns NOTHING, ever
SELECT * FROM loans WHERE returned_at IS NULL;     -- correct

= NULL is never true, because "is this unknown value equal to this unknown value?" cannot be answered. It is not even false — it is a third thing, UNKNOWN. That three-valued logic catches everybody, and it has a lesson of its own in module 2.

What the model does not do

Being honest about the limits, because the next modules depend on knowing them.

It does not decide your tables for you. The model tells you how to represent a decision, not what to decide. Whether "address" is a column, five columns or a table is a modelling judgement, and module 8 is about making it.

It does not scale writes across machines easily. Joins and transactions assume the data is reachable; splitting one table across ten servers makes both expensive. This is the genuine pressure that produced the other families.

It fits some data badly. A document of arbitrary nested shape, a social graph traversed to unknown depth, a million sensor readings a second. Those are the problems the families lesson listed, and they are real.

And it is fifty-five years old and still the default, which is unusual in this industry and worth taking as evidence. The ideas in Codd's paper survived because keeping each fact in one place, and connecting by value, turns out to be right far more often than not.

Check your work

The idea, in one sentence. Facts in tables, each fact in one place, connected by matching values.

The three anomalies. Update (copies disagree), insertion (cannot add one thing without another), deletion (removing one thing loses another).

What a primary key is for, and why not an email. Unique identity — and business values change.

What REFERENCES buys. Referential integrity, enforced against every client.

Pointers versus values. A pointer is followed for free inside one process; a value is joined at query time, every time.

The upside of joining at query time. You can combine in ways nobody designed for.

The cost. Joins are work, which is why indexes and EXPLAIN exist.

Why row order is not promised. A table is a set; without ORDER BY anything may happen.

Why duplicate rows are possible. SQL permits them; a primary key prevents them.

Why = NULL returns nothing. NULL means unknown, so the comparison is UNKNOWN, not true. Use IS NULL.

Three things the model does not do. Decide your tables, scale writes across machines, or fit every shape of data.

Practice

1. Write out the flattened loans table and give Asha three loans. Change her email in one row and describe each of the three anomalies.
2. Split it into three tables and repeat the exercise. Confirm all three anomalies are gone.
3. Draw the three tables on paper with lines between the keys.
4. Write the CREATE TABLE for loans with both foreign keys.
5. Try to insert a loan with a member_id that does not exist. Read the error.
6. Try to delete a member who has loans. Read that error too.
7. Write a query joining all three tables.
8. Run a SELECT with no ORDER BY several times on a growing table and watch for the order changing.
9. Insert the same row twice into a table with no primary key. Then add one and try again.
10. Run WHERE returned_at = NULL and then IS NULL on the same data.
11. Take any object model you have written and identify a pointer that would become a foreign key.
12. Find the 1970 Codd paper and read the first page. It is more readable than you expect.

Official documentation

• PostgreSQL — Concepts — Tables, rows and columns, in the project's own words.
• PostgreSQL — Constraints — Primary keys, foreign keys and referential integrity in full.
• PostgreSQL — Joins between tables — Connecting by value, with worked examples.
• Codd, "A Relational Model of Data for Large Shared Data Banks" (1970) — The original paper. Short, and the source of everything above.

Next: the promises a database makes — ACID, BASE and CAP.