Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    2

    Unanswered: Inventory Stock, Triggers vs Views/SP

    Hello..

    I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?




    1st Design

    PRODUCT TABLE
    ItemID
    ItemName
    Price
    QtyOnHand
    ..and other unique info of the product..

    SALES TABLE
    SalesID
    Date
    ...etc...

    SALESDETAIL TABLE
    SalesID
    ItemID
    QtySold
    Price

    PURCHASE TABLE
    PurchaseID
    Date
    ...etc...

    PURCHASEDETAIL TABLE
    PurchaseID
    ItemID
    QtyPurchase
    Price
    ...etc...

    and similar design with SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

    Tracking Inventory stock is done by using (update, insert and delete) triggers in each of the DETAILS to update the QtyOnHand in the PRODUCT TABLE




    2nd Design

    PRODUCT TABLE
    ItemID
    ItemName
    Price
    ...etc...

    INVENTORY TABLE
    ItemID
    QtyBegin
    ...etc...

    SALES TABLE
    SalesID
    Date
    ...etc...

    SALESDETAIL TABLE
    SalesID
    ItemID
    QtySold
    Price
    ...etc...

    and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

    The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:

    QtyOnHand = QtyBegin + Sum(QtySold) + Sum(QtyPurchase) + Sum(QtySalesReturn) + ........

    And at the end of a accounting period, the calculation of the QtyOnHand will be the QtyBegin of the next accounting period.

    According to you guys, which way is better in PERFORMANCE, RELIABILITY, ERROR FREE, and why? What are the pros and cons of these two?

    Thanks a lot.

  2. #2
    Join Date
    Jan 2007
    Posts
    1
    Hi

    I would suggest to have design 2 as my option. Performance without reliability is of no use. There is no meaning in giving a wrong information to the user.

    In the first design, you are storing the stock of each item whenever there is a movement of the product, either inward or outward. As per your design, let us assume, initially you are having a product, Product A, with no stock. Now you make a purchase on 01/23/2007 for quantity 50. Now the QtyOnHand will have value 50. Suppose you make a sales on 01/25/2007 for 30, as per your design you will update the QtyonHand field, which will now become 20. Now, if I need to get the stock of Product A on 01/04/2007, I cannot use the value in the field QtyonHand, which will be wrong. In this case it will be 20. But actually it must be 50. So we need to calculate the stock by adding the inwards and deducting the outwards. Also if you allow backdate billing, stock updation will become a serious issue.

    In the second design, I accept that the performance will be lesser than the first one. But by careful designing that can also be solved. For eg, having a seperate table which contains a date field, Product key and QtyonHand fields. For each purchase, sales and other product movement transactions, you can update this table for each day. There can be several other methods as well. If you come across please intimate me as well.

    with regards

    Abdur Raoof M

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    For what it's worth...
    No matter which solution you choose, don't forget to leave room to account for stock transactions that are not
    related to sales and receipts, such as damage or shrink.
    Invariably, Beginning inventory-sales+purchases<>actual ending inventory.
    Inspiration Through Fermentation

  4. #4
    Join Date
    May 2005
    Posts
    2
    @Abdul Raoof

    I very much agree that the 2nd one is far more reliable, but I am still wondering how much more do I have to pay for the reliability? I have done the 2nd one before, and the difference to load the 'item' table without calculating qty compared to load a view that calculates the QtyOnHand with many tables related (although there are only 2 tables with more than 30000 rows) is about 1 sec.

    And thanks for minding me the problem of the 1st design, never thought of that before.

    @RedNeckGeek
    I think I have included Adjustment Table if that's what you mean?
    Beginning inventory-sales+purchases-adjustment = actual ending inventory

    Thank you Guys.


    Anyone? Please... feel free to comment...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •