Relationships

In Microsoft® Access®, data can be stored in one database file where the file can contain multiple tables with defined relationships between the tables.

In the ongoing example the following table was used to store teacher information:

Of particular note is the School field in this table.  Each school will certainly have more than one teacher, so for every teacher that is entered into the database, the school's name will need to entered every time.  There are some inherent problems with this design:

• Using the entire school name in this field requires more storage space in the database
• User-entry errors could result in misspellings or undesired variations of the school name

To alleviate these problems, the design is modified to include two tables, Teachers and Schools:

A relationship between the two tables is defined such that for every unique School_ID entry in the Schools table, there may be many occurrences of School_ID in the Teachers table.  This is a one-to-many relationship as diagrammed below:

The field School_ID in the Teachers table is known as a foreign key.  If there are teachers that work at more than one school, how might the database design be modified to accommodate this?  View Answer