Utilizing primary keys to unlock new possibility
Relational database applications depend on the most important fields in any table, the primary and foreign keys. These fields are central to the functioning of relational databases and many developers are failing to unlock their full potential. As the field that links one record to other records, if anything bad were to happen to this one field, all connections and relationships in the database can be damaged and unrecoverable. Protecting the sanctity of the key fields is of paramount importance to a developer. This article explores the qualities of good key management, including having unique, abstract, and internal keys.
The primary key is so important and basic to good database design that it is included as a default field in any new FileMaker Pro table. When a record needs to store a relationship to a second record in another table, the first record stores that second record’s primary key in a foreign key field on the first record. A basic requirement is that the primary key is unique within that table. Since related tables use the Primary Key to point to one specific record in another table, if it’s a duplicate the other table will point to two records.
The Unique Identifier
FileMaker introduced the function Get ( UUID ) in version 12, and it had a huge impact on generating unique primary keys for tables. Prior to its introduction many developers used sequential key generation. Sequential key generation simply increments a number by one every time a record is created. The primary keys will be 1, 2, 3, etc. This will create unique keys for each record, and it is how FileMaker generates its own internal record ID which is returned by the function Get ( RecordID ). However, the sequential keys have their limitations.
The first limitation is that sequential keys must be created by a single invariable source. If the database reverts to a back up, when that source was set to 100 as the next sequential value, and then imports the records created after that back up there will will be records with sequential keys past 100. When a new record is created, the sequential source will use 101 as the next key, duplicating a key already in the table.
Another limitation is records that are created offline or in another system. These records are created by a separate system with its own internal count, and when they are uploaded to the server, they may conflict with the primary keys created from the server. This can also happen when a company wants to merge multiple applications instances into one, or integrate the data from several different applications. That data will have multiple conflicting keys if they were all generated sequentially.
Universally Unique
Lastly, although the sequential keys are unique within the table, they are not universally unique. That means that every table has a record with a primary key of 7. Creating a relationship to the wrong table will still return a related record from the wrong table, because record ID 7 still exists even though it’s in the wrong table. Some developers will compensate for this by adding a unique table prefix before the sequential key, but it becomes difficult to insure prefix uniqueness the more complex the database tables and relationships become. And it still suffers from the sequential key limitations listed above.
For these reasons, universal uniqueness is an important quality of any primary key. That means they key is not only unique within the table, but is also unique across all tables. Fortunately, FileMaker has a simple function that can be used to generate a universally unique 16 byte string, Get ( UUID ). An advantage of this primary key, universally unique across tables, is the use of a generic foreign key.
Imagine a role table, where a person can have a role that is linked to any number of other tables, equipment, rooms, projects, but only one table for each role. Using UUIDs, a developer can use a person foreign key, which always relates to the person table, and a generic foreign key, which points to any other table. Since a UUID is universally unique, it will only point to one record out of every other table. There are no duplicate primary keys in any table, unlike sequential keys.
Keep Keys Hidden
The second quality of a robust primary key is to be abstract. Some developers will use concatenated key generation, which is not an abstract key. They might use several data fields, concatenated together that will most likely be unique across the table, such as “name.birthday.height”. There might be two people with the same name, but the chances of them having the same birthday and height is remotely small. Even though this key may be unique, it is not abstract.
An abstract key is not related to the data it represents in any way. Keys based on data are inherently fragile to changes in the data. When the data changes, the key cannot be updated or all relationships based on that key will break. While a birthday is not likely to change, names can change frequently, through marriage, nicknames, or personal choice. And even though a birthday doesn’t change, it could have a typo when put into the system and need to be corrected later. Soon enough, the keys meant to represent their records no longer represent their records accurately. Abstract keys insure that there is no assumption or expectation that the key represents anything but a link to the correct record.
Keys vs Identifiers
Another important part of abstraction is that the universally unique keys are not exposed to the user. They should exist behind the scenes, doing the work of relating tables while hidden completely from the user. For one thing, universally unique primary keys are long abstract alphanumeric values, “E47E7AE0-5CF0-FF45-B3AD-C12B3E765CD5”. The primary keys do not represent data, and shouldn’t be used by the user to refer to data. Separation Model best practices keep the data fields separate from functional fields. When keys are exposed to users, they become part of the user data about that record, instead of the system keys for maintaining the database relationships.
If a user wants to be able to uniquely refer to a record, such as an order number or a catalog item, that is a good use for a sequential identifier field for the user, as long as the sequential identifier is never used as a foreign key in other tables. In case any of the issues described above with sequential fields occurs, the identifiers can more easily be updated without breaking the relationships of the database.
Make Your Own Keys
The last quality of a strong primary key is to make sure it is internal. The developer should have full control of the keys that their database relies on. Often times databases incorporate external tables with their own unique identifiers. Developers should not rely on these external keys to base their own database relationships on for several reasons.
Firstly, for all the reasons above, there is no way to know that an external developer has created unique and abstract keys that are guaranteed not to change. If that external key fails in any way, all relationships built on it fail as well. Instead, use the external key for importing and updating data from the source system, but make your own internal UUID as new records are created and use them for the basis of table relationships.
Secondly, there is no way to know if that external data source will remain the same external data source for the life of your own application. For example, a payroll system may use its own unique and abstract key for all employees. If a developer uses this as their own key instead of generating their own internal primary keys, what will happen when the payroll company changes? All local relationships built on the external key will break when the new payroll company introduces their own unique keys. If internal keys were used instead, this would be a one time exercise to map one table to the new external keys. There is simply no way to protect the robust foundation of a database without maintaining unique, abstract, and internal primary keys.
Final Note
In FileMaker 17, a new function was introduced for generating unique primary keys, Get ( UUIDNumber ). Instead of returning a string value, Get ( UUIDNumber ) returns a universally unique 24 byte number. FileMaker states that using Get ( UUIDNumber ) instead of Get ( UUID ) will improve performance in relationships and searches. The performance gains appear to come from the way that FileMaker optimizes indexes for numeric fields vs text fields, even though the numeric UUID is larger than the text UUID. Any fields used in searches or relationships should be indexed for optimized performance. For versions after 17, a best practice is to use Get ( UUIDNumber ) in a number field for primary keys. Make sure the foreign key fields are also stored as numbers.
