dot com> wrote:

>Hi,
>I have an inventory application where I keep track of items in different
>warehouses. I have a table where I mantain the onhand inventory, the
>current average cost, last cost, last purchase date, last date item was
>sold, and a few other data.
>
>Something like this:
>CREATE TABLE iInv
>(
> warehouseNumber int NOT NULL,
> itemID int NOT NULL,
> QtyOnHand decimal(15),
> avgCost decimal(10),
> lastCost decimal(10),
> CONSTRAINT PK_iInv PRIMARY KEY (warehouseNumber,itemID)
>)
>
>I then have transaction headers with their rows. The tables are called
>iTrans and iTransRows.
>iTransRows is something like:
>CREATE TABLE iTransRows
>(
> siteID int NOT NULL,
> transactionID int NOT NULL,
> rowID int NOT NULL,
> itemID int NOT NULL,
> Qty decimal(15),
> Cost decimal(10),
> Price decimal(10),
> CONSTRAINT PK_iTR PRIMARY KEY (siteID,transactionID,rowID)
>)
>
>In my application, once a transaction has been captured, it is "posted"
>which means that the corresponding row in the table iInv (above) is updated
>to reflect the current state in the inventory, taking into consideration the
>type of inventory transaction entered.
>
>I have been thinking lately that a BETTER design would be that the "posting"
>process, INSTEAD of manipulating the iInv table directly, to generate some
>sort of iInv_Transactions, and then with a trigger I update the iInv table.
>
>I'm thinking something like:
>CREATE TABLE iInv_Trans
>(
> iInvTransRowID int, -- auto generated
> dt datetime,
> itemID int NOT NULL,
> transType int NOT NULL, -- To know effect on
>inventory
> Qty decimal(15),
> Cost decimal(10),
> Price decimal(10),
> parentSiteID int,
> parentTransID int,
> parentRowID int,
> CONSTRAINT PK_iInvTR PRIMARY KEY (iInvTransRowID)
>)
>
>The main reason I want to do this is that should there be any problem with
>the table iInv, I would be able to rebuild it MUCH more easily with the
>iInv_Transactions in place.
>
>I would appreciate any comments on how is this typically done.
>
>Best regards,
>Edgard