J is for Join Table
A join table is used to resolve a many to many relationship with a flexible structure for data storage.
The FileMaker Platform works around a relational database. The structure of a relational database includes tables and relationships between tables. There are three major types of relationships:
- one to many
- many to many
- one to one
The most common relationship is one to many. This is where one record in table A may be related to many records in table B. Conversely, each record in table B is related to only one record in table A.
For example, consider customers and sales. One customer is (hopefully) related to many sales. Each sale is related to only one customer.
Other examples of one to many relations are:
- one venue – many events (each event = one venue)
- one employee – many payments (each payment = one employee)
- one diver – many dives (each dive = one diver)
- one student – many certifications (each certification = one student)
These are often represented with a crow’s foot notation where the crow’s foot is the ‘many’ end of the relation;
When working through the data structure for a database, we often come across many to many relationships. Some example are:
- houses and owners – one house has many owners (over time); one owner owns many houses
- students and classes – one student enrols in many classes; one class has many students enrolled
- cars and mechanics – one car can be serviced by many mechanics; one mechanic services many cars
What is the problem with many to many relationships?
While it is possible to create a many to many relationship in FileMaker Pro and other database platforms, it is generally not done. When we find a many to many relationship, it presents a problem with the storage of data associated with the relation.
For example, with a relationship between houses and owners, where would you store data for the sale price and date purchased? If you put fields in the house table, then you will need to store a price and a date each time the house is sold; if you put them in the owner table, you need a price and a date each time they buy a house.
A join table is the solution
To correctly model a many to many relationship, we break it down using a join table. This is a table that represents the relationship between the tables. There is often an action word that represents the relation:
- owners purchase houses; houses are purchased by owners
- students enrol in classes; classes take enrolments by students
- cars are serviced by mechanics; mechanics perform services on cars
In the case of house and owner, we add a table between called purchase. There is a relationship between owner and purchase (one owner makes many purchases; each purchase made by one owner). And there is a relationship between purchase and house (each purchase is for one house; one house can have many purchases).
Each purchase record relates to one house and one owner. The purchase record is the right place to store data such as sale price and date purchased. So this solves the data storage problem with one purchase record created every time an owner purchases a house.
Many to many resolved
Each table will have a primary key (named ID in each table below). The relationships are created to foreign keys (ID owner, ID house) in the join table.
When creating a purchase record, you need a process to select the owner and the house. The primary key (ID) of each is inserted into the foreign key fields of the purchase record to create the join.
Draw the tables and relationships required for the other many to many examples above. Name the join table and the foreign keys required.