Building Relationships
The relationship is the cornerstone of database design. Leveraging how tables relate to each other enables complex reporting, analysis, and interface designs. Getting Started with Good Data describes basic approaches to modeling data in a relational database. Taming Table Occurrences introduced specific examples of good practices using the Relationships Graph. Now, let’s look at additional techniques of the data relationship graph.
Relationship Types
The primary relationship type is one-to-many. Layout portals are lists that are built from these relationships. Starting from the record on the table occurrence that the layout is based on, portals are used to show the all the related records. If the relationship type is one-to-one, a portal is not needed. Just putting the fields from the related table occurrence will show the first ( and only ) related record.
FileMaker supports a many-to-many relationship type, using a multikey to list all the primary keys from another table in a single field. While this approach will work, it is highly discouraged since managing the list of keys in a single field can become challenging. Instead, a separate “join” table should be used between the two related tables to manage the more complicated relationship. Change a many-to-many relationship into two one-to-many relationships. This makes the relationships much easier to manage with the separate join table.
In the example below, a one-to-many relationship is demonstrated by a person who may have many cars, but a car is only owned by one person. In the next example, a many-to-many relationship is shown by a driver who drives many cars and the cars may have many drivers. In the many-to-many case, a join table ( Car.Driver ) is used to track the relationship between a driver and all their cars, and a car with all their drivers. The many-to-many case becomes two one-to-many relationships.
Relationship Criteria
Previous articles focused on relationships based on key values ( primary key and foreign keys ) being equal. That is a single criteria relationship. In the example above, Person::@UUID = Car::@person. These relationships are useful for showing all related records. Sometimes, only a subset of the related records need to be shown. For an example, all customer invoices from last month. In this case there are multiple criteria, all customer invoices, and dates equal to last month.
A multicriteria relationship accomplishes this by relating tables by multiple fields, a primary key and a date. Although it can be done with a single criteria relationship, and then using a portal filter on the layout to filter the dates, it will be more efficient to filter at the relationship with a mulitcriteria relationship. Filtering on the portal requires all records to be loaded, and then the filter is applied. Filtering at the relationship only loads the matching records. When customers have a long history of invoices, the portal filter will perform worse over time.
Relationships can also contain comparative operators besides “equal to”. They can relate two fields that are greater than, less than, not equal, and other operators. As an example, a date field can be edited by the user to filter the invoice results to that date. While the field is empty, no records are shown, and once a date is entered, the invoices from that date appear.
Related records can only be created directly from the equals comparative operator. This selection can be made when editing the relationship, by double clicking on the relationship operator ( “<” in the example above ). This can be used to greatly simplify the process of creating related records for the user, all they have to do is type into a field from the related table and the record is automatically created with the required relationship. In order to create records with a more complicated comparative relationships, a Super Buoy technique can be used.
Creating Super Buoy Relationship Records
The Super Buoy is a special table of global fields that uses selector fields to relate to specific records that might be outside of the layout’s context. The Selector-Connector method sets a Selector with a key value, which then creates a relationship Connector to the desired record in the desired table. This method can be used to view and edit records, and it can also be used to create new records.
When the Selector key is set, it points to s specific record. However, if it is empty, and the Allow Creation of New Records selection is set in the relationship, then it can be used to create a new related record. This sets the empty global field to the new record’s primary key automatically through the relationship, allowing the new record to be edited by the user through the relationship. That may include date fields or other criteria that wouldn’t be able to be created through the multicriteria relationships.
An advantage to this approach is its support of transactional methods. In database terms, a transaction is a series of steps that change a database from one stable state to another stable state. A transactional method won’t allow the database to be saved in an unstable state. The requirements are described in the ACID test. A transaction should be all or nothing, if it doesn’t complete it is completely reverted, as if nothing happened. Using the Selector-Creator, multiple related records in other tables can be created before committing all required records at once. By completing the transaction with a single commit, the process prevents child records from being created before their parent record is saved, and possibly creating an incorrect database state.
Flexible Source Keys
The Source Key is a generic foreign key that can be used on any table. Usually, a foreign key is used to point to a specific record in a specific table. The Source Key points to a specific record in an unspecified table. It requires the keys in all tables to be universally unique so there is no overlap across tables. This can be useful in the following example, a roles table that tracks every role for a person. That person may have a role on a project, or a role on a document, or a role on a job. It’s called a Source Key because the source of the record determines which table the key relates. A document application can use the roles table Source Key to point to the document table, while a project application uses the roles table to relate people to projects records and uses the project primary key in the roles Source Key field. Since the relationship filters out all other records from the different applications, it can be used in multiple applications without creating conflicts. Imagine the benefit of having one table that tracks all of a user’s Approvals in one table, and being able to aggregate all approvals in a single list for the user, no matter what type of approval it is.
Summary Fields Across Relationships
Summary fields are useful for summing and counting fields within a table, but they become even more powerful when used across relationships. A summary field always presents the summary of the found set. For a layout table, that means the current found count. For a one-to-many relationship, that summary uses only the related records. In the example above, there is a customer table and an invoice table and we want the sum of costs of all the invoices for that customer. One method would be to add a calculation field to the Customer table that equals:
Sum ( Customer|Invoices::Cost )A much more powerful and flexible method is to add a Summary field to the Invoice table instead. With the Invoice::#total.cost field, it can be used to sum the invoice costs across any relationships, organizations, locations, products, that it’s a part of. To see a total of invoices for a specific product, just add the Invoice::#total.cost field to the product layout. To see a total of invoices for a specific customer, just add the Invoice::#total.cost field to the customer layout.
New Tools for the Toolbox
There are many features that are unlocked by designing your relationship graph to do more than just relate data. Join tables can be used to simplify the management of more complicated many-to-many type relationships. The relationship graph can also be used to filter related records for layout portals. Records can be created transactionally, through Selector-Creator relationships that can perform multiple actions in a single commit. Using UUIDs and the flexible Source Key allows tables to support relationships to multiple types of tables, aggregating information across multiple uses. And the summary field proves itself as a flexible and powerful calculation that can be re-used across any type of relationship. Share which tools you like the most in the comments below.
