Relational databases

A database is an organised collection of data, stored so that it can be searched, updated and shared. Online shops, social networks, school registers and hospital records all sit on databases. The most common design, and the one this chapter is mostly about, is the relational database: data kept in tables that are linked to each other.

The simplest idea is a flat file: everything in a single table. Here is a bookshop's sales in one:

Repeating data wastes space, and worse, it causes three kinds of anomaly:

• Update anomaly: if Ana moves to Valencia and you change only some of her rows, the table now disagrees with itself and nothing says which is right.
• Delete anomaly: delete the only sale of a book and you also delete everything known about that book and its author.
• Insert anomaly: you cannot record a new author until somebody buys one of their books, because every row needs a customer.

The cure is to give each kind of thing its own table and link them.

• Tables. A table describes one entity (a customer, a book). Each row is one record (also called a tuple), such as one particular book. Each column is an attribute (or field): one characteristic, such as the price.
• Primary key. One attribute that is different for every row, so each record can be picked out exactly. Names are no good (two books can share a title), so tables usually get an ID.
• Foreign key. An attribute holding another table's primary key. This is how a row in one table points at a row in another.
• Composite key. When no single attribute is unique, two or more together can be (a purchase line identified by order and book). If a key is made by joining pieces of several values (initials, surname and birth date), it is sometimes called a concatenated key.
• Relationships between tables: one-to-one, one-to-many (one author, many books) and many-to-many (customers and books). A many-to-many relationship cannot be stored directly: a third linking table, here Purchase, holds a foreign key to each side, turning it into two one-to-many relationships.
• Integrity rules. Referential integrity means a foreign key must match an existing primary key (or be empty), so a record can never point at something that does not exist.

• Less duplication and redundancy: each fact is stored once, so it takes less space.
• Consistency and integrity: with one copy of each fact, it cannot disagree with itself, and keys and rules stop bad data going in.
• Easy retrieval: SQL (A3.3) can combine and filter tables in one statement.
• Concurrency and reliable transactions: many users can work at once and a transaction either fully happens or not at all (A3.3.6).
• Security: each user can be given only the access they need.
• Scalability and support: relational systems have been used since the 1970s, have huge communities, and are easy to scale up by adding resources to one server.

• Design complexity: splitting data into well-linked tables takes skill, and many tables are harder to understand and maintain.
• Rigid schema: every row must fit the table's columns, so varied or unstructured data (posts, photos, sensor readings with different fields) fits badly, and changing the structure later can be painful.
• Very large data and speed: joining many big tables is slow, and relational systems scale up (a bigger server) more easily than out (many servers). Huge analysis and web-scale workloads often use other designs (A3.4).
• Hierarchical data: deep trees need many joins to walk.
• Object-relational mismatch: programs written with objects (B3) hold data in a different shape from tables, so converting between the two takes effort.