Databases Reference
In-Depth Information
But in a temporal table, multiple rows may represent the
same object, and so all of those rows will have the same business
key. Consequently, we cannot guarantee that each business key
points to one and only one object by defining a unique index
on it. Nor can we simply extend the scope of the index by defin-
ing a unique index on the business key
plus
assertion begin and
end dates and effective begin and end dates. On the other hand,
once SQL supports a PERIOD datatype, then new index methods
already defined by computer scientists could enforce uniqueness
on the combination of a business key plus an assertion and
effective time period.
A business key can be made up of any number of columns.
For example, the business key for the Wellness Program Enroll-
ment table consists of two columns. Each column is a temporal
foreign key which points back to an object in a parent table. The
Wellness Program Enrollment table is a temporal associative
table, expressing a many-to-many relationship between wellness
programs and the clients that enroll in them.
Business keys are unique to the objects they designate. But
in temporal tables, a business key may appear on multiple rows,
and consequently their uniqueness cannot be guaranteed simply
by defining a unique index on them. Nonetheless, they have an
important role to play. We discuss business keys, how the AVF's
enforcement of temporal entity integrity guarantees that no
two objects will ever have the same business key, and how busi-
ness keys help the business user clarify her intentions when
submitting transactions to an Asserted Versioning database, in
Chapter 9.
Foreign Key Mapping Metadata
The foreign key mapping metadata table, shown in
Figure 8.6
,
lists the foreign keys used by tables that were originally conven-
tional tables, and that have been converted into asserted version
tables. For each foreign key, whether it consists of a single col-
umn or multiple columns, the table shows the single-column
FK-TFK
tbl-nm
fkey-col-nm
tfk-col-nm
Policy
client_nbr
client_oid
policy_oid
wellpgmcat_oid
client_oid
wellpgm_oid
Policy_Amendment
policy_nbr
Wellness_Program
wellpgmcat_cd
Wellness_Program_Enrollment
client_nbr
Wellness_Program_Enrollment
wellpgm_nbr
Figure 8.6 The Foreign Key to Temporal Foreign Key Metadata Table.
