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

Invalid Objects Error

Whenever I use the DTS Wizzard to copy my database, I get an "Invalid Object" error and the transfer aborts. The error message says one of my views is invalid. If I delete this view and rerun, the copy is successful. When I recreate the view the error returns. The application that uses this database & view runs successfully everyday. I just can't seem to get the DTS package to do my backup / copy.

Help needed ASAP

Thanks in advance.Please post the DDL for the VIEW and the underlying TABLEs.

Hugh Scott

Originally posted by James Aiello
Whenever I use the DTS Wizzard to copy my database, I get an "Invalid Object" error and the transfer aborts. The error message says one of my views is invalid. If I delete this view and rerun, the copy is successful. When I recreate the view the error returns. The application that uses this database & view runs successfully everyday. I just can't seem to get the DTS package to do my backup / copy.

Help needed ASAP

Thanks in advance.|||Thanks for the help. This condition has existed for over a month. It seemed to have been created when I updated the definition of the view.

The database was originally upsized from MS-Access a year ago so I don't have the DDL for all of the tables. I attached a zip file with the DDL for the view, and current PDF's of the tables generated from an MS-ACCESS project file attached to the database.

Thanks in advance.

Jim Aiello|||I have to confess that diagnosing SQL code is not my strong suit. Some may wonder if I even have a strong suit ;-). It's a fair question.

In looking at your code, I am troubled by the white space between various concatenations. There's nothing wrong with it per se, but I just wonder if one of them is slightly off.

The other thing that came to my mind was the possibility that one or more records in the view might exceed 8000 (?) bytes -- the max allowed by SQL (the precise number is in BOL, and I don't have it in front of me). Looking at the underlying tables, that did not appear to be a problem, since the address fields appeared to be nicely limited to nvarchar(40). Still, if some of the white space were improperly delimited and it were combined with some extra-long addresses, I suppose it might conceivably result in a record with too many characters. Not sure how to check for and identify the problem record.

Some additional to do's for you:

1. Right click on the tables in EM and find Generate SQL Scripts (it's at the bottom of the pop-up, I think). Generate theSQL scripts and save them to a file. Then, post those scripts to the forum. That way others who are much smarter than me can help you work on the problem.

2. Populate a new database with the views and the underlying tables (from the DDLs you just created). Do your DTS Wizard while there is no data in the database (if it succeeds, then I might possibly be on to something). If it fails, then you haven't wasted much time and you can focus in on the syntax of the view.

3. If the DTS Wizard does work, then start populating the tables with data from your production machine. Take it in increments and see if you can re-create the error.

I hope this helps.

Hugh Scott

Originally posted by James Aiello
Thanks for the help. This condition has existed for over a month. It seemed to have been created when I updated the definition of the view.

The database was originally upsized from MS-Access a year ago so I don't have the DDL for all of the tables. I attached a zip file with the DDL for the view, and current PDF's of the tables generated from an MS-ACCESS project file attached to the database.

Thanks in advance.

Jim Aiello

Invaild Object Name

Thanks in advance!

I am in the process of creating a means for users to access data via views using MS Excel. The views I have created "pull" from data in two different databases into a third. I also wanted to use the data as well in some web reporting that I do. The problem I ran into in the web app is that it giving me the following error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.NVoltHSCLConsolProduction'

What did I miss in creating the views?

Thanks,
leeI assume NVoltHSCLConsolProduction is the name of your view? Can you access it from the QA and EM?

Try taking off the dbo in the reference.|||Yes, NVoltHSCLConsolProduction is the name of the view.

Yes, I can access it from both QA and EM as well as with MS Excel.

I use Dreamweaver UD for web apps and it's query building tool will access it as well.

I tried dropping the dbo, but did not help!|||I am assuming we have some kind of ownership \ permissions issue here. I am not familiar with Dreamweaver UD. What kind of web application is it? Is at ASP, PHP etc... If it is ASP I would check to see if the security context of the IIS web server (the IUSR_... account) has permissions to access the view.|||DW UD is for creating ASP. I have used views in the asp before. However they were views of data in that particular db.|||Sounds like you need to fully qualify your view with a three part name: Select thingamajig
From TheDatabase.dbo.NVoltHSCLConsolProduction|||The reason why you're not getting the error when using the view in EM and QA may be because of the default database setting for the account with which you registerd the server in EM and connected to in QA. Follow Max's advise.