One of the things many organizations want to have is a reference number that conveys meaning. For example, PO20999 tells the organization that they are likely in the Purchase Order table and that there are 20,998 previous purchase orders. In this article I’ll review the need for primary keys and the techniques I use to add another sequential field with meaning.
One of the rules of Relational Database Management Systems (RDBMS) is that every table has a field (or column) that has these four characteristics:
This field is known as the primary key. And the primary key must have some kind of value (Not Null), it cannot be changed once it is set (Immutable), it must be different from every other primary key in the same table (Unique), and only one field can be considered a primary key (Sole Key).
Sometimes people like the primary key to convey information. For example, take these six primary keys:
Even though you don’t know anything about the database you could probably make a few guesses based on these keys:
An alternative to an intelligent primary key is a surrogate key. This is a value that in and of itself, conveys no meaning. This can be a number:
When it is a number, it is usually sequential and increments by 1. We can no longer tell which table the key is from, and in fact, since none of these keys are duplicated in the list, they could all be from the same table.
Another type of surrogate key is the UUID (Unique Identifier ID). In FileMaker, this can look like this: BF7BA7C4-D7CC-4F05-8D58-4833977EEC13 A UUID is unique across all tables, not just unique within a single table.
Intelligent keys can be very helpful, especially for customers and vendors. Not the customer and vendor table, mind you, but the actual people who make up your customers and vendors. They will call you. Or at least, call your business. Or email. Or fax (I have one customer that sends and receives orders via fax. I can’t believe it, but that’s where their industry is).
When people contact you about a purchase order or an invoice, it is nice for them to have a manageable text string as a reference. 20998 is helpful. PO20998 is even better because it tells your customer service rep that they are dealing with a purchase order and not an invoice. BF7BA7C4-D7CC-4F05-8D58-4833977EEC13 is terrible. What person wants to read this back to someone over the phone just so they can get an order record up on their computer screen? So intelligent keys can be very helpful for both your organization and the people you serve.
I have found that organizations want their intelligent key to be without gaps. That is, if you have a purchase order of PO20998, then the next one better be PO20999. If, instead, the next purchase order is PO21000, then someone in purchasing might raise a stink “WHERE THE HECK IS PO20999?”, even though it never existed. That’s how we are, us humans. We like things in sequential order.
In FileMaker, you can create an intelligent key using the auto-enter serial field definition
The problem is, records get deleted. You definitely need to have rules in place to prevent unwanted deletions (e.g. Purchase Orders with line items cannot be deleted). But records will be deleted. It will happen. And when that happens, the auto-enter serial number does not automatically re-set. And then you will have a gap between your keys. A dreaded, awful gap.
My solution to this is to have an intelligent key if the organization wants it, but it is never the primary key. For that, I use a surrogate key (either a sequential number or a UUID). I don’t care about gaps in the primary key. Gaps in key values do not break any rules in RDMS. Gaps don’t prevent the primary key from doing its job. The intelligent key becomes an intelligent reference, but not a key. I never use them in connecting table occurrences on the relationship graph.
In the Universal Data Model where we have one order table, it is no longer possible to set the intelligent key as a serial number because the prefix (e.g. PO in PO20999) needs to change based on the type of order record. You can still use the auto-enter section, but instead of the Serial number you would use the Calculated value. Another way to set the intelligent key is to use a script trigger on the order type.
In the demo file for this series, the ORDER table has an intelligent reference field (remember, its not a “key”) in addition to the primary key (ID field in the demo file). The intelligent reference field has two rules:
There are a number of ways to approach this. In the demo file the way I solved this was to create a new Table Occurrence (TO) and relate it to the ORDER table occurrence.
Then, on the ORDER layout I put an OnObjectSave script trigger on the orderType field. Through this relationship I can then determine what the last number of that type of order record is. Adding a 1 to that number gives me the next sequential value, without any gaps. If that record gets deleted (and records will get deleted), then that number gets reused.
Now, since we have a number that can be reused, it bears repeating:
The script that generates the next value for that intelligent reference field has two methods. There are, undoubtedly, more.