All of the data in the database is stored in this one place. The student database example that we looked at in the previous section was a flat-file database...
Why would you want to have more than one database table?
Take a look at the student database example....
- Class (5B)
- Tutor (Mr noggin)
- Room (56)
Repeated data in a database is generally considered a bad thing:
- It wastes space in the database
- It takes time to input, typing the same data over and over (and mistakes may be made)
- It is a pain to update (if class 5B gets a new tutor, we have to find every 'Mr Noggin' and change it to the new name)
So, now the main Student table just contains data directly related to students, whilst the new Class table contains data directly related to classes.
Note that both tables are independent, and each one has its own key field / primary key:
- Student table key field is student ID number
- Class table key field is class code
Linking Tables - Relationships
The common field in both tables is the Class field.
We use this field to create a relationship (link) between the two tables...
When a key field from one table appears in a different table (e.g. the Class field in the Student table), we call this a foreign key.