Keeping the relationship graph under control is key to maintaining a simple and easy to update application. Good data model practices keep an application from exploding into bloatware that becomes impossible to work on. Developers often add fields to support functionality in the most convenient location and the most convenient location is rarely the correct location. In this article we explore a path to good data model design for networked databases that share tables across multiple applications in a multi-user environment. These best practices can be implemented in single user applications as well, but the benefits multiply as the environment becomes more complex.
Take the example of trying to show a list of equipment for a particular room from the context of a person record. The convenient thing to do is to add a global field to the Person record that can be set with a room primary key, creating the relationship necessary for linking the equipment. Except, now there is an additional global field in the Person record which has nothing to do with the person’s data. This additional field is only relevant to the application creating this connection, and is useless to any other applications linking to people data.
Sure, it’s only one field the developer says, but after a couple of years of each developer for each application adding each field needed for a specific function, the people table has more functional fields than property fields. And since each functional field is relevant to different applications, it’s difficult to deprecate and remove them without evaluating every application that connects to the table to see if it’s still in use.
Separation Model
The solution is simple, separate form from function with a data model that keeps data tables clean and clear by utilizing a Separation Model. The Separation Model is derived from the Model-View-Controller programming paradigm. The Model is the data tables and relationship graph. The View is the interface layouts and global fields. And the Controller is the functional scripts and variables. Keeping these components separate will greatly simplify your design and development.
The separation model implies two things. First, separate the data tables from the interface, and second, separate the data fields from the functional fields. Simplicity always comes with a cost, and there is a learning curve to maintaining separation of these components. The value comes with easier to maintain code, since the View can be updated without impacting the Model, and the Model can be updated without impacting the Controller. Every other path inevitably leads to tightly bound dependencies that are impossible to untangle when it comes time to make updates and upgrades to the application.
Separating the Model
The first part of the Separation Model is usually accomplished by creating a file with only data tables in it, and connecting a second file with the layouts and scripts the user will use for the interface. This will separate the Model from the View. Here is an excellent overview by Matt Petrowski at FileMakerMagazine.
An advantage of this approach is that the interface can be updated separately from the application data. Otherwise, data from the old version would need to be migrated into the new version before going to production. Any new interface versions can easily be tested against test data in a copy of the production data file. The interface file can have data tables, but the important aspect is that they are not relevant to the production data of the application.
The second part of the Separation Model is to separate the Model from the Controller. The Model and Controller overlap when the interface needs to save values to fields for use later after the script ends, or for dynamic relationships. There are a couple of approaches to address these uses while maintaining separation using a couple of daily implemented best practices. Saving values to fields for later use can easily be accomplished with a globals table that exists in the interface file which is separate from the data file. And creating dynamic relationships can be done by utilizing the Super Buoy approach described below.
Layout Anchor Buoy
The first, most important, relationship graph practice is to organize table occurrences in an anchor buoy fashion. The anchor table occurrence is the context table, the table that layouts are based on. From the anchor, relationships to other tables are organized from left to right. The context table is the “parent” and each table related to it are it’s children. Moving to the right, the children of the child tables are found.
By keeping the parent’s name in the name of the child table, any table can be traced back to it’s parent context table simply by looking at the name. An “@“ symbol is used to denote the context table and all the children of that table, and a “|” is used to separate the parent table name from the child table name. This allows all the table occurrences from the same context to sort to the top of the table selection sort, in the order of their relationships.
Super Buoy
The Super Buoy is a table which is specifically built only of global fields meant for building dynamic relationships to other tables in the application. It’s an adaptation of the Selector Connector method pioneered at DevCon 2015. By separating the global fields into their own table, it keeps the data tables free from fields that aren’t relevant to the table entity. The Super Buoy is so useful, that it’s given it’s own naming conventions that follow to all its child tables. A “#” symbol is used to denote that it’s a control table, used primarily for scripting. This puts the control tables conveniently below the context tables in the table selection sort order.
The Super Buoy needs to be connected to the context table via a cross join, and there needs to be at least one record in the globals table. After that’s connected, the interface has access to all the functional fields it could need. Since the global table is in the interface application file, the developer has access to modify it to the needs of the specific application. As an example, setting an account field will allow the interface file to link to the user preferences and using a “created by” field, to any records that user created in another table.
Naming Conventions
Here are some best practices to help keep your relationship graph under control:
“@“: Used to note context table occurrences and key values
@Person, @UUID ( primary key ), @PROJECT ( foreign key ), $@PERSON
“|”: Used to separate table occurrence table names
@Person|Role|Project
“#”: Used to note Super Buoy tables, summary or unstored calculation fields, and custom functions. These fields shouldn’t be used in relationships, values lists, or finds.
#Globals|Person, #flag.count, #JSON (key ; value )
“.”: Used in Table Names to separate words, such as in join tables
People.Cars, @Person|Person.Cars|Cars
“~”: Used in Transaction tables, utility fields, and calculation scoped variables
~Roles, ~removed, Let ( ~value = “”; “” )
“{“: Used to note a table that has been skipped via a relationship. Sometimes a table can be skipped via the relationship, “course{Roles” communicates that the Roles table is linked directly via the class course relationship, without going through the course table, which could be different than the class roles, ie “@Class|Roles”.
@Class|course{Roles!teachers
“!”: Used to note a comment about the purpose of a table and unused calculation variables. “!teachers” communicates that this relationship to roles represents the course teachers.
@Class|course{Roles!teachers
“?”: Used to flag a table or field as deprecated
“_”: Used for internal scoped variables and custom function getters
$_variable, _JSON
