Calculating stock on hand - logic flow

Dear Community,

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.

The diagrams (png and html)

  1. 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:

    • PhysicalInventoryService#L181
    • QuantityValidator#L129
    • StockCardAggregate#L69
    • StockCardBaseService#L75
    • StockEventNotificationProcessor#L84
  2. 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?

Best Regards,
Wojciech Buława

Thanks @wbulawa, this is looking good. I have a feeling that there was a reason for stock card summaries to go through some of those additional steps in finding stock cards. Perhaps it was to know which stock on hand the system was supposed to know about, even when there was no stock card yet? Are you sure that’s covered? Is the test coverage here good enough such that it will allow such a big change without breaking anything?

@joshzamor we don’t skip any searching in the workflow. Methods’ calls like e.g. getApprovedProducts(facilityId, programId, orderableIds) or findByIds(ids) will stay intact. A call to the repository findByProgramIdAndFacilityId(programId, facilityId) will be hidden behind Service layer in order to prevent fetching uncalculated SoHs. The only thing that actually change is fetching previously calculated SoH instead of calculating for every function call.
Regarding the tests’ coverage:

  • StockCardSummariesService#L145
  • PhysicalInventoryService#L181
  • QuantityValidator#L129
  • StockCardAggregate#L69
  • StockCardBaseService#L75
  • StockEventNotificationProcessor#L84

are covered.

Great, thanks @wbulawa