The relational model

Almost every application you will work on stores its important data in a relational database. Before you write a single query, it pays to understand the model those databases are built on: data lives in tables, tables are linked by keys, and you describe what you want rather than how to fetch it. In this lesson you will learn that vocabulary, see why the relational model beat its rivals, and set up the small online bookstore you will query in every lesson that follows.

#Tables, rows and columns

A relational database stores data in tables. Each table describes one kind of thing — authors, books, customers — and has a fixed set of named, typed columns. Each row is one instance of that thing.

Here is part of the books table from our bookstore:

 book_id |           title           | author_id |  genre   | price | published | stock
---------+---------------------------+-----------+----------+-------+-----------+-------
       1 | A Wizard of Earthsea      |         1 | fantasy  | 12.99 |      1968 |    14
       2 | The Left Hand of Darkness |         1 | sci-fi   | 15.50 |      1969 |     6
       4 | Guards! Guards!           |         2 | fantasy  | 10.99 |      1989 |    22
       9 | Norwegian Wood            |         4 | literary | 17.00 |      1987 |     8

The formal theory uses different words for the same ideas. You will meet both, so it helps to know the mapping:

A few properties of tables matter in practice:

• Every value in a column has the column's type. price is numeric(6,2), so it can hold 12.99 but never 'cheap'. The database enforces this.
• Each cell holds a single value. You do not store "fantasy, sci-fi" in one cell; if a book could have many genres, that would be a separate table (you will see how in the schema design lesson).
• Rows have no inherent order. A table is a set of rows. If you need them sorted, you must ask for it in the query. The order you happen to see today can change tomorrow after an update or a vacuum.

#Keys: how rows are identified

A key is a column, or set of columns, whose values are unique across every row in the table. Keys are how you point at exactly one row.

• A candidate key is any column set that could uniquely identify a row. In customers, both customer_id and email qualify.
• The primary key is the candidate key you choose as the official identifier. It must be unique and can never be NULL. Each table has at most one.
• A natural key is one that already exists in the real world (an email address, an ISBN). A surrogate key is a meaningless number the database generates (customer_id = 4).

Our bookstore uses surrogate keys generated by PostgreSQL:

book_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Surrogate keys never need to change. A customer can change their email address, but customer_id = 4 stays the same forever, so nothing that refers to that customer has to be rewritten. We still keep email unique, because the business rule "one account per email" is real.

A key can span several columns. In order_items, neither order_id nor book_id is unique by itself — an order has many items, and a book appears in many orders — but the pair is unique: a given book appears at most once per order. So the primary key is (order_id, book_id), a composite key.

#Relationships and foreign keys

Tables become powerful when they refer to each other. A foreign key is a column whose values must match a key in another (or the same) table. Here is the whole bookstore schema, with arrows pointing from each foreign key to the key it references:

 authors                books                     order_items              orders                customers
 ───────────            ───────────────           ─────────────            ──────────────        ───────────────
 author_id (PK) ◄────── author_id (FK)            order_id (PK, FK) ─────► order_id (PK)     ┌──► customer_id (PK) ◄──┐
 name                   book_id (PK) ◄─────────── book_id  (PK, FK)        customer_id (FK) ─┘    name                │
 country                title                     quantity                 ordered_on             email (unique)      │
                        genre                     unit_price               status                 city                │
                        price                                                                     joined_on           │
                        published                                                                 referred_by (FK) ───┘
                        stock

This one diagram contains every relationship shape you will meet:

#One-to-many

One author writes many books; each book has one author. The foreign key lives on the many side: books.author_id references authors.author_id. The same pattern links customers to their orders.

#Many-to-many

An order contains many books, and a book appears in many orders. You cannot model that with a single foreign key column, so you introduce a junction table — order_items — that holds one row per (order, book) pair. Notice that the junction table also carries facts that belong to the pairing itself: how many copies were bought and at what price.

#Self-referencing

customers.referred_by points at another row in customers: Ben was referred by Ada, Dev by Ben. A table can reference itself whenever things relate to other things of the same kind (employees and managers, folders and subfolders).

#What a foreign key guarantees

A foreign key is not just documentation. The database refuses to store a reference to something that does not exist:

INSERT INTO books (title, author_id, genre, price)
VALUES ('Mystery Book', 99, 'fantasy', 9.99);

