Linked database
Designing relational databases that use two or more linked tables to store data is the way to avoid insert, delete and update anomalies.
It is possible to split the data from the previous example into two different tables. These can be linked them together using a field that is relevant to both tables:
- a Teacher table could store details on each member of staff - ID, forename, surname, department ID
- a Department table could store details on each department - department ID, department, phone number
- both tables are linked because they share a field - department ID
Teacher table
The ID is the primary key and is a unique value - two teachers cannot share the same ID.
| ID | Forename | Surname | Department ID |
| 1 | Colin | Arthur | 001 |
| 2 | Laura | Brown | 001 |
| 3 | Stephen | MacLeod | 001 |
| 4 | Scott | Sinclair | 002 |
| 5 | Michelle | Wie | 002 |
| 6 | Ross | Dyett | 003 |
| 7 | Ian | Anderson | 003 |
| 8 | Betty | Flood | 004 |
| ID | 1 |
|---|---|
| Forename | Colin |
| Surname | Arthur |
| Department ID | 001 |
| ID | 2 |
|---|---|
| Forename | Laura |
| Surname | Brown |
| Department ID | 001 |
| ID | 3 |
|---|---|
| Forename | Stephen |
| Surname | MacLeod |
| Department ID | 001 |
| ID | 4 |
|---|---|
| Forename | Scott |
| Surname | Sinclair |
| Department ID | 002 |
| ID | 5 |
|---|---|
| Forename | Michelle |
| Surname | Wie |
| Department ID | 002 |
| ID | 6 |
|---|---|
| Forename | Ross |
| Surname | Dyett |
| Department ID | 003 |
| ID | 7 |
|---|---|
| Forename | Ian |
| Surname | Anderson |
| Department ID | 003 |
| ID | 8 |
|---|---|
| Forename | Betty |
| Surname | Flood |
| Department ID | 004 |
Department table
Department ID is the primary key and is unique for each department.
| Department ID | Department | Phone number |
| 001 | ICT | 300 |
| 002 | English | 301 |
| 003 | PE | 302 |
| 004 | Geography | 303 |
| Department ID | 001 |
|---|---|
| Department | ICT |
| Phone number | 300 |
| Department ID | 002 |
|---|---|
| Department | English |
| Phone number | 301 |
| Department ID | 003 |
|---|---|
| Department | PE |
| Phone number | 302 |
| Department ID | 004 |
|---|---|
| Department | Geography |
| Phone number | 303 |
The tables are linked because the primary key (Department ID) in the Department table is also needed in the Teacher table 鈥 so that you know which department each member of staff is part of.
In the Teacher table, Department ID is known as a foreign key.
When you use linked tables in a relational database you reduce the likelihood of insert, update and delete anomalies. In this example, moving to linked tables means:
- a new department can be added without having to add staff information alongside it.
- if a member of staff were deleted, such as Betty Flood, you would no longer lose the information held about her department.
- if the ICT phone number had to change, it would only have to change once in the Department table.