Time and Time Again: Managing Time in Relational Databases, Part 14 - Versioning with Surrogate Keys
InfoManagement Direct, November 30, 2007
Beginning with Version Pattern 6, we will use surrogate version keys in all versioned tables. But note that the phrase “surrogate version keys” should be read as “surrogate keys for versions,” and not as “keys for surrogate versions.” We are recommending the use of surrogate keys for versioned tables. We are not introducing a concept of “surrogate versions.”
Neither the chart of version patterns nor the chart of this series are included in this article. The reason is that this discussion of surrogate keys is relatively self-contained and makes few references to earlier articles in this series.
Advertisement
We begin by defining our terms.
Surrogate and Other Kinds of Keys: Definitions
Primary Key. The column or columns of a table in a relational database that the database management system (DBMS) recognizes as a unique identifier for that table, and as the column or columns whose values will be duplicated in foreign keys in order to create relationships.
Comment: The second half of the definition, the part following the "and," is necessary. The reason is that a DBMS is able to recognize and accommodate more than one unique identifier for a table. Any column or columns over which a unique index is defined is as much a unique identifier for a table as is the primary key. Once one of those columns or groups of columns is designated the primary key, the DBMS knows that their values will appear in foreign keys whenever a relationship to their rows is created.
Foreign Key. The column or columns of a table in a relational database that the DBMS uses to establish a relationship between that table and another (not necessarily distinct) table. At the instance level, foreign keys relate pairs of rows such that each foreign key value is identical to the primary key value of a row in the referenced table.
Surrogate Key. A primary key whose values are system-generated and are either hidden from business users or exposed with the understanding that no meaning other than their function as unique identifiers should be assumed true of them.
Comment: the second half of the definition, the part following the "and," is necessary. The reason is that system-generated primary key values may be exposed to business users. A typical example is system-generated document numbers for invoices, purchase orders, receipts, etc. The exposure does not mean that those primary keys are no longer surrogate keys. But, if exposed, such keys must be exposed to business users with the clear understanding that their only role is to identify, and that no other meaning can reliably be given to them.
Because this has turned out to be an excellent example with which to illustrate the concepts of meaning and semantics, the next section will be devoted to elaborating on it. This is an aside to our main theme of introducing the use of surrogate keys with versioned tables, but we think it is an important one.
Business Key. A unique identifier for a table, all of whose columns express business meaning.
Natural Key. A primary key which is a business key.
Semantics and Meaning, Illustrated With a Surrogate Key Example
Page 1 of 5.
