Databases Reference
In-Depth Information
appears in it as a non-key column. Wellness program name is
left unchanged. Episode begin date, effective end date, assertion
end date and row create date are added as non-key columns. As
before, unique constraints and indexes are augmented and are
modified, as required.
Wellpgmcat_cd
code appears in the logical data model as a
foreign key to the Wellness Program table, and so the AVF must
convert it into a temporal foreign key. The foreign key declaration
is dropped from the DDL, the wellness program category code col-
umn is also dropped, and a
wellpgmcat_oid
column replaces it.
With these changes, the temporalization of this table is complete.
The Wellness Program Enrollment Table
. Unlike the other
tables in this sample database, Wellness Program Enrollment is
an associative table, commonly called an “xref table”. But its
conversion to a temporal table follows the pattern we have
already seen. The only difference is that this table has two for-
eign keys to convert to temporal foreign keys, not just one, and
two columns in its original primary key.
According to the Table Type metadata table, the Wellness Pro-
gram Enrollment table is an asserted version table. Prior to
temporalization, the primary key of this table consisted of the
two foreign keys
client_nbr
and
wellpgm_nbr
. But asserted ver-
sion tables must have single-column object identifiers, and so
instead of creating an object identifier for both client and well-
ness program, we create a single object identifier and name it
client_wellpgm_oid
. We then add effective begin date and asser-
tion begin date as the other two primary key columns.
As we see in
Figure 8.8
, the business key of this table is the
pair of temporal foreign keys. The other four non-key columns
are left unchanged. Episode begin date, effective end date, asser-
tion end date and row create date are added as non-key
columns. As before, unique constraints and indexes are aug-
mented and are modified, as required.
Client_nbr
and
wellpgm_nbr
appear in the logical data model
as foreign keys to the Client and Wellness Program tables,
respectively. The foreign key declarations are dropped from
the DDL, the client number and wellness program number
columns are also dropped, and the
client_oid
and
wellpgm_oid
columns, respectively, replace them. With these changes, the
temporalization of this table is complete.
In fact, the temporalization of the entire physical data model
is now complete. The result is the Asserted Versioning physical
data model shown in
Figure 8.8
. But an asserted version data-
base is not simply one that contains one or more temporal
tables. It is also a database that includes the code which enforces
