2

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.

user1763470
  • 197
  • 1
  • 1
  • 11

1 Answers1

4

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.

PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
  • 1
    Lets do it the right way as you said with just one QOH in the Inventory Table is the Current QOH. So let's say, on 1st April 2015, I want to get the QOH of all Inventory Items on 1st April 2014. For each of the inventory items I need to do QOH today - sum all transactions between today and 12 months? Isn't that going to be terribly expensive? – user1763470 Apr 18 '15 at 11:55
  • 2
    Not at all, it is the normal (correct) method, millions of sites do it, this structure is heavily used in accounting and banking systems, where they have the additional complexity of Audit requirements. (1) if your tables are reasonable (no negative performance), and the rows per item are a few thousand, the speed would be straight-forward: dependent on the no of rows retrieved. (2) If you had Relational Keys, it would be blindingly fast because each Item would address a very small branch of the B-tree. – PerformanceDBA Apr 18 '15 at 23:57
  • (3) if, and only if, the company has the notion of performing a physical inventory audit, and recording a permanent QoH per item (say annually), which is an "audit quality figure" and not changed after that date, then there is an enhancement (addition, not change) to handle that. The user needs to thoughtfully decide on a cut-off date (quarterly, annually). – PerformanceDBA Apr 19 '15 at 00:04
  • (4) if there are millions of adjustments per item per year, the same enhancement [3] applies. Read and understand [this Answer](http://stackoverflow.com/a/29713230/484814 "Accounting Method with Audit Requirements"). Note, even there, no duplicates are stored. Note, they do it for Audit reasons, **not** because the rows are millions. Ask, and I will walk you through it. – PerformanceDBA Apr 19 '15 at 00:14
  • (1) Contd. Just write the code and try it. (2) Contd. You might take this change request as an opportunity to ramp up one or a few files to Relational tables, and thus obtain Relational benefits, such as speed. – PerformanceDBA Apr 19 '15 at 00:29
  • 2
    You are in a different league my friend. Thanks a lot for the lucid explanation, and excellent answers. – user1763470 Apr 19 '15 at 02:27
  • 2
    Thank you. It is my pleasure. (4) In case you are interested, I updated [that Answer](http://stackoverflow.com/a/29713230/484814), to deal with the comments over there. – PerformanceDBA Apr 20 '15 at 08:39