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.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|||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.|||@.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...