FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Time and Time Again: The Logical Data Model for Asserted Versioning Examples

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.


Tom Johnston is an independent consultant specializing in enterprise data architecture, and in relational, object-oriented and data warehouse modeling in various industries, including telecommunications, health care, banking, retailing and transportation. He can be reached at tjohnston@acm.org, and his Web site is www.MindfulData.org.

Randall Weis, founder and CEO of InBase, Inc, has more than 24 years of experience in IT and IT management, specializing in enterprise data architecture. Weis' technical expertise is in sophisticated, multitiered systems. He has designed logical and physical data models and implemented several high profile, very large database (VLDB) systems in the financial and insurance industries. These systems have had very stringent performance and real-time history requirements. His software development company, InBase, Inc., has developed software and Web sites used by some of the nations largest companies. Weis has been a presenter at various user groups, including Guide, Share, Midwest Database Users Group and Camp IT Expo. His technique for modeling history, retro activity and future dating has been reviewed and approved for the physical implementation of IBM's Insurance Application Architecture (IAA). He may be reached via email at randyw@inbaseinc.com.

For more information on related topics, visit the following channels:



Industry Vendors