ERROR:  insert or update on table "books" violates foreign key constraint "books_author_id_fkey"
DETAIL:  Key (author_id)=(99) is not present in table "authors".

This property is called referential integrity: every arrow in the diagram always lands on a real row.

#NULL: the value that is not there

Some facts are unknown or do not apply. Chloe did not give us her city; Ada was not referred by anyone. SQL represents a missing value with NULL. NULL is not zero and not an empty string — it means "no value", and it behaves in surprising ways in comparisons, which you will study carefully in the filtering lesson. For now, remember two design rules: a primary key column can never be NULL, and you should mark a column NOT NULL whenever a value is always required (every book has a title).

#Why the relational model won

Before 1970, databases were navigational. Hierarchical systems such as IBM's IMS and network databases following the CODASYL standard stored records linked by physical pointers. To answer a question, a programmer wrote code that walked those pointers step by step: start at this customer record, follow the link to the first order, follow the next link… If the storage layout changed, the programs broke. A new question meant new traversal code.

In 1970 Edgar F. Codd, a researcher at IBM, published "A Relational Model of Data for Large Shared Data Banks". His key ideas still explain why you are learning SQL today:

1. Data independence. Programs describe data logically — tables and columns — not where bytes sit on disk. The database can add indexes, reorganise storage or change algorithms without breaking any query.
2. Declarative queries. You say what result you want ("customers in Leeds with their total spend"), and a component called the query planner works out how to compute it. The same query keeps working, and often gets faster, as the data grows.
3. A mathematical foundation. Operations on relations — filter, project, join, union — always produce new relations. That closure is why you can nest queries inside queries.
4. Integrity in the database, not the app. Types, keys and constraints are declared once and enforced for every program that touches the data.

Add decades of engineering on transactions, crash recovery and concurrency, and you get systems that keep data correct across many applications and many years. Other models — document stores, key-value stores, graph databases — are excellent for specific workloads, but the relational model remains the default for data whose correctness matters.

#Set up the bookstore database

Every later lesson assumes you have this database loaded, so do it now. Install PostgreSQL 17 from your operating system's package manager or the official installer, or run it in Docker:

docker run --name levelcode-pg -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:17
docker exec -it levelcode-pg psql -U postgres

In psql, create a database, connect to it, and make NULL visible (by default psql prints it as blank, which is easy to confuse with an empty string):

CREATE DATABASE bookstore;
\c bookstore
\pset null '(null)'

Now paste the following script. It creates the five tables and inserts the sample data. You can re-run it at any time to reset the database to this exact state — later lessons show output that assumes a fresh load.

DROP TABLE IF EXISTS order_items, orders, customers, books, authors CASCADE;

CREATE TABLE authors (
  author_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      text NOT NULL,
  country   text
);

CREATE TABLE books (
  book_id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title     text NOT NULL,
  author_id integer NOT NULL REFERENCES authors (author_id),
  genre     text NOT NULL,
  price     numeric(6,2) NOT NULL,
  published integer,
  stock     integer NOT NULL DEFAULT 0
);

CREATE TABLE customers (
  customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  email       text NOT NULL UNIQUE,
  city        text,
  joined_on   date NOT NULL,
  referred_by integer REFERENCES customers (customer_id)
);

CREATE TABLE orders (
  order_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers (customer_id),
  ordered_on  date NOT NULL,
  status      text NOT NULL
);

CREATE TABLE order_items (
  order_id   integer NOT NULL REFERENCES orders (order_id),
  book_id    integer NOT NULL REFERENCES books (book_id),
  quantity   integer NOT NULL,
  unit_price numeric(6,2) NOT NULL,
  PRIMARY KEY (order_id, book_id)
);

INSERT INTO authors (name, country) VALUES
  ('Ursula K. Le Guin', 'US'),
  ('Terry Pratchett',   'UK'),
  ('Octavia E. Butler', 'US'),
  ('Haruki Murakami',   'Japan'),
  ('N. K. Jemisin',     'US');

