Transaction-style Inventory?

[Kenneth Courville]

Hello, I'm looking for a little input on this situation.

I'm working on an inventory system and was thinking that I'd like to
build it similiar to the way a bank keeps track of your funds.

If you look at at bank statement, for each line item, you'll normally
see at least date/time, description, amount (plus or minus value), and
balance.

In my inventory system, the amount and balance would be a count of the
items used or transferred instead of money.

Does this sound reasonable? Or is there a better design? I am planning
to connect the inventory system to a few different ordering interfaces,
and I think it would work well if I can dump transactions into the
inventory instead of incrementing and decrementing counts.

The only problem I see with this, is if I want to implement the balance
part of it with each record....

I can't think of a way to do that currently.

I can certainly use a trigger in the database to retrieve the most
recent transaction and update the balance field for one record inserted
at a time, but if a list of transactions is dumped into the inventory
system at once, I'd have to compensate for that in the trigger.

Ideas?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ross Presser]

Re: Transaction-style Inventory?

On 15 Sep 2004 22:01:11 GMT, Kenneth Courville wrote:
[color=blue]
> Hello, I'm looking for a little input on this situation.
>
> I'm working on an inventory system and was thinking that I'd like to
> build it similiar to the way a bank keeps track of your funds.
>
> If you look at at bank statement, for each line item, you'll normally
> see at least date/time, description, amount (plus or minus value), and
> balance.
>
> In my inventory system, the amount and balance would be a count of the
> items used or transferred instead of money.
>
> Does this sound reasonable? Or is there a better design? I am planning
> to connect the inventory system to a few different ordering interfaces,
> and I think it would work well if I can dump transactions into the
> inventory instead of incrementing and decrementing counts.
>
> The only problem I see with this, is if I want to implement the balance
> part of it with each record....
>
> I can't think of a way to do that currently.
>
> I can certainly use a trigger in the database to retrieve the most
> recent transaction and update the balance field for one record inserted
> at a time, but if a list of transactions is dumped into the inventory
> system at once, I'd have to compensate for that in the trigger.
>
> Ideas?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

Balance can be a field in a view based on your table, like this:

CREATE VIEW InvWithBalance
AS
SELECT tID, tDate, SKU, Qty,
(SELECT SUM(tQty) FROM InvTransactions AS T2
WHERE T2.SKU = T1.SKU
AND T2.tID < T1.tID) AS [Balance]
FROM InvTransactions AS T2

[Kenneth Courville]

Re: Transaction-style Inventory?

ah.. silly me

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!