Search code examples
database-designinventoryinventory-management

How to handle change in Inventory Quantity on a backdate

My database has 2 tables 1. INVENTORY with attributes of ID, Name, QOH (ie Quantity on Hand)

  1. INVENTORY_ADJUSTMENT with attributes of Date, AdjQty, QOH, Notes

Each time a inventory item is sold or purchased, I make an entry in the INVENTORY_ADJUSTMENT. At this point I also calculate stock on hand and update QOH in both INVENTORY and INVENTORY_ADJUSTMENT.

So far this had worked fine. But now some of the clients have a requirement where they want to go back a month and update the quantity on hand. So for ex: on 10th April, a user can go back and change QOH of 1st of March. If this were to be allowed, then every entry I had made in the INVENTORY_ADJUSTMENT table between 1st March and 10th April would need to be updated as well, since each row of INVENTORY_ADJUSTMENT has the QOH field which will now be out of sync.

How do I support the feature of allowing back dated inventory quantities to be entered? Any help will be much appreciated.

Solution

  • Problem

    First you need to understand the problem, for what it is, and that you have created it. After that we will have a different context, and can discuss possible solutions.

    1. The Inventory is the hard-and-fast, permanent fact table. The idea is, at any point in time, if the building goes up in smoke, the table definitive identifies the position of the inventory of the company.

    2. The InventoryAdjustment table is just a transaction against the inventory of the company, against the Inventory table. It could have been named Transaction.

    3. The InventoryAdjustment.QoH column is a duplicated column. The QoH already exists in Inventory. When you create a duplicated column, you have:

      • broken Normalisation rules (and thus you have a well-known, classic error that we are commanded to avoid, and which has consequences). Your tables are no longer Normalised, your database isn't a database.

      • introduced an Update Anomaly. This means you have to update the datum (the value that is the single version of the truth) in more than one place, otherwise the two (or more) items get "out of synch". The idea is, do not create the duplicate in the first place.

      • If you had implemented it in order to provide a running total (InventoryAjustment.QoH, in consecutive lines), you have been fooling yourself. The running total (Inventory.QoH plus all adjustments up to the current line) is easily supplied in SQL code. Ask for it, if you need it.

    Now you are quite used to this erroneous state of affairs, you do not view it as an error, it has "worked just fine" up to now. So you may have difficulty accepting that it is an error. I will leave that for you to overcome.

    • The pivotal term is "up to now". Classically, it is only when a bug is exposed, or when an user wants to change something, that such errors become a problem. So first, they need to be exposed and understood for what they are.

    • The event just happened, the above has been triggered.

    Solution

    • Drop the InventoryAdjustment.QoH column.

    • Retain the re-calculation and update of the Inventory.QoH column whenever an InventoryAdjustment row is inserted.

    • Now the Inventory.QoH column is true, and it is true all the time. It reflects the exact QoH in the warehouse right now.

    • No "synching" needed, because there are not two versions of the truth, or two copies of one truth, on file.

    End of story.

    New User Requirement

    Now some of the clients have a requirement where they want to go back a month and update the quantity on hand. How do I support the feature of allowing back dated inventory quantities to be entered?

    • The now-corrected database handles it just fine.

      • Note well, since the database has been corrected, there is just one QoH. If it had not been corrected, we would have to stop and ask:

        Which QoH do they wish to update, the running total, or the real one, or both ?
        What purpose does it serve ?

    • The app needs a little tweaking. I assume the current function sets InventoryAdjustment.Date to the current date before insertion.

      • Add a function to allow the privileged user (eg. who has been granted "supervisor" permission, in order to permit this) to set InventoryAdjustment.Date to a valid date in the past, constraining it to (eg) 30 days or whatever.

    • When the transaction executes, it will adjust Inventory.QoH in the usual manner.

    End of story.

    (If it isn't, that would indicate that there are pertinent details that have not been communicated. Please do so.)

    Band-Aid Fix

    If you do not appreciate (a) the nature of the error, and (b) the veracity of the correction, ie. you are happy to retain it, then ...

    If this were to be allowed, then every entry I had made in the INVENTORY_ADJUSTMENT table between 1st March and 10th April would need to be updated as well, since each row of INVENTORY_ADJUSTMENT has the QOH field which will now be out of sync.

    Yes.

    That is the consequence of an Update Anomaly. All the duplicated InventoryAdjustment.QoH values for the adjusted item are now deemed to be false anyway. There was not, and is not, any value in recording them. But you did record them. And now they need to be updated.

    Code it.