AcumatiCares
Introduction
In a database application, table keys play an important role. Furthermore, when you design new screens or even a module, the decision on what key scheme to use will not only influence how you code your DACs but also will influence to some degree how easy your application will be to use and integrate. In the early days of working with Acumatica, I used to scratch my head a lot on making decisions in choosing my keys or how to use an ID versus a CD in Generic Inquiries.
Why you should care
Deciding to change the primary key of one of the tables is very hard once you have installed the application to other sites which usually means that you will have to live with your choice for a very long time – if not forever. Before we go further, let’s define what we mean by keys[1].
Primary Key (PK): A column or group of columns in a table that uniquely identifies every row in that table. Primary Keys can’t be duplicates – meaning the same value can’t appear more than once in the table. A table cannot have more than one primary key.
Candidate Key: Also a unique key to identify a record uniquely in a table but a table can have multiple candidate keys. Examples of typical candidate keys are emails, company name, employee ID, DUNS[2] Number.
Alternate Key: A column or group of columns in a table that uniquely identifies every row in a particular table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not the primary key are called alternate keys.
Natural Key: A column or group of columns in a table that uniquely identifies every row in that table in a manner that is humanly recognizable. A code or a document number is often referred to as a natural key.
Surrogate Key: An artificial key that aims to uniquely identify each record is called a surrogate key. This kind of key is used when you don’t have or don’t want to use a natural key as the primary key.
Foreign Key (FK): A column or group of columns in a table that is the primary key of another table.
Difference between key and index
As mentioned earlier, a key is a column or group of columns or a group of values if you will. An index, on the other hand, helps databases locate the physical place where the row is stored based on the values provided by the key. Therefore, the index is like a dictionary of keys vs locations. The database uses the index to quickly find the row related to the key values.
What Scheme to Use
Some database applications often use just one scheme (type) to identify keys. In the past, I worked with one ERP that used GUIDs as all their keys, another used only natural keys while a third one used only surrogate integers. Each one, as we will see, has their pros and cons. Acumatica uses multiple schemes for table keys depending on the need and context which makes it difficult to choose one over the other, but they all have their place in the database. But let’s start with the simplest of keys and work our way toward the more complex ones.
The Natural Key
A natural key is a simple value (such as string) that clearly identifies the meaning of the row. If I say for instance “US”, “CA” or “MX”, you instantly know what I am talking about countries. If I say “H0H 0H0[3]” or “90210[4]”, you probably know these too – although this is a bit more difficult. Lastly, if I say “NONTAXABLE”, I don’t have to explain anything it. Although it is rare these days that anything is non-taxable, per se.
Pros: Highly recognizable, easier to understand, shorter than others, gives usage context, easier to use in database queries.
Cons: The size (usually fixed) must be decided ahead of time so they cannot grow more easily. Small sizes lead to being unrecognizable codes. Once the key has been saved, it cannot be changed without difficulty.
The Surrogate Integer Key
The surrogate integer is one of the most used key in database applications. They take very little space (typically 32 bits), can automatically increase, and the numbers of rows can be quite substantial (around 2 to 4 billion depending if signed or not). Such a key will be visible or not depending on its usage.
Pros: Efficient storage and database access, most tables have a small number of rows, Meaning keys are easier to read and use in queries, auto-incrementable, simple model, and liked by most developers.
Cons: Their values are not easy to recognize, typically require a joined table to understand the meaning. Once the key has been saved, it cannot be changed easily if at all.
Suitable for:
- Tables with many rows
- Table for processing rows
- Sequences of ordered rows
Some examples:
The Surrogate GUID Key
An universally unique identifier (UUID) or globally unique identifier (GUID) is a 128-bit (16-byte) value that is normally divided into five groups of varying lengths. The surrogate GUID is widely used in many database applications. Its strength resides in its universality and almost zero likelihood of a collision. They are unique not only on a given system but across all systems. It makes them ideal for exchanging data or customized applications between systems. Please note that there are several variants defined by the RFC 4122 standard but we will not discuss them here. Other non-conforming GUIDs exist as well.
Pros: Universal and unique due to their nature. They are well documented and standardized. Their unrecognizable nature makes them a good candidate for “secret” references. Given a GUID, it is virtually impossible to guess a future value, although some variants are weaker in this aspect.
Cons: They are difficult to write and impossible to remember. Therefore you must use a join statement or a copy and paste action to use them for database queries. Their value is virtually unrecognizable, so they give no context of their specific uses.
Some examples:
What about Acumatica keys?
Acumatica has done a great job to choose its keys although it might not seem this way at first glance.
The Natural Key
A lot of Acumatica tables use a natural key when it is appropriate.
Some examples:
Suitable for:
- Code names (TaxZoneID, TermsID, CountryID, StateID, etc.)
- Class names (ItemClassID, CustomerClassID, etc.)
- Documents (SOOrder.OrderType, SOOrder.OrderNbr, GLDocBatch.Module, GLDocBatch.BatchNbr, etc.)
The Improved Surrogate Integer Key
When I started with Acumatica, this key scheme was quite intriguing to me until I really understood the power behind it. This key scheme is made of 2 parts:
- The real surrogate key, a.k.a. the ID, which is the real physical key of the table (in most cases a 32-bit integer but sometimes a 64-bit integer).
- The visible natural key, a.k.a. the CD, is the key that is visible to the user.
The idea behind this is that the table is using the integer ID with all of its pros while the user sees the CD with all of its pros. The trick behind the curtain here is that the physical table will use the ID as its key, but the DAC (Data Access Class) will use the CD as its key in order to order the rows on the screen by CD instead of ID. This allows the user to navigate from one CD to another in a natural way while still maintaining the table with an integer key. Furthermore, all Foreign Keys referencing the table will use the ID as the physical FK but will again show the CD for the user. This scheme is used for the most important tables such as all of the Business Accounts (Customers, Vendors, Branches, Companies, Employees) but also the most used tables (Inventory Item, Warehouses, GL Accounts, Sub-Accounts, Projects, Tasks, Assets, Routes, etc.).
The other nice feature of this scheme is that the user can also change the CD related to the ID using a global action call CHANGE ID. Using that action, the user is able to “recode” keys as the business and implementation progress.
Suitable for:
- Business Accounts (mostly because you might want to change the key later)
- Changeable key of widely used tables
In the next 2 GISTs, notice how the table’s physical key (see the [BAccount_PK] in BAccount Table) is different than the DAC key (see IsKey = true in the AcctCD field).
See the usage of the CHANGE ID action:
The Improved Surrogate GUID Key
Very similar to its integer counterpart, this key scheme is made of 2 parts:
- The real surrogate key, a.k.a. the ID, is the real physical GUID key of the table.
- The visible natural key, typically a string name, is the key that is visible to the user.
Suitable for:
- System object references
- Global identifiers of shared values
In the next 2 GISTs, notice how the table physical key (see the [WebHook_PK] in WebHook Table) is different than the DAC key (see IsKey = true in the Name field).
Contrary to the typical ID/CD, the screens using this scheme do not have a global action to CHANGE ID although one could easily be created.
The Detail Key
A detail key is a key used for a table that represents the children of another table. Typically, you take the summary table (the parent) and add another key field to it. Again, multiple patterns exist, and we will see which one is favored.
The Simple
A simple design for that kind of detail key is to take the Summary key and add the value of the primary candidate key for the detail. This (fairly rare) design should be favored only if you want to simplify the uniqueness of your key but without having the advantages of the next design.
Examples : GITable, CashAccountDetail, State
The Good
A good design for detail keys is to take the Summary key and add a LineNbr to it. Acumatica favors that in the majority of cases for multiple reasons:
- It’s easy: just a simple integer and voilà
- It’s automated: by using the LineNbrAttribute, you can automatically generate the LineNbr.
- It’s sortable and drag/droppable: if your DAC implements ISortable AND you use a PXOrderedSelect
- It allows for “duplicates” when considering the primary candidate key that is usually found on the detail row (InventoryID for instance)
- It allows null values for candidate keys which would typically make the key unique
Examples: SOLine, POLine, ContractDetail, ARTran, APTran, GLTran, etc.
The Shared
Another interesting design choice for a Detail table is not using the summary key at all and create a key made with a Surrogate Integer and add the NoteID of the Summary as the “parent”. This design allows the detail table (such as CRRelation) to be shared by multiple other summary tables (such as CRLead, CRCase, CROpportunity, CRContact, BAccount, etc.). However, this typically requires a special version of the PXSelect (such as CRRelationsList) to handle the relationship with the parent.
Example: CRRelation
Conclusion
I hope I’ve helped a bit in unlocking the mystery behind the choice of keys in Acumatica. What’s important to remember is that “One size fits all” does not apply to Acumatica. Using a natural key, although the easiest scheme to use, is not the best choice for all contexts.
Over time, I’ve noticed that if you understand the keys of an application, you will understand a lot with just a single look.
Happy queries to you all.
____________________________
Footnotes
[1] Since Acumatica is a multi-tenant application, its table keys almost always include a CompanyID field. We will ignore this fact for the sake of simplicity.
[2] Data Universal Numbering System, abbreviated as DUNS or D-U-N-S, is a proprietary system developed and managed by Dun & Bradstreet that assigns a unique numeric identifier, referred to as a “DUNS number” to a single business entity.
[3] Santa Claus postal code (in Canada obviously).
[4] A famous US television series for older folks like me.
