Tables, rows, columns and keys
How a database organises information, using a school register as the example.
Lesson 2 of 33 · about 10 minutes
The lesson
Think of a school attendance register. Across the top are headings: Roll number, Name, Class. Each line below is one student.
A database table works the same way. The headings are columns: each one holds one kind of information, like a name or a price. Each line is a row: one complete record, like one student or one book.
Two students can have the same name. So how does the school tell them apart? By the roll number. No two students share one. In a database this is called the primary key: a value that identifies exactly one row.
A database usually has several tables that are linked together. Our bookshop has a books table and an authors table. Instead of typing "Agatha Christie" next to every one of her books, each book stores the author's ID number, say 6. That ID points to her row in the authors table. A column that points to another table like this is called a foreign key.
| books.id | title | author_id |
|---|---|---|
| 10 | Murder on the Orient Express | 6 |
| 11 | And Then There Were None | 6 |
| authors.id | name | country |
|---|---|---|
| 6 | Agatha Christie | UK |
Why bother? If an author's name is spelled wrong, you fix it in one place, and every book that points to that author is correct straight away.
- A table is like a register: columns are the headings, rows are the records.
- A primary key uniquely identifies each row, like a roll number.
- A foreign key links a row to a row in another table, so information is stored once and shared.