The idea behind the relational model
A relational database stores information in tables. Each table describes one kind of thing, such as customers, products or support tickets, and the tables are connected by shared values. The word “relational” refers to those connections between tables, which are called relationships.
The approach is popular because it matches how many organizations already think about their data. A shop has customers, and customers place orders. Rather than putting everything in one enormous list, the relational model keeps customers in one place and orders in another, then links them.
Tables, rows and columns
A table looks a lot like a spreadsheet sheet.
- A column (also called a field) holds one kind of detail, such as a name or an email address. Every column has a data type, such as text, whole number, decimal number, date or yes/no value.
- A row (also called a record) is one complete entry, such as one particular customer.
- A table is the collection of all the rows that share the same columns.
Here is a small, hypothetical customers table:
| customer_id | name | city |
|---|---|---|
| 1 | Ana | Leeds |
| 2 | Ben | Bristol |
| 3 | Chloe | Glasgow |
Unlike a spreadsheet, a database table is strict. Every row must follow the same structure, and the database checks that values match their column types. You cannot put a word into a date column. That strictness is part of what keeps the data trustworthy.
Primary keys
Rows need a reliable way to be told apart. Two customers could share a name, so names alone are a poor identifier. A primary key is a column, or combination of columns, whose value is unique for every row and never empty. In the table above, customer_id is the primary key.
Keys are usually simple numbers assigned by the database, though they can also be other unique values. A good key does not change over time. An email address looks unique but people change addresses, so many designers prefer a separate, stable number.
Foreign keys and relationships
Now suppose the shop also keeps an orders table:
| order_id | customer_id | total |
|---|---|---|
| 101 | 1 | 25.00 |
| 102 | 1 | 40.00 |
| 103 | 2 | 15.00 |
The customer_id column in orders does not describe the order itself. It points back to a row in the customers table. A column that refers to the primary key of another table is called a foreign key. The totals here are made-up example values.
This arrangement means a customer’s name and city are stored once, in one place. If Ana moves, you change one row, and every order still points to the right person.
A foreign key can also be enforced. The database can refuse to add an order for customer 99 if no such customer exists, and it can stop you from deleting a customer who still has orders. This is called referential integrity, and it prevents orphaned records.
Kinds of relationships
Relationships come in three common shapes.
One-to-many. One customer can have many orders, but each order belongs to one customer. This is by far the most common shape, and the foreign key sits on the “many” side.
One-to-one. One row in a table matches at most one row in another. It is used less often, for example to separate rarely used or sensitive details into their own table.
Many-to-many. A student can take many courses, and a course has many students. A single foreign key cannot express this, so designers add a third table, often called a junction or linking table. Each row of the linking table holds one student and one course. The two many-to-one links on either side of it together create the many-to-many relationship.
Asking questions across tables
Because the data is split across tables, answering a question such as “what has Ana ordered?” means combining them. That is the job of SQL joins, using the keys as the matching points. The basic language for reading rows is covered under SQL queries.
Why this design works
Storing each fact once has practical benefits:
- Updates happen in one place, so the data does not contradict itself.
- Storage is used efficiently, because details are not repeated on every order.
- The rules can be enforced by the database rather than by every program that touches it.
How far to take the splitting is a design question. The process of deciding how to divide data sensibly is called database normalization, and it is worth learning once the basics feel comfortable.
Where relational databases are not the best fit
Relational databases are not the answer to everything. Data that has no fixed shape, such as varied documents, or data that must be spread across many machines with very high write volumes, is sometimes handled better with other designs. The trade-offs are discussed in the comparison of relational versus document and key-value stores. For ordinary business records with clear structure, though, tables and keys have remained a dependable choice for decades.
A quick vocabulary check
- Table: a set of rows about one kind of thing.
- Row: one record.
- Column: one attribute shared by all rows.
- Primary key: the unique identifier for each row.
- Foreign key: a column that refers to another table’s primary key.
- Schema: the overall design of tables, columns and relationships.
If you can describe your own information, such as a book collection or a set of IT assets, in terms of these words, you already understand the core of a relational database. Tracking equipment is a good practice case, because asset management naturally involves devices, owners and locations that link to one another.