Linked database
The way to avoid insert, delete and update anomalies is to design relational databases that use two or more linked tables to store data. In our example, it is possible to split the data into two different tables and to link them together using a field that is relevant to both tables.
The first table could store details on each member of staff. This table stores their ID, forename, surname and department ID. The ID is the primary key and is a unique value - two teachers cannot share the same ID.
Teacher Table
| 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
This table contains three fields, department ID, department and phone number. Department ID is the primary key and is unique for each department. The tables are linked together because the primary key in this table (Department ID) is also needed in the staff table 鈥 so that you know which department each member of staff is part of. In the staff table, Department ID is known as a foreign key.
| 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 |
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.