we are working on the Stock Management performance improvements. We have started a discussion about improving the database scheme by storing calculated stock on hand as a separate table. Currently stock on hand needs to be calculated from all stock line item associated with the stock card. These calculations happen every time when we want to fetch stock on hand for e.g. summary report.
The aim of this topic is to discuss possible solutions which we could apply to the logic flow of fetching SoH.
Handling SoH request
At this point, we are simply fetching StockCards and calculating SoH for every one of them by summing all its stock line items. We want to replace it with a fact table described in the topic linked above. Here is our suggestion of how we could handle this fact table. The elements marked as blue are the one we want to replace with the red elements.
The workflow was described for StockCardSummariesService#L137 because this is the main usage of SoH, but in order to avoid discrepancies we should apply this change also for:
Updating SoH fact table.
Every change of StockCard or StockCardLineItem should update the fact table. In order to do so, we could add updating logic which will be triggered for every StockEvent.
StockEvent is triggered for every update of StockCard and it contains StockEventLineItems. Also, StockCardLineItem is saved only using cascade with StockCard. This means that every change of StockCardLineItem, which are used to calculate the SoH, will be triggering our update flow.
If a StockCard will be updated we will trigger the event. If there is no record for this StockCard on that day we will create a new record of the fact table. If such a record already exists, it will be updated.
We want to store one record of the new fact table for every StockCard and for every day.
Please let us know what do you think about this design?