Note: A glossary of technical terms used in these columns can be found on the Web sites of the authors. In addition, a listing of earlier articles and/or columns in this series can be found on DMReview.com, MindfulData.com and InbaseInc.com.
Figure 1 presents the side-by-side diagram we will use to illustrate the points we will make in this installment. Notice that there are two identical timeline graphics and two nonidentical schemas.
On the left is the schema for a non-temporal table. The underlined column is the primary key. The italicized column is a foreign key to a table that will appear in future columns. Type and Copay are the two business data columns in the table, which is a table of health care insurance policies. Create and update date are, respectively, the date the row was inserted into the table and the date the row was last updated.
On the right is the schema for a uni-temporal table. We will refer to it as a version table, because that is standard terminology among IT professionals. When the business requires us to track changes to a conventional, non-temporal table, a common approach is to version the table by adding a date to the primary key, as we have done here.
There are many variations on version tables, but the variations make no difference to this discussion. In this particular case, version date is also the date the row was inserted, although in other variations, that is not always the case. There is no last update date, because versions are not physically updated. Instead, each logical update is carried out by copying the current version, applying the update to the copy and inserting the result as the new most current version. Finally, this particular variation on version tables also contains a delete flag, although this too is not present in all variations. When a delete is requested, the most current row is copied, its delete flag is changed from “N” to “Y,” and the new row inserted.
Figure 2 shows the results of applying the same insert transaction to each table. The result is a single row, as shown. Above each timeline is a bracket. The non-temporal bracket shows the period of time the row has been in the table. The temporal bracket, as we will see, shows the period of time that each row was the current version of the policy.
After the insert, the brackets show a one-month period of time, despite the fact that, on the date of the insert that one month has not yet elapsed. This is because we assume, throughout these examples, that the tables are updated once a month. So once a transaction has been applied, the results will remain on those tables for at least a month.
Figure 3 shows the results of applying the same update transaction to each table. This transaction changes the copay amount on the policy from $15 to $20. The update was applied in May, so the brackets extend through June.
Be the first to comment on this post using the section below.