The Relational Model

Tables, rows and columns

In a relational database all data lives in tables. A table holds the data about one kind of thing: customers, products, orders. Each row describes one of those things (one customer), and each column is one property that every row has (the customer's email address), with a name and a data type. Here is one row of the shop's customers table, showing four of its seven columns:

SELECT customer_id, first_name, last_name, email
FROM customers
WHERE customer_id = 2;

customer_id  first_name  last_name  email
-----------  ----------  ---------  ------------------------
2            Kwame       Mensah     kwame.mensah@example.com

The same ideas go by several names, depending on whether you read database theory, SQL documentation or a spreadsheet manual:

The model is called relational because of the mathematical term relation (a set of tuples), not because tables have relationships with each other, although they do. A few rules follow from it:

• Every column has a name that is unique within its table, and one data type.
• Each cell holds a single value, not a list. A customer with two phone numbers needs a second row in another table, not two numbers in one cell (see Normalization).
• Rows have no built-in order. Like a relation in theory, a table has no first or last row, so a query that needs a fixed order must ask for it with ORDER BY.

Primary keys

If two customers are both called Tom Jansen, the database still needs to tell them apart. A primary key is the column, or combination of columns, whose value identifies exactly one row in a table. The database enforces two rules on it: the value must be unique, and it may not be missing. In the shop every table has one: customers.customer_id, products.product_id, orders.order_id.

Try to add a customer with a number that is already taken, and the database refuses:

INSERT INTO customers
  (customer_id, first_name, last_name, email, city, country, signup_date)
VALUES
  (1, 'Noor', 'Bakker', 'noor.bakker@example.com', 'Leiden', 'NL', '2026-09-30');

Error: UNIQUE constraint failed: customers.customer_id

Natural and surrogate keys

A natural key is a value that already identifies the thing in the real world: an email address, an ISBN, a country code. A surrogate key is a value invented by the database only to identify rows, usually a number counting up, like customer_id.

Most designs use surrogate keys, for good reasons. Natural values change (customers change their email address), are sometimes missing, can be long, and may be personal data you would rather not copy into every related table. A surrogate key never needs to change because it means nothing outside the database. The natural value still deserves protection against duplicates: the shop's email column is declared UNIQUE.

Foreign keys

Tables become useful together. Each order belongs to a customer, so the orders table has a customer_id column that holds the primary key value of the customer who placed it. A foreign key is such a column: its values must match a primary key (or another unique column) in the table it references. Instead of copying Kwame's name and email into every order, the order just says "customer 2":

SELECT order_id, customer_id, order_date, status
FROM orders
WHERE customer_id = 2
ORDER BY order_id;

order_id  customer_id  order_date  status
--------  -----------  ----------  ---------
102       2            2026-06-05  delivered
107       2            2026-08-03  shipped

Because orders.customer_id is declared as a foreign key, the database guarantees that every order points to a customer who exists. An order for customer 99, who is not in the customers table, is refused:

INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (111, 99, '2026-09-25', 'pending');

Error: FOREIGN KEY constraint failed

The protection works in the other direction too: customer 1 cannot be deleted while orders still refer to them. This guarantee is called referential integrity; what should happen to the orders when a customer is deleted (refuse, delete them as well, or set the reference to empty) is a design choice covered in Designing Tables.

Relationships

A relationship is an association between rows of two tables (or of one table with itself), implemented with a foreign key. Relationships are described by how many rows can be on each side:

One-to-many

The most common relationship by far. One customer places any number of orders (zero, one or many); each order belongs to exactly one customer. The foreign key always goes on the "many" side: the order stores the customer's number, because an order has one customer, while a customer row could never hold a list of order numbers in one cell. You saw it above: customer 2 has orders 102 and 107.

A table can also have a one-to-many relationship with itself. In the employees table, manager_id refers to another row of the same table: one manager has many employees, and the director has no manager at all (NULL, meaning "no value").

SELECT employee_id, name, role, manager_id
FROM employees
ORDER BY employee_id;

employee_id  name              role               manager_id
-----------  ----------------  -----------------  ----------
1            Fatima El Amrani  Director           NULL
2            Joris Bakker      Head of Sales      1
3            Yuki Tanaka       Head of Warehouse  1
4            Daan Visser       Sales Agent        2
5            Chloé Martin      Sales Agent        2
6            Mateus Costa      Warehouse Worker   3

One-to-one

One-to-one relationships are rarer, because two tables that always pair one row with one row could usually be one table. They are used to keep optional or sensitive data apart: for example a customer_payment_details table whose primary key is also a foreign key to customers, stored with stricter permissions. The sample shop has no one-to-one relationship.

Many-to-many and junction tables

An order can contain many products, and a product can appear in many orders. Neither table can hold the other's key, because both sides are "many". The solution is a third table in between: a junction table (also called a link, bridge or associative table) whose rows each connect one order to one product. In the shop that table is order_items, and it also stores facts that belong to the combination: how many were ordered and at what price.

SELECT order_id, product_id, quantity, unit_price
FROM order_items
WHERE order_id = 106
ORDER BY product_id;

SELECT product_id, name, price
FROM products
WHERE product_id IN (3, 7, 10)
ORDER BY product_id;

order_id  product_id  quantity  unit_price
--------  ----------  --------  ----------
106       3           2         25
106       7           1         45
106       10          4         7

product_id  name                price
----------  ------------------  -----
3           Wireless mouse      25
7           USB-C hub           45
10          Ethernet cable 5 m  7

The first query shows that order 106 has three lines; the second looks up those three product numbers (IN means "any of these values"). Read from the other side, the same junction table tells you every order that contains the wireless mouse:

SELECT order_id, product_id, quantity, unit_price
FROM order_items
WHERE product_id = 3
ORDER BY order_id;

order_id  product_id  quantity  unit_price
--------  ----------  --------  ----------
101       3           1         25
106       3           2         25
109       3           1         25

Looking up the numbers by hand in a second query works, but SQL can combine the tables in one query and return product names next to quantities. That is a join, explained in Joins.

Entity-relationship diagrams

Before tables are created they are usually drawn. An entity-relationship diagram (ERD) shows the entities (the kinds of things the data describes, which become tables), their attributes and the relationships between them. The shop's ERD, with the key columns marked PK (primary key) and FK (foreign key):

Read the diagram along the lines: one customer, many orders; one order, many order items; one product, many order items. The customer-to-product relationship is many-to-many, resolved through orders and order_items. Professional tools use richer notations, such as crow's foot (a three-pronged line end for "many"), and also show whether a side is optional, but the information is the same.

Kinds of keys

"Key" is used for several related ideas. They are worth keeping apart, because exam questions like to test the differences:

The primary key of order_items is composite: neither order_id nor product_id is unique on its own (order 106 has three lines; the mouse is in three orders), but the pair is. That also means a product can appear only once per order; ordering two means one line with quantity 2. Adding product 1 to order 101 a second time is refused:

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (101, 1, 1, 9);

Error: UNIQUE constraint failed: order_items.order_id, order_items.product_id

The schema in practice

All of the above (tables, columns, types, keys and relationships) together form the database's schema. On paper it is the ERD; in the database it is a set of CREATE TABLE statements. This is how the shop declares the orders table, with its primary key, its foreign key and a rule for the allowed status values:

CREATE TABLE orders (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL REFERENCES customers (customer_id),
  order_date   TEXT    NOT NULL,
  status       TEXT    NOT NULL
               CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
);

Every part of that statement, from data types to CHECK rules, is explained in Designing Tables. How to decide which tables you need in the first place, so that every fact is stored once, is the subject of Normalization.