What is a relational database?

What makes a database relational?

The idea comes from Edgar F. Codd, a researcher at IBM, who proposed the relational model in 1970 in a paper called "A Relational Model of Data for Large Shared Data Banks", according to IBM. In his model, data lives in relations, which we now call tables. SQL, the language used to work with relational databases, was developed at IBM in the early 1970s.

• Table: holds data about one kind of thing, such as customers.
• Row (record): one item, such as one customer.
• Column (field): one property, such as a customer's city, with a fixed data type.
• Primary key: a column whose value is unique for each row.
• Foreign key: a column that holds another table's primary key, linking the two.

If databases are new to you, start with our guide to what a database is.

A relational database example

An online shop needs to store customers and their orders. Here are two tables:

Order 103 doesn't repeat Amira's name and city. It just stores her customer_id, 1. That number is a foreign key: it points to the matching primary key in the customers table. If Amira moves house, you change her city in one row, and every one of her orders is automatically up to date.

Try it: this question shows a table and asks which column should be the primary key.

Types of relationship

Many to many relationships can't be stored with a single foreign key, because a student takes many courses and a course has many students. Instead, a linking table (also called a junction table), such as enrolments, has one row per student per course, with two foreign keys: student_id and course_id. That turns one many to many relationship into two one to many relationships. Try one: this question describes two tables and asks for the relationship between them.

Combining tables with a JOIN

Splitting data into tables is only useful if you can put it back together. In SQL, a JOIN matches rows using the keys. This query lists every order with the customer's name:

SELECT customers.name, orders.order_id, orders.total
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

Tom Ellis is missing because he has no orders: an INNER JOIN only keeps rows that match in both tables. A LEFT JOIN keeps every customer, filling in empty (NULL) order details for Tom. Add GROUP BY customers.name with SUM(orders.total) and you get each customer's spending: Amira £37.25 and Grace £60.00. Try one: this question asks which kind of join a task needs.

Why use a relational database?

• No needless repeats. Each fact is stored once, which the AQA specification describes as eliminating data redundancy and data inconsistency.
• Rules the database enforces. Data types, required fields and referential integrity: an order can't point to a customer who doesn't exist.
• Flexible questions. SQL can combine, filter, sort and total data in ways nobody planned when the tables were designed.
• Safe changes. Transactions make groups of changes all succeed or all fail together.

Normalisation

Designing tables so each fact is stored once is called normalisation (normalization in the US). The first rule, first normal form, says each cell holds one value: no lists like "Milk, Bread" in a single column. Later normal forms move data that depends on something other than the primary key into its own table, as we did by moving customer details out of the orders table.

Relational vs NoSQL databases

A relational database management system (RDBMS) is the software that runs a relational database. The Databases course has you design tables, write joins and compare SQL with NoSQL.

Common mistakes

• Thinking "relational" means the tables are related. The name comes from Codd's mathematical term "relation", meaning a table. The links between tables are relationships.
• Putting the foreign key on the wrong side. In a one to many relationship it goes in the "many" table: orders store customer_id, not the other way round.
• Storing lists in one cell. Use a separate table instead.
• Trying to link many to many directly. It needs a linking table.
• Choosing a primary key that can change or repeat, such as an email address or a name.
• Expecting an INNER JOIN to show unmatched rows. Use a LEFT JOIN for that.

Read next

Computer Science · DatabasesWhat is a database? Types, software and examplesWhat a database is, how tables, records and fields work, what database software (a DBMS) does, the main types of database and a first SQL query.