This is a story about a relationship, much like that of Romeo & Juliet, that was doomed from the start — the relationship that can never be. We should speak of an implied many-to-many relationship because there is no such thing in databases themselves. Where a many-to-many relationship is implied, we must remove that relationship and replace it with one-to-many relationships.
In this tutorial:
Required knowledge:
If you do create a relationship and it is a many-to-many relationship, Access will list it as Indeterminate when you inspect the join properties.
1. Scenario
You run a small library at your school and must keep track of all the books.
When you start, you might keep a simple handwritten list of the books being loaned out using pen and paper.
You soon realise that you will have to create some form of spreadsheet. To start, it might look something very similar to a manual paper version. The shortcomings of this spreadsheet become evident as the number of books grows.
Yes, it is time to create a database.
2. (Implied) Many-to-Many
You should quickly be able to work out that you need a table to store information about students (imported from CEMIS or other school database systems) and a second table to record information about the books.
The problem is that one book can be taken out by many students and many students can take out one book. This implies a many-to-many relationship between students and books.
What you need to understand is that the information that relates to the relationship between the students and the books they take out does not belong in either table as it describes neither students nor the books — rather it describes the relationship between them. We will see as we progress how we decide where we put what information, a process which is rather deceptively called Normalisation.
3. Foreign keys
To accommodate this relationship between the students and the books we create a new table, often referred to as a “link” or “linking” table. The Primary Key (“PK”) from the tblStudent table and the PK from tblBooks are added to this new table as Foreign Keys (“FK”). Relationships are then created between the tblStudents and the link table and tblBooks and the link table. These relationships are created on the PK-FK combinations.
In this instance, the FKs — bookID and studentID — become the compound PK for the link table, tblBookLoans. The implied many-to-many relationship is solved using 2 one-to-many relationships. This is a common pattern in relational databases.
