FileMaker has a good overview of the different types of relationships you might create when building a database system in its help section. I wanted to illustrate it better for a client, this is my attempt.
One-to-Many Relationships are the standard way of building a relational database. Sometimes a developer comes up against situations where they need something more complicated. Think of instances where you might need Many-to-Many Relationships. In the situation I am working on, we needed a way for multiple Customers to be linked to a single Form. The form was a waiver and customers were signing up for themselves and their children at the same time on the same waiver form. We needed a way to link the waivers not just to the parent, but also to the children.
Standard database relationships are created by linking ID numbers between data tables. These fields are referred to as key fields. The image above is an example of a regular database relationship—one record (the Customer) can be linked to multiple (Form) records. In this example a Customer record could be continuously updated with a more current form — One Customer to Many Forms.
According to FileMaker’s Help page on Join Tables, “In relational database design, a many-to-many relationship is not allowed. Consider the example of keeping track of invoices. If there were “many” invoices with the same invoice number and one of your many customers called up asking about that invoice number; how would you know which specific one they were referring to? The answer is you could not. An invoice should have a unique “ID.”
To get around the problem of having a many-to-many relationship you need to break apart the many-to-many relationship into two one-to-many relationships. Using a third table, commonly called a “join table”, does this. Each record in the “join table” would have the foreign key fields of the two tables it is joining together. Nothing special needs to be done with the foreign key fields in the join table as they will get populated with data from the other two tables as records are created. It is not uncommon for a join table to have a lot of records in it – since records are created in the join table as records are created in the two tables it joins.”
Adding a Join table between the two existing tables allows the possibility of linking multiple records to the same record. More than one Customer record can be linked to a single form.
This example imagined above might be appropriate where two Customers are from the same family and they share a single form. This setup also supports the previous capability of having one Customer linked to multiple Forms.
Another common example of a join table use is a school with Students, Signups and Classes. Each student signs up for multiple classes and each class can have multiple students. The Signups data table is the join table.
Customer data table is linked to the Join table. Join table is linked to the Waiver table.
Join table has at least these three fields:
- ID Customers
- ID Waivers
- ID Join
From the Customers layout, we can reference the Waivers table (one jump away on the relationship graph) through a portal.