Using orders, we now have a way to track past changes to our data. For financial analytics, this is particularly important to get accurate results for queries that intend to look at the impact of object attributes on our model. For example, if we want to compare the stock market performance of EU countries in 2010, we need to know which countries were actually part of the European Union at that point in time.
From a data perspective, that means we need to store information on the validity periods of an attributes value, so we can later retrieve the objects value based on a date.
During the last article we already described a way in which we are implicitly storing that information in the database, since our orders contain all changes to their underlying objects as well as a value date that reflects when that piece of information became valid.
However, retrieving the correct value at a certain point in time by looking at past orders is cumbersome and bears a maintenance risk: What if we want to change the way that orders store object data modifications in the system? We would have to rebuild the entire functionality connected to retrieving an objects history. That is only one issue. Things get a lot more difficult when validity periods become more complex
Introducing Validity Date Ranges
All objects in our system are stored in the OBJ table. Whenever we need additional attributes for a certain business type, those will be stored in a sub-class carrying the name of that business type, eg. OBJ_USER. Either way, the OBJ_ID is the primary key of these tables and therefore unique as per the table definition.
We must therefore introduce new tables for the history of the object. Keeping things simple, the corresponding entities and tables will carry the table name with the suffix “_HIST”. By omitting attributes in these sub-classes, we will also be able to specifically NOT historize certain object attributes.
Let us look at an example:
Our OBJ table contains an object of type “OBJ_USER” called “James Miller”. We can see that the last modification of that object was on November 20th, 2020. What we cannot see, is what the name of that user was before the modification, how many modifications there have been in the past or when the modification took effect. For example, the order to modify the object may have been executed on November 20th, but the Value date of the order could have been on October 15th. That would typically be the case, when the users name has changed earlier “in the real world”.
Let us look at how this object looks like in the OBJ_HIST table:
Here we can see all past modifications of the user in one table. We can see which orders caused the modifications, what the value date for those modifications was, which values were changed and what value the object had before the modification.
Now whenever we want to return the current data of an object, we can simply query from the OBJ table. If we need historic data instead, we can query from the OBJ_HIST table using a specific date to get the correct value.
The depicted scenario is relatively simple. When a new order change a value, the VALID_TO date of the previous period gets adjusted and the new period is entered based on the orders value date. Things become trickier if the VALUE DATE of an order is before the VALUE DATE of a previous order. In that case, adjusting the VALID_TO date to the value date of the new order would generate a period with a where the VALID_FROM date is greater than the VALID_TO date. Such an entry would therefore be not valid. To honour that fact, our OBJ_HIST table will receive an attribute called “VALID” which will result in false if that occurs.
Historic Validity Ranges
Another scenario we want to examine is that of orders with VALID_TO dates in the past or VALID_TO dates which are smaller than that of a previous order. One way to account for this would be to add a field “valid to” for master data orders and then treat those cases in the code. However, this will create a lot of implementation effort for an edge case that may never occur. If it does ever happen, a workaround can be used, in where an order gets created based on the lowest affect VALID_FROM date and all other order with a newer value date get re-executed or recreated to adjust the VALID_TO dates in the past.
Eventually, the new order process for our application will look like this: