Note: A glossary of technical terms used in these articles can be found on the Web sites of the authors. In addition, a listing of previous articles and/or columns in this series can also be found on DMReview.com, MindfulData.com and InbaseInc.com.
In this column, we describe the logical data model we will use to illustrate the asserted versioning approach to bi-temporal data management. We begin with Figure 1, which uses our familiar policy table as an example. This figure was presented in our previous column, and is repeated here because it illustrates five of the key concepts of asserted versioning.
Next, we turn to a logical data model of the set of five tables we will use for all our examples of how to maintain and query an asserted version database. Figure 2 is a representation of that data model.
Four of these tables are asserted version (AV) tables. The wellness program category table is a non-temporal table and is included so we can illustrate how AV and non-temporal tables interact in the context of referential integrity (RI) constraints later.
The PK for the four AV tables is what Figure 1 calls a temporal primary key (TPK). It consists of:
- A unique identifier for the object being versioned, the object's OID,
- The starting point of the effective time period of the version and
- The starting point of the assertion time period of the version.
Foreign keys (FKs) from AV tables are all temporal foreign keys (TFK). They consist of a single column, which contains the value of the OID of the object on which they are referentially dependent. Because an OID is not a full PK in the referenced tables, the database management system (DBMS) cannot enforce temporal referential integrity (TRI). In this diagram, TFK relationships are drawn in red.
In the policy and wellness program enrollment tables, the two begin dates are marked "PF." This is an artifact of the data modeling tool and of some tricks we used to get the tool to show PFKs rather than PKs when the relationship is a temporal one. None of those four columns are FK. They should be marked simply "PK."
The relationship from wellness program category to wellness program is a "normal" relationship, as are all relationships from non-temporal tables even when their target is, as it is in this case, an AV table. The DBMS can enforce referential integrity on this relationship. In this diagram, FK relationships are drawn in black.
In any table in which a surrogate column is used as all or part of the PK, it is important to include the full business key in the table as well. If the business key is a unique identifier, as it should be, we should also define a unique index on that key. In this data model, however, we have not included separate columns for business keys. Rather, we use OID values that look like single-column business keys for each of our versioned objects. This makes it easier to refer to specific objects throughout these discussions by using their OIDs. We do not show separate business key columns because we will be using sample tables in these discussions, in which columns are laid out across the page, and so the width of the page placed constraints on how many columns we could use in our examples.
The Client Table
In our example, a client is a person who is covered by a policy. This coverage is a legal obligation that the insurance provider accepts, in return for payments from the client or his/her employer. Coverage often extends to members of a client's household, but this fragment of an insurance provider's data model does not include households or employers.
Figure 3 and all other AV tables have a common syntax for the PK. If our company also guaranteed that OIDs are unique across all tables, then every AV PK will be unique across all tables. With a common syntax (i.e., a common user-defined data type) and a set of unique values, heterogeneous collections become much easier to assemble and manage. But that is an aside, and we will not pursue the point.
Note that neither an effective nor an assertion end date is included as part of an AV PK. This means that as we manage AV tables, we must have a way to guarantee that no versions of the same object will be created for which there are identical or overlapping effective and assertion time periods. It is possible, as we will demonstrate, for two or more versions of the same object to have identical or overlapping effective time periods; but it is not possible for those versions to also exist in identical or overlapping assertion time periods.
It will be important, as we proceed through our series of examples, to understand both how and why asserted versioning enforces these constraints. Fundamentally, the constraints mean that we will not make multiple concurrent claims about what an object was, is or will be like.
The Policy Table
A policy is the legal contract that lays out the coverage that the insurance company agreed to extend to the client. Clients and policies are both extensive subject areas within a complete insurance provider data model. But from those subject areas, we only need these two tables to illustrate asserted versioning.
This table is a "temporal child table" to the client table. Its FK is a temporal foreign key, as indicated by the suffix "tfk." Because it contains a TFK, it does not point back to any specific row in the client table, but only to that set of rows all of whose client OIDs match this value.
The referent of a TFK is neither an object, nor a version. It is an episode, an effective-time contiguous set of versions of the same object, all of which exist within an assertion time period that wholly contains the assertion time period of the referencing row. In TFK relationships, from either non-temporal or AV tables, individual rows do the referring, and what they refer to are episodes.
The Wellness Program Table
Wellness programs are becoming increasingly important to insurance providers. Together with employers, these providers recognize that everyone benefits from good health - the client, employer, provider and society at large. For this reason, employers and providers are beginning to manage health care proactively, not just reactively, and wellness programs are one of the principal forms that proactive management of health care takes.
Figure 5 contains a FK, as indicated by the suffix "fk." It is the only table that does. We will use this table in our discussions of "mixed relationships" - those between a non-temporal and an AV table.
The Wellness Program Category Table
There are many different kinds of wellness programs. One basic distinction is between programs that monitor the status of an ongoing chronic medical condition and programs that attempt to improve a client's health, by providing information, raising awareness of health issues, or by underwriting enrollment in weight loss, exercise or other wellness improvement programs.
This description suggests that wellness program categories might have a hierarchical structure. That structure would be represented by a one-to-many recursive relationship on this table. But again, in this data model fragment, we have eliminated everything except what is necessary to illustrate bi-temporal data management and the asserted versioning approach to it.
Figure 6 is the only non-temporal table in the model. It has a one-to-many relationship to the wellness program table. Because it is a non-temporal table, this is a "normal" non-temporal relationship. It uses a FK, not a TFK. The DBMS can enforce RI on this relationship.
Some of the more complex scenarios we will examine toward the end of this series involve the interaction between non-temporal and AV tables and the enforcement of integrity constraints across that mix of tables.
The Wellness Program Enrollment Table
Clients enroll in one or more wellness programs. Each program enrolls one or more clients. Therefore, the enrollment table is an associative table, implementing a many-to-many relationship between clients and wellness programs.
Figure 7 is the only AV table with rows that do not represent versions of objects. Rows in this table represent versioned relationships among versioned objects. As we shall see, however, AV associative tables should cause no trepidation among those hoping to use AV. Just as the policy table is an AV table with one TFK dependency on another AV table, the wellness program enrollment table is an AV table with a TFK dependency on two AV tables.
In our next column, we will begin our example-driven presentation of AV. We will call these examples "data scenarios," or "scenarios" for short. Unless otherwise indicated, each scenario will be independent of all other scenarios.
We will begin with a series of scenarios involving only our AV policy table. This means that the discussion of temporal referential integrity - a complex issue - will not occur until we fully understand how AV works in the single-table scenarios.
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access