top of page

Primary Keys

Primary Key 3d-2.jpg

Primary keys in the architecture consist of a globally unique eight-byte key, which includes a system id and a record id. The system id identifies the system that created the record, while the record id is an incremented integer. These primary keys are automatically assigned to all types of data, including master data, transaction data, and system data.

Globally unique primary keys provide the following benefits. 

Globally unique id - The use of a globally unique primary key simplifies the process of exchanging data and updating it when it changes. As the primary key includes a system id that uniquely identifies the system that created the record and a record id that is an incremented integer, this makes the id universally unique, and can be used to ensure that the same data can be identified across different systems.

 

Compact  - The size of the primary key is important because it affects the size of foreign key indexes. The globally unique primary key is half the size of a GUID, making it more efficient in terms of storage and retrieval of data.

Simplifies the creation of common utilities - Because the primary keys are consistently formatted and named, it makes it considerably easier to create functionality in the framework.

 

Easy to reference - The primary key has a simple format of system id followed by a record id (1-123). Compared to a GUID, the globally unique primary key is more succinct and simple for developers and users to reference.

Efficient for storing data - Because the record id component of the primary key is an incremented integer, SQL databases can store data based on the primary key value, which causes the data to be neatly organized sequentially. This improves storage and retrieval efficiency.

Efficient for inserting records - The primary key leverages built-in functionality for generating primary keys, which makes it efficient for generating large amounts of data all at once, such as when journal entries are generated for a billing process.

 

Supports record governance - The primary key identifies the system that generated the record, which simplifies the process of maintaining which system governs a particular record. This helps ensure proper governance and auditing of data.

 

For more information on Primary Keys please check out the following video.  https://m1archive.s3.us-west-2.amazonaws.com/Videos/Primary+Keys.mp4. 

 

There are multiple functions for the primary key maintenance that start with the name "fnsysid*" which can be found here: https://www.3d-ess.com/sourcecode. 

 

The main function for creating primary keys is here: https://github.com/bkjenner/3dEnterpriseSystems/blob/ccd549238291795b8ed11b00426618c57e3e397d/200%20Functions/fnsysIDCreate.sql

bottom of page