ASP.NET, SQL Server, WPF, C#, VB.NET

I'm wondering if I'm setting myself up for problems......am I wrong in my design 
 
of a fairly simple Inventory DB. 
 
This is for receiving only.  Some items will be stocked on shelf, but most items 
 
will be delivered directly to various departments. 
 
Most of the samples I see have a "Products" and/or "Inventory" Table, and a 
 
Transactions Table.  I only have a Transactions Table.  (and P.O. Table). 
 
To get current stock level, I sum the transactions field. (+24 for 24  ItemXYZ 
 
received, -8 for when some was delivered to department). 
 
So, my thinking is why have *any* table that stores an Inventory? But now, as I 
 
said, I'm wondering if I'm missing something obvious.... 
 
Also,  the P.O. Item(s) when ordered are inserted into the Transactions Table as 
 
a positive, but with Status of "OnOrder".  When Recieved, the Status is changed 
 
to "Received".    I wonder about the wisdom of doing that, also. Would it be 
 
better to add the P.O. Items at all (to the transactions table) until Received. 
 
If it would be better to do that, then I suppose, to get current stock info 
 
would take sum of Transaction table to get Stock on hand, then add the sum of 
 
P.O. "OnOrder" items (for each item). 
 
Thoughts? 
 
Thanks, Josh

It is a rural health center, so we don't "make" or "ship" anything, just 
 
receive.  I do have a ItemTable, which list the Items/Parts that we order, which 
 
has PartNumbers, Descriptions, UOM's, prices, Manufactors, VendorID, etc along 
 
with a VendorTable. And, of course, a POTable(purchaseOrders) & POdetails table. 
 
The comment you make reference coping with weird things, is why I thought I 
 
would track that sort of thing in the PurchaseOrderTable.  In that table, I have 
 
fields [NumberOrdered], [NumberRecieved], [remarks] which I hope will track that 
 
sort of thing. 
 
Thanks, Josh 
 
On Thu, 17 Aug 2006 11:10:30 +0800, "Allen Browne" <AllenBrowne@SeeSig.Invalid> 
 
wrote:

Josh, if you can calculate the inventory, there is absolutely no need to 
 
store the current inventory levels. It is far better to take the normalized 
 
approach, and NOT store the dependent value. 
 
I didn't understand the bit about not having a Products table though. If you 
 
store anything other than ItemXYZ, it seems to me that you would need 
 
records for the different products. 
 
You probably do need a way to handle transactions a bit more than you have. 
 
The database must cope with weird things such as: 
 
- You ordered 100, but they actually sent 1000. 
 
- You ordered 500. They sent 100, and backordered 400. 
 
- There were 100 backordered from last time, plus the 200 from a new order, 
 
so they sent 300 at once. 
 
- You ordered 100 for a line that is discontinued. They sent the 24 they 
 
had, but the others will never be sent. 
 
-- 
 
Allen Browne - Microsoft MVP.  Perth, Western Australia. 
 
Tips for Access users - http://allenbrowne.com/tips.html 
 
Reply to group, rather than allenbrowne at mvps dot org. 
 
"Josh" <traygo@hotmail.com> wrote in message 
 
news:q347e25fpe1rke8qnnfvp2ouv6gs9pquhu@4ax.com...