Achilles’ Tortoise

In the spirit of the post about the light cone, I thought I would post about a problem that I have been thinking about lately, but it is really a concern for most applications, and that is the storage and processing of entities that have one or more time dimensions. I will call these ‘temporal entities’.

To provide a domain to have examples for discussion, consider a payroll system. In particular, suppose the system’s Employee objects have attached to them a ’salary’ property. This example will help illustrate the multi-dimensional time aspects. Suppose an Employee receives a paycheck in June based on the salary, which was $100 at the time. When the Employee gets a raise, the system must capture the new salary amount so the new check for $125 is sent in July.

So far, we have one temporal dimension- the ‘perfect world’ scenario where the system and the users do everything perfectly. How the system accomplishes the management of this information is up to the reader, but I suppose it could have multiple ’snapshots’ of an Employee with an associated effective date. To find the salary for a point in time, you might retrieve the record that was “in effect” at the time in question. This seems to be a reasonable approach at first blush.
Now consider the situation where we are “in the wild”- at a particular customer’s installation of our payroll system:

1. Jim Bob gets his check in June for $100. Everything’s going great and Jim Bob’s supervisor, Betty Sue, tells him he will get a raise to $125 in July.

2. Betty Sue forgets to update Jim Bob’s Employee object in the payroll system.

3. The system cuts a check to Jim Bob for $100 for July. Jim Bob complains to Betty Sue.

4. Betty Sue comes in on July 4th and updates Jim Bob’s salary to $125 for July. She also cuts a new check for $25 as a “correction” to the payroll.

Now, this is where it gets interesting. If we were to query the system and supply the effective date in June, we would see $100 for salary. A query for July would show $125. We would naturally assume that the check cut to Jim Bob in July was for $125, but we see the the transaction log that it was in fact not $125, but $100 on July 1st, and changed to $125 on July 4th along with the correction.

Whew. Ugly, nasty business, payroll systems are. Imagine systems (not payroll) where the above situation is the norm rather than the exception. The point I am driving at here is: what is the most natural, elegant, intuitive way to think about or model objects that have this sort of behavior “built-in”? Is there a clean approach, or is it just necessarily ugly? Managing temporal relationships has been a topic of heavy research in database systems for some time. But are there patterns that help us in the object world? And how do object-relational mappings work when temporal objects are persisted in relational databases? Inquiring minds want to know.