HomeCalendarFAQSearchRegisterMemberlistUsergroupsLog in

Share | 

 Time and Time Again: Managing Time in Relational Databases, Part 8: Version Pattern 5

Go down 

Number of posts : 1850
Age : 55
Registration date : 2008-03-08

PostSubject: Time and Time Again: Managing Time in Relational Databases, Part 8: Version Pattern 5   Sun 13 Apr - 20:07

Version Pattern 5 is the first pattern to manage objects with multiple episodes, a concept introduced in Part 4. To avoid taking on too much at once, however, Pattern 5 will not include bi-temporality. That will come with Pattern 6 which will be, consequently, a pattern supporting both multiple episode objects and bi-temporality.

Up to this point, the version patterns we have examined have incorporated the assumption that once an object is logically deleted, it will never recur. In Part 4, we called this the "single episode" assumption. Technically, this assumption means that a) there can be at most one logical delete version for an object; and b) that no other version for the same object can have an effectivity begin date later than the logical delete date.

Single Episode Objects
In the case of single episode versioning, it is reasonable to ask, "What kind of objects never recur?" For if there aren't any such objects, then the patterns considered thus far have no real world applicability.

The answer to this question depends on what we mean by "recur". So, at this point, we could embark on a general discussion of the concept of recurrence. That discussion would include an examination of the ontology of types vs. instances, and of enduring (persisting through time) objects vs. events (processes in and through which objects change over time).

The types vs. instances distinction is, in relational databases, the distinction between a table and its rows, or an entity and its attributes. In object-oriented theory, it is the distinction between object classes and their objects, and there the ontological question looms large, as the question of whether or not classes are "first-class objects". These questions are the current manifestation of what the medieval Scholastics (especially Ockham and Duns Scotus) knew as the realism / nominalism debate.

For realists, types really existed, just as truly as their instances did. Realists would have treated classes as themselves objects. For nominalists, instances were what really existed; a type was just a name given to the common features we observed across many different objects. Nominalists would not have treated classes as objects, able to exert influence on other objects, for example.

What about the second philosophical distinction, that between objects and events? Some proponents and followers of the Kimball methodology believe that everything of importance can be represented in dimensional data structures. From the perspective of the history of Philosophy, we would see that this position reflects a commitment to the ontological priority of events over objects. We would see that the approach to change which we have taken in this series of articles, which involves preserving both the before and after states of objects undergoing change, reflects a commitment to the ontological priority, or at least equivalence, of objects to events.

This, we believe, would be an important discussion. The history of Philosophy has much to tell us about problems which IT professionals and computer scientists alike are still struggling with. A discussion like this would illustrate how such business IT-specific issues as we have just mentioned are but the current manifestation of two ways of thinking about the world, whose roots go back at least to the pre-Socratic philosophers. Although object-orientation (in the sense of the ontological priority of "things" and "stuff") has dominated Western metaphysics for two-and-a-half millennia, there has been a strain of process-orientation (in the sense of the ontological priority of events and occasions). This latter strain originates, perhaps, in the reflections of Heraclitus, and his famous dictum "panta rei" - "all things change". It reached perhaps its fullest expression some eighty years ago, in the work Process and Reality, by Alfred North Whitehead.

Don't Know and Don't Care
The problem with the single-episode assumption is that things exist over time, and so always contain within themselves the possibility of recurrence - of appearing to us, going away, and then returning. So, by their very natures, objects are fraught with multi-episode possibilities.

The reason that version patterns incorporating the single-episode assumption may still have some real-world use, is that there are objects we may be interested in whose recurrence is not of interest to us. In other words, there may be objects which, when they do recur, we treat as new objects. Either we don't notice that they are the same objects we encountered before, or we do notice but don't care.

A simple example is a policy holder. Suppose that Joe Smith is a policy holder with our company from 2002 to 2004, at the end of which year he lets his policy lapse. A year later, Joe takes out another policy. If our company makes no effort to identify Joe as the same person who held a policy from 2002 to 2004, then when he returns, in 2006, Joe is effectively a new policy holder for us - not just a holder of a new policy, but a new holder of a policy.

Twenty years ago, to take another example, it was common for banks to manage "accounts". Some effort was made to identify cases in which one person held several accounts concurrently, but almost no effort was made to identify cases in which one person held several accounts successively.

So the answer to the question "What kind of objects are single-episode objects?" is clear. Single-episode objects are those whose recurrence we either don't know about, or don't care about.

But with Version Pattern 5, we begin to deal with multi-episode objects. These, then, are objects whose recurrence we do make an effort to identify and track.

Across industries that deal with people as customers, the same change has taken or is currently taking place. Insurance companies who managed a relationship with policy holders now want to manage a relationship with persons who are, or who may become, policy holders. Banks which used to manage the account relationship now want to manage the customer relationship. In some industries, companies are even striving to recognize the appearance and re-appearance of households.

Multi-Episode Objects
If an object could recur, what would that look like, as far as data is concerned? In the case of Mary's policy, it would look like a logical delete of policy P138 followed, after one or more clock ticks, by another version for P138 which is not a logical delete.

Normally, persistent objects re-appear after some period of time, i.e. after some possibly large number of clock ticks. So there can be gaps between episodes of the same object, although if there is only one clock tick between a logical delete and the next version of an object, those episodes would not have a gap between them.

