The Join Table
In my coaching of FileMaker developers two topics seem to come up most often. For newer developers it is summary reports and how to build them. The other issue that comes up often—even with experienced developers—is the “Join Table” used to create Many-to-Many relationships.
There are three basic relationships between entities or tables:
This article assumes that you are familiar with basic relationships and key fields.
One-to-One Relationship: Though the simplest relationship, it is probably the least used. In this relationship a record in one table is related to one and only one record in another table and visa versa.
Each department in a company has only one manager and each manager only runs one department at a time. The Marketing department’s record in the company database will have its Manager’s unique People ID. In the People table this is the “Primary key” but when this value is stored in the Department record it is a “Foreign key.” It links the department with only one person record. (In my figures the blue text are fields that store attributes about each entity.)
One-to-Many Relationship: This is the most common relationship. A record in one table can be related to many records in another table. For example, in a FileMaker Pro file with a People table and a Companies table. One Person can be related to no company, one company or many companies. John Doe is an accountant with ACME Corp. so his People record is linked to ACME’s Company record. His wife, Jane, is an engineer at ACME so her People record is also linked to ACME’s Company record. If you look at ACME’s company record you would see both John and Jane listed as employees. To link John and Jane to ACME, their People record would have a field to store the unique Company ID number for ACME. Every company in the Company table has a unique Company ID which is that company’s “Primary Key”. Here is a simple diagram that illustrates the relationship between People and Companies:
Many-to-Many Relationship: Now, what if John takes a night job with BucksBack Tax Preparation Service to make extra money during tax season? With the structure above John can only be related to one company at a time. We could add a field called “Company 2 Key” so John can be linked to BucksBack. But what if John takes a new job with Lexicality, LLC, and we want to capture his job history? Create “Company 3 Key”! You see how this can get out of hand. Also notice that the Job Title and Hire Date fields in John’s record will have to change with each job change.
Think about the relationship between John and each company. John was an accountant for ACME, is a part-time tax preparer for BucksBack and now is the Comptroller for Lexicality. These job titles define the nature of John’s affiliation with each company. This affiliation is an entity in itself deserving its own table and would look like this in our diagram:
“Affiliation” joins a person with a company by storing the People ID and the Company ID in one record. John will have three Affiliation records: one joining him to ACME as a former accountant, one joining him to BucksBack as a tax preparer and one joining him to Lexicality as its Comptroller. The Affiliation table is a “Join Table” because its records join two tables’ records. Notice that we moved the Job Title and Hire Date to the join table because these fields tell us something unique about each the Affiliation.
Many-to-Many relationships are not difficult to create and manage in FileMaker Pro and they can add powerful functionality to your software. They also allow you to properly represent the connections between your data.
I hope this has helped you “get your head around” the concept of the “Join Table.”