What a relational database is, explained with a small shop

Tables, records and fields

A small shop has customers, products and sales. In a relational database each gets a table. Each row is a record (one customer), and each column is a field (the customer’s name, email, postcode). Every record has a primary key, usually a number, that never changes and is never reused, so it can be pointed at even if the name is corrected.

Linking tables

A sale needs to say who bought. Rather than copying the customer’s name and address into the sale, the Sales table has a Customer field holding that customer’s ID. That field is a foreign key: it points at a record in another table. One customer can have many sales, which is called a one-to-many relationship.

A sale can contain many products, and a product appears in many sales. That many-to-many link is made with a table in between, Sale Lines, where each line points at one sale and one product and holds the quantity and price. Most real databases are built from chains like this.

Why bother

• Typed once. A customer moves house; you change one record, and every sale shows the new address.
• Checked. The database refuses a sale line pointing at product 412 when there is no product 412. This is called referential integrity, and it is the thing a spreadsheet full of VLOOKUPs cannot promise.
• Cleaned up. A relationship can say that deleting a sale deletes its lines too, or that a customer with sales cannot be deleted at all.
• Questions across tables. A query can join sale lines to products and answer “what were our best sellers this month” in one step.

Where you will meet one

Microsoft Access and LibreOffice Base are desktop databases that keep a file on your own computer. SQLite is the engine inside countless phone apps and programs. MySQL, PostgreSQL and SQL Server run on servers for many people at once. They differ in size and setup, but tables, keys and relationships work the same way in all of them.