Relational databases
Tables that store each fact once, rules that the database enforces, and joins that put the facts back together.
6 minute lesson
Every fact in one place
A relational database keeps data in a table for each kind of thing: one for customers, one for orders. Every row has a primary key that identifies it. An order does not repeat the customer's name. It holds the customer's key.
- ordersid, customer_id, total
- customersid, name, email
- table
- A set of rows that all have the same columns, such as one row per customer.
- primary key
- The column, or columns, whose value identifies exactly one row of a table.
- foreign key
- A column that holds the primary key of a row in another table. The database refuses values that point at no row.
Key terms
What a copy costs
- One wide table
Suppose every order row also carried the customer's email. Ana has 50 orders, so her email is stored 50 times.
- 50 order rows50 copies of one email
- A change
Ana changes her email. All 50 rows have to be updated. If the update misses a few, the database now holds two emails for one person, and no way to tell which is right.
- 47 rowsnew email
- 3 rowsold email
- Two tables
With a customers table the email exists once. The 50 orders point to that one row. A change is one update, and a contradiction is impossible.
- 50 orderscustomer_id 7
- 1 customer rowone email
Customers and orders are separate tables. Each order holds the id of its customer. Customer 7 has 120 orders and changes her email address.
How many rows have to be updated?
- 1
- 7
- 120
- 121
Show the answer
- 1
The email is stored in one place, the customer's row. The 120 orders do not contain it. They contain the number 7, which still points to the same row after the change. One update, and every order shows the new address the next time it is read with its customer.
A fact stored once is changed once, however many rows refer to it.
The database guards the link
CREATE TABLE orders (
id bigint PRIMARY KEY,
customer_id bigint REFERENCES customers (id),
total numeric
);REFERENCES declares a foreign key. From now on the database refuses an order whose customer does not exist. The rule holds for every program that writes, including the ones that forget to check.
The column orders.customer_id is declared as a foreign key to customers. A program inserts an order with customer_id 999. No customer has that id.
What happens?
- The database rejects the insert with an error
- The order is stored without any customer
- The database creates customer 999 for it
- The order is stored and repaired overnight
Show the answer
- The database rejects the insert with an error
A foreign key is a promise that the database keeps: every customer_id in orders points to a real customer. An insert that would break the promise is refused, and the program gets an error. The database never guesses, by inventing a customer or storing a dangling order.
With a foreign key, a reference to a row that does not exist is rejected.
A join puts the facts back together
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;A join pairs the rows whose keys match: each order with its customer. Because the facts are stored separately, they can be combined in any way, also for questions that nobody thought of when the tables were designed.
A relational database stores customers and orders in separate tables. Why, and how does a query get an order together with its customer's name?
Show the answer
So that each fact exists once: a customer's name is in one row, and a change is one update that cannot leave contradictions. An order holds the customer's key, guarded by a foreign key. A join brings them together again, by pairing each order with the customer row whose key matches.
Recap
- Tables store each fact once. Rows refer to each other by key, not by copies.
- A foreign key makes the database refuse a reference to a row that does not exist.
- A join combines rows by matching keys, for any question the data can answer.