Advanced Database – Create, modify a many-to-many relationship using a junction table.
For this example, create 3 new tables with the designs shown.
Note the Order Details table has 2 fields combined to create the primary key. To do this, hold down Ctrl key, click in the grey area to the left of each field, then right-click and select primary key. The reason for this will be explained in a later slide.
Then populate each table with the data shown.
A many-to-many relationship is used when a record in the first table can have matching records in the second and vice versa.
In this example, a single part may have many orders, and a single order may contain many parts.
Therefore, if we create a relationship between ‘Part Ref’ and ‘Order ref’ fields, it will be a many-to-many relationship. To do this we need a junction table.
We need a junction table as you cannot directly connect the two fields as there are multiple records in the Parts and Orders tables.
We can use the Order Details as the junction table. This is the reason we created two primary keys, so we can connect to both of the other tables.
Click Tools-Relationships, then add tables: Parts, Orders and Order Details
The junction table is the primary table, start by clicking ‘Order Ref’ field in the Order Details table and drag it over to the ‘Order Ref’ in the Orders table. (If you do it the other way around, it won’t work)
Then, click and drag ‘Part Ref’ from the Order Details table to the ‘Part Ref’ in the Parts table.
This makes 2, 1 – n lines, i.e. one-to-many. By linking through the junction table, overall it creates n-n, many-to-many.
Using a junction table has made a relationship between two fields, Part Ref and Order Ref, that would have been impossible otherwise.
If you don’t have a junction table, you can create one, as long as it contains foreign keys from both tables.
To modify the relationships, right-click on each line separately and select Edit.
