Table Talk

Table Talk

A **database** is an organised, persistent store of data. A **relational** database keeps that data in **tables** that can be linked together. To talk about them you need five words exactly right: **table, record, field, primary key, foreign key**, and to explain why splitting data across tables is worth it. Let's get fluent.

Tables, records and fields

Picture a **Students** table. Its shape gives you three of the five words, and they describe different parts of the same grid:

Find the record

Here is the Students table. Tap the **record** that stores Ben's details (StudentID 1025).

Count them

The Students table has three columns and three rows of data. How many FIELDS does it have?

• Three: StudentID, FirstName and TutorGroup
• Nine, one for each box of data
• One, because it is a single table
• Six: three columns plus three rows

The primary key

Every record has to be tellable apart from every other. A **primary key** is a field whose value is **unique** for every record: no two records may share it. In Students, **StudentID** is the primary key. Names might repeat, but every student's id is one of a kind. Uniqueness is the only requirement worth remembering. A primary key does not have to be a number, and it does not have to be the first column: it just has to identify one record and only one.

Find the primary key

Tap the **column** that is the table's **primary key**.

Why not FirstName?

Why would **FirstName** be a poor choice of primary key?

• Two students could share the same first name, so it is not unique
• Names are too short to store reliably
• A primary key has to be a number
• Names are private data and should not be keys

Linking tables: the foreign key

The power of a *relational* database is **linking** tables. A **foreign key** is a field in one table that refers to the **primary key** of another. A separate **Grades** table holds a **StudentID** field: a foreign key pointing back at the Students table's primary key. That link connects each grade to exactly one student without copying their name and tutor group into every grade. Here is the part that catches people out. A foreign key is **not unique in its own table**. Student 1024 can appear in three rows of Grades, one per subject. It is unique over in Students, which is exactly what makes it usable as a link.

Find the foreign key

This is the **Grades** table. Tap the **column** that is a foreign key: the one holding another table's primary key.

Why bother splitting the data?

You could put everything in one giant table. Here is what happens if you do, and what changes when you split it into linked tables instead:

Spot the problem

A shop keeps ONE big table and writes the customer's full address on every order row. A customer moves house. What is the main problem?

• The address is duplicated on many rows, so an update can leave copies disagreeing
• The table runs out of column space
• Orders can no longer be sorted by date
• The table cannot have a primary key

Diagnose the library

A library keeps one table with a row per loan, and writes the borrower's full address on every row. Storing the same address on 40 rows is _____. When a borrower moves and only 39 rows are updated, the database has become _____. Splitting into Borrowers and Loans, linked by a borrower id, stores the address _____, and the id in the Loans table is called a _____ key.

Explain the problem

A sports club stores every booking in a single table, with the member's name, phone number and address written on each booking row. Explain the problems this causes, and how splitting the data into linked tables would fix them.

• Name the first problem, and give a concrete example of what is duplicated
• Name the second problem, and explain how it FOLLOWS from the first
• Say how the data would be split, and what each table would hold
• Name the field that links them, and say which table its values are unique in

In the exam

A _____ is a row and holds everything about one thing; a field is a _____. A primary key identifies each record because its value is _____. A foreign key holds another table's primary key, which is what _____ the two tables together, and it does not have to be unique in the table it sits in.