Having taken a deep dive into our convenience functionalities that aim to remove most obstacles for working with temporal data, I anew “appreciated” the underlying complexities. This time around I decided to quantify these. Just how difficult is it to introduce time in a database? Is bitemporal comparatively a huge leap in complexity, as I have been touting for years without substantial proof? The answer is here.
Tracking versions is four times as difficult as not tracking anything, and adding corrections in addition makes it forty times as difficult.
To see how we got to these results, we will use the number of considerations you have to take into account as a measure. This is not exact science, but likely to be sufficiently good to produce a rule of thumb.
No temporality
When you have no intent of storing any history in your database, you will still have the following considerations. The (rough) number of things to consider are printed in parentheses before the description of the consideration.
- (2) Your key will either match no rows or one row in the database, no prep needed.
- (2) The value for the key will either be the same or different from the one stored.
Total: 2 × 2 = 4 considerations.
Not so bad, most people can understand some if-else logic for four cases.
Tracking versions (uni-temporal)
Stepping up and adding one timeline in order to track versions, the changes of values, many additional concerns arise.
- (3) Your key will match no rows or up to possibly many rows in the database, some prep may be needed.
- (2) The value for the key will either be the same or different from the one stored.
- (3) The time of change may be earlier, the same, or later than the one stored.
Total: 3 × 2 × 3 = 18 considerations.
In other words, tracking versions is more than four times as difficult as just ignoring them altogether. Ignorance is not bliss here though, mind my word.
Tracking versions and corrections (bi-temporal)
Taking the leap, to also keep track of corrections made over time, even more concerns arise.
- (3) Your key will match no rows or up to possibly many rows in the database, some prep may be needed.
- (3) The value for the key will either be the same, logically deleted, or different from the one stored.
- (3) The time of change may be earlier, the same, or later than the one stored.
- (3) The time of correction may be earlier, the same, or later than the one stored.
- (2) Your intended operation may be an insert or a logical delete.
Total: 3 × 3 × 3 × 3 × 2 = 162 considerations.
If you managed to pull through the 18 considerations from tracking versions, imagine nine times that effort to track corrections as well. Or, if you came from not tracking anything, prepare yourself for something requiring forty times the mental exercise.
Tracking versions, and who held an opinion about those and their certainty (multi-temporal)
I just had to compare this to transitional modeling as well, for obvious reasons.
- (3) Your key will match no rows or up to possibly many rows in the database, some prep may be needed.
- (5) The value for the key will either be the same, logically deleted, held with some degree of certainty, either to the value itself or its opposite, or different from the one stored.
- (3) The time of change may be earlier, the same, or later than the one stored.
- (3) The time of assertion may be earlier, the same, or later than the one stored.
- (3) Your intended operation may be an insert, a logical delete, or with consideration to existing data result in you contradicting yourself or not.
- (2) Assertions may be made by one or up to any number of asserters.
Total: 3 × 5 × 3 × 3 × 3 × 2 = 810 considerations.
That’s two hundred times more complex than most databases. It sort of makes me wonder how I ended up picking this as a topic for my research. But, here I am, and hopefully I can contribute in making everything more understandable in the end. In all fairness, many of the considerations actually have trivial outcomes, but those who do not can keep your though process going for weeks.
Thankfully, in all the scenarios above, much logic can actually be hidden from the end user, thanks to “default” rules being applied by triggers, hiding the complexity.