If the number of logical deletes of an object is the same as the number of episodes, then, for as long as that is true, the object does not currently exist on our database. That is because every episode, including the most recent one, has been terminated by a logical delete.

The only other case that can arise is that the number of episodes is one greater than the number of logical deletes. That is because, in a series of versions for the same object, what splits them into episodes are the logical delete versions. In this case, the object does currently exist on our database because there is no logical delete version for the most recent (or only) episode.

Semantic Constraints for Pattern 5
Pattern 5 is identical to Pattern 3, but with the single-episode constraint dropped. So instead of presenting our usual insert/update/delete scenarios, we will develop Pattern 5 by beginning with the semantic constraints (called "assumptions" when that is the more convenient terminology) for Pattern 3, and seeing what changes need to be made to them to accommodate the multi-episode functionality.

We start with the first constraint.

Semantic Constraint {SC 3-1}.
{SC 3-1} states that "if a version for an object is followed by another version for that same object, the end date of the former version must be one tick of the clock prior to the begin date for the latter version." This constraint says that versions must have no gaps in time between them. But multiple episodes of an object nearly always will have gaps in time between them. For example, a policy expires, because of a late payment. But a week later that policy is re-instated because the company granted a grace period in this case, in order to retain a good customer.

So to accommodate multiple episodes, {SC 3-1} must be modified to apply only to versions which do not follow a terminal (logical delete) version. This gives us the corresponding constraint for Pattern 5.

{SC 5-1}. If a non-terminal version for an object is followed by another version for that same object, the end date of the former version must be one tick of the clock prior to the begin date for the latter version.

Semantic Constraints {SC 3-2 and 3-3}.
{SC 3-2} states that "if a version for an object is not followed by another version for that same object, and is not itself a logical delete version, the end date will be assumed to be unspecified". And {SC 3-3} states that "if a version for an object is a delete version, the object will be considered deleted as of that delete date". Both of these semantic constraints apply to Pattern 5 as well.

Semantic Constraint {SC 3-4}.
{SC 3-4} states that "it is semantically illegitimate for any version of an object to follow (to be chronologically later than, based on its version date) a logical delete version of that object."

This is the single-episode constraint. So it is precisely the constraint that is dropped for Pattern 5.

Semantic Constraints {SC 3-5 and 3-6}.
The next two semantic constraints for Pattern 3 explicitly apply to a logical insert, i.e., to the first version of an object.

{SC 3-5} states the constraint on the effectivity begin date. It says, of the first version of an object, that "ver-beg-dt cannot be in the past or in the future, because it is also the system insert date. To set such a date to anything but [now] would be a lie. We can't do anything in the past, or in the future. [Now] is all we have."

{SC3-6} states the constraint on the logical delete date. It says, of the first version of an object, that "obj-end-dt must be null. We can't end something before it begins. We can't even end something when it begins. We can only end something after it begins."

What's different about Pattern 5 is that there can be multiple first versions of an object, one for each episode. So the question is whether these two constraints apply to initial versions of any episode, or just to the initial version of the first episode.

The answer is that these two constraints apply to initial versions of any episode of an object. No matter what episode we are dealing with, it is obviously true that (without bi-temporality) we can't do anything in the past, or in the future. And it is equally obvious that (without bi-temporality) we can't end something before it begins, or even when it begins.

Taking Stock of Where We Are
We will complete Version Pattern 5 in Part 9. But here, at a point about a third of the way through this series of articles, we want to remind you why we are considering several versioned history patterns, and also why the discussion has been so painstakingly detailed.

There are two reasons for considering several version patterns. The first is that each pattern can be used to satisfy any set of business requirements which conform to the semantic constraints stated for the pattern. Earlier patterns are less complex than later ones, and some developers may prefer to use simpler versioned history patterns when their system requirements permit it.

The second reason for considering several version patterns is to gradually build up to what is the most powerful versioned history pattern that we know of. As we said in Part 1, the versioned history pattern we have evolved is the most complex data pattern for its size that we know of. It is best, pedagogically, to approach it one step at a time.

Another pedagogical feature of these articles is their discursive style. Often, data patterns or data models are presented as completed objects. Little effort is spent on explaining the thought processes that led up to them.

We believe, however, that the thought processes are, if anything, even more important than the completed pattern. If we can teach senior data modelers how to think about time, by explaining how we thought through the issues and why we chose the solutions we did, then the final versioning pattern we present should seem almost inevitable. If, at the end of this series of articles, that pattern does seem inevitable or at least completely plausible to you, then we will have given you not just a tool for managing time in databases, but also the skills to build other such tools.
Back to top Go down
View user profile
Time and Time Again: Managing Time in Relational Databases, Part 8: Version Pattern 5
Back to top 
Page 1 of 1
 Similar topics
» After a long long time, we can have a clam sleep
» Time to invest in stock market: SC Securities
» Only 6 months more; time to discuss new SLFRS and its impacts to stocks
» Rupee hits all time low
» A Good Time to Trade in Gently Used Car -Is it Same SriLanka

Permissions in this forum:You cannot reply to topics in this forum
Job98456 :: Databases-
Jump to: