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 account_bin entities.

Thoughts or suggestions welcome.


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.