We’re going to provide some improvements for the Stock Management performance. One of the issues is that the Stock Management service currently doesn’t store any information about current stock on hand. Instead, the current stock on hand needs to be calculated from all of the transactions that happened on a stock card. In order to tackle that, we would like to introduce the fact table, that would store the stock on hand information as of any given date. That should make it faster to determine both the current stock and stock as of any given date. The design of the database can be found here: https://www.db-fiddle.com/f/qRbv6dCFnBbLXmP63vYjRv/1.
The idea is to store one entry for each day when the event has occurred, but:
- if there are more events in one day, the stockonhand value would get updated for an existing record in the database;
- if there are no entries for a given day, we can consider the latest one up-to-date;
As you can see in the fiddle, we’re planning to add an index only on the stockcardid column, which will speed up the queries a lot. According to the test we’ve run, there no need to add additional indexes as it won’t improve the performance in this case.
Please let us know what do you think about this design?