Search code examples
databaseinventory

Regarding the opening quantity of an item in the store

If I entered the opening quantity and then made some item movements such as buying and selling, etc., and I have a record of the item movement and the current quantity of the item was affected by these movements, if I wanted to adjust the opening quantity or correct it after all these movements, what is the best scenario? Should I review the transaction history and add the difference between the first opening quantity and the corrected opening quantity and update the current quantity based on this difference or ignore the adjustment in the opening quantity or what?

Solution

  • All you need are transactions and entities. The items move between entities - and one of the entities will be you (or your store/warehouse). So in order to enter an opening quantity you will have a transaction which moves the given item from the SUPPLIER entity (either you or your vendor) to the WAREHOUSE entity.

    Then you will buy or sell more instances of the same item - so you will have transactions from/to the WAREHOUSE and either your supplier(s) or your customer(s).

    Then, at a later time, if you want to make a physical inventorisation of the WAREHOUSE and you find out that the actual quantity is different from the quantity computed at the end of all transactions - you will need a new (corrective) transaction which will have the WAREHOUSE on one of the sides and INVENTORY_CORRECTION on the other side. Whether the WAREHOUSE will be on the left (source) side or on the right (recipient) side will depend on whether the actual quantity is lower than what the software thinks it is, or the actual quantity is above.

    If you buy/sell more than one kind of items - you will need to specify the item kind in each transaction. So the transactions will have the following format:

    • unique ID
    • timestamp
    • source entity
    • destination entity
    • item kind
    • item quantity
    • item price for the given quantity at the current point in time - this is total price, not a unit price (unit price will be item_price / item_quantity)
    • measurement unit for the quantity - e.g. pieces, boxes, kilograms, meters, liters, etc.

    You may keep cached (precalculated) information about the quantity of each item at specific points in time - e.g. between transactions, at the end of each day, at the beginning of each month, at the current time. It's all up to you - but the list of transactions is the only source of truth. So if you need to insert new transactions at a later time with a timestamp in the past - you will need to precalculate the corresponding cached values.