Modelling more than one application period per entity?
I am new to MariaDB specifically because of the bitemporal cabablilities which are very impressive.
There is a restriction in MariaDB whereby there can only be a single application period per table. I naively imagined that I would try to add a second application period. Maybe it is just too complex to implement or maybe it just doesn't make sense logically. Or maybe queries against such an arrangment would be too complex. I'm not sure which. Maybe, I just need to model it differently.
What is the best way to handle this?
In my use case, I want to be able to manage two application periods over system time for an entity. One is operational reality. The other is financial reality. Not everything that happens operationally will make it across to finance and sometime discretion is exercised by managers in applying operational transactions to finance.
Specifically, in the waste industry, waste bins are associated with a customer account. For various reasons, bin stock on the ground tends to be at variance from the bin inventory, e.g. a customer ends up with an extra bin that we don't know about but we lift it anyway (because the account is financially in good standing). This could persist for a period of time and then it gets fixed: we would want to set the
valid_from_time back to the time of the first lift for that bin at that location for that customer but we might not necessarily want to apply these lifts to the customer's invoices in the intervening period. (I know that there are more cut-and-dried ways of dealing this this kind of a mess but we have to be pragmatic about the reality)
Regardless of the MariaDB restriction, maybe the best way to model this is to have two entites (i)
account_bin_operational, and (ii)
account_bin_financial. I could manage writing the operational and financial application versions via an
account_bin procedure flagging any
account_bin_financial attribute that would require a review due to prior lifts. That could work.
How would the
system_from_time be managed in a procedure like this. There could be small differences in the
system_from_time depending on how long the procedure took to run. Would it be possible to specify a logical
system_from_time based on when the procedure started to run that would apply to both entities in the procedure? If I specify a
system_from_time it errors out with a message saying that the value for
system_from_time is ignored. I see value in maintaining the same
system_from_time for both parts of the
account_bin transaction expecially when it comes to querying and joining the two
Thoughts or suggestions welcome.