Databases Reference
In-Depth Information
Temporalizing the Physical Data Model
ERwin generates a physical data model from the logical data
model shown in
Figure 8.2
. At that point, the physical data model
is a conventional model. The next step is to apply the metadata
shown in
Figures 8.3 through 8.7
. The result of that step is the
temporalized physical data model shown in
Figure 8.8
.
An enterprise which chooses to build its own framework, based
on the ideas presented in this topic, may choose to use some other
data modeling tool, of course. But Asserted Versioning LLC's
commercially available AVF stores this metadata in ERwin User-
Defined Properties, not in metadata tables like those shown in this
chapter; and it automatically applies that metadata as the physical
model is generated.
Absent that automated process in which temporal metadata
is applied, the temporalization process works like this.
The Client Table
. According to the Table Type metadata
table, the Client table is an asserted version table. Prior to
temporalization, the primary key of this table was
client_nbr
. From
this column we derive the name
client_oid
for the first column in
the bi-temporal primary key by dropping the suffix and replacing
it with
oid
. We then add effective begin date and assertion begin
date as the other two primary key columns. Client number itself
is now treated as the business key of the table, and so it appears
in the temporalized table as a non-key column. Client name is
left unchanged. Episode begin date, effective end date, assertion
end date and row create date are added as non-key columns.
Where necessary, unique constraints and indexes defined in the
non-temporal model are augmented with temporal date columns,
and converted to non-unique indexes. And at this point, the
temporalization of the Client table is complete.
The Policy Table
. According to the Table Type metadata
table, the Policy table is an asserted version table. Prior to
temporalization, the primary key of this table was
policy_nbr
.
From this column we derive the name
policy_oid
for the first col-
umn in the bi-temporal primary key. We then add effective begin
date and assertion begin date as the other two primary key
columns. Policy number itself is designated as the business key
of this table, and appears in it as a non-key column. Policy type
and copay amount are left unchanged. Episode begin date, effec-
tive end date, assertion end date and row create date are added
as non-key columns. As before, unique constraints and indexes
are augmented and modified, as required.
Client_nbr
appears in the logical data model as a foreign
key to the Client table, and so the AVF must convert it into a
