Hello,
I am very new to Data vault, so I would like to have a sanity check, if it is a way to go…
So there are Order table and Calculated_price table, that stores all calculated prices generated by price microservice.
Then there is a calculated_price_history table that stores a relation through reference_price_id between order and calculated_price, in case such occurs, meaning not each calculated price has connection to an order. Moreover, there is a possibility to change a price manually that will also be stored in calculated_price_history w/o any reference (reference_price_id is null) to calculated_price table, obviously; the price can be changed many times, therefore, one order can have many null entries that can be distinguished by created_at attribute. There is also an action_type attribute that shows whether price is USER_CREATED (no reference_price_id) or AUTO_CREATED (with reference_price_id).
The ER-diagram looks this way:
So my idea was following:
Hub_order:
order_hk
order_bk
source
load_time
Hub_calculated_price:
calculated_price_hk
calculated_price_bk
source
load_time
And calculated_price_history table is where I am struggling…
The first thought was a non-historized link, however, what is bk that should be part of link_hk in case of USER_CREATED entries, -1 || created_at? It seems wrong that once link_hk is combination of order_bk and calculated_price_bk and other time order_bk and (-1 || created_at).
Another thought was to have a calculated_price_history as satellite of order_hub, however, than I don’t see a way to have a connection to calculated_price…
Perhaps, I am missing some knowledge, so any tips/ideas/suggestions are very welcomed.
Thanks