INSERT INTO books (title, author_id, genre, price, published, stock) VALUES
  ('A Wizard of Earthsea',      1, 'fantasy',  12.99, 1968, 14),
  ('The Left Hand of Darkness', 1, 'sci-fi',   15.50, 1969,  6),
  ('The Dispossessed',          1, 'sci-fi',   16.00, 1974,  0),
  ('Guards! Guards!',           2, 'fantasy',  10.99, 1989, 22),
  ('Small Gods',                2, 'fantasy',  10.99, 1992,  9),
  ('Going Postal',              2, 'fantasy',  11.50, 2004,  3),
  ('Kindred',                   3, 'sci-fi',   14.00, 1979, 11),
  ('Parable of the Sower',      3, 'sci-fi',   13.25, 1993,  5),
  ('Norwegian Wood',            4, 'literary', 17.00, 1987,  8),
  ('Kafka on the Shore',        4, 'literary', 18.50, 2002,  0);

INSERT INTO customers (name, email, city, joined_on, referred_by) VALUES
  ('Ada Park',     '[email protected]',   'Leeds',  '2024-01-15', NULL),
  ('Ben Okafor',   '[email protected]',   'London', '2024-02-03', 1),
  ('Chloe Martin', '[email protected]', NULL,     '2024-03-10', NULL),
  ('Dev Raman',    '[email protected]',   'Leeds',  '2024-05-21', 2),
  ('Elena Rossi',  '[email protected]', 'London', '2024-06-30', 4),
  ('Farid Haddad', '[email protected]', NULL,     '2024-09-12', 1);

INSERT INTO orders (customer_id, ordered_on, status) VALUES
  (1, '2025-01-05', 'shipped'),
  (2, '2025-01-12', 'shipped'),
  (1, '2025-02-02', 'shipped'),
  (3, '2025-02-14', 'cancelled'),
  (4, '2025-02-20', 'shipped'),
  (2, '2025-03-01', 'shipped'),
  (5, '2025-03-08', 'paid'),
  (1, '2025-03-15', 'paid'),
  (4, '2025-03-22', 'paid'),
  (2, '2025-03-30', 'paid');

INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
  (1,  1, 1, 12.99), (1, 4, 2,  9.99),
  (2,  9, 1, 17.00),
  (3,  2, 1, 15.50), (3, 5, 1, 10.99),
  (4, 10, 1, 18.50),
  (5,  7, 2, 14.00), (5, 8, 1, 13.25),
  (6,  4, 1, 10.99), (6, 6, 1, 11.50), (6, 5, 1, 10.99),
  (7,  1, 3, 12.99),
  (8,  2, 1, 15.50),
  (9,  9, 1, 17.00), (9, 7, 1, 14.00),
  (10, 6, 2, 11.50);

Check that it worked with the psql meta-command \dt, which lists tables:

            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | authors     | table | postgres
 public | books       | table | postgres
 public | customers   | table | postgres
 public | order_items | table | postgres
 public | orders      | table | postgres
(5 rows)

\d books describes one table, including its keys and which other tables reference it. Spend a minute reading the data: some details are deliberate. N. K. Jemisin has no books yet, Farid has never ordered, order 4 was cancelled, and order 1 bought Guards! Guards! at 9.99 even though the current price is 10.99.

#Practice

The bookstore wants customers to review books. Each review has a rating from 1 to 5, an optional comment, and the date it was written. A customer may review a given book at most once.

1. Is the relationship between customers and books (through reviews) one-to-many or many-to-many?
2. Which columns form the primary key of a reviews table, and which are foreign keys?
3. Write the CREATE TABLE reviews statement, run it in your bookstore database, and inspect it with \d reviews.

CREATE TABLE reviews (
  customer_id integer NOT NULL REFERENCES customers (customer_id),
  book_id     integer NOT NULL REFERENCES books (book_id),
  rating      integer NOT NULL,
  comment     text,
  written_on  date NOT NULL,
  PRIMARY KEY (customer_id, book_id)
);

#Key takeaways

• A relational database stores data in tables of typed columns; each row is one fact, and rows have no inherent order.
• A primary key uniquely identifies each row and is never NULL; keys can be surrogate (generated) or natural, single-column or composite.
• Foreign keys link tables: put them on the "many" side of one-to-many relationships, and use a junction table for many-to-many.
• The database enforces referential integrity, so a foreign key can never point at a row that does not exist.
• The relational model won because of data independence and declarative queries: you describe the result and the planner finds the fastest way to compute it.