Re: Schema question

In article <3A921F6A.2D17A584_at_yahoo.com>, "J.Teo" says...
>
>
>I am developing a inventory application. Our still evolving schema
>currently doesn't record the quantity on hand for a particular part in
>the inventory. Instead, it logs everything going in and out of the
>store. And we have a rather complicated view to figure out the quantity
>on hand.
>
>We did it this way because:
>
> 1. it seems like duplicate information to store the current state
> since it can be figured out of the log
>
> 2. we need the ability to generate stock report for any point
> in time, so the above-mentioned view is needed in any case
>
>My question is, are there any serious problems with this approach?
>
>I can think of a couple:
>
> 1. not clear how to do transaction since we're not simply
> decrementing the quantity on hand when something is
> delivered from the store.
>
> 2. may not scale well ... as the logs get longer, it takes
> longer to figure out the qty on hand
>
>May be able to solve 1 by doing a 'fake' update to basically lock a part
>explicitly. And 2 can be solved by periodically taking a snapshot so it
>doesn't have to look at everything from the beginning of time.
>
>Sorry if I sound like I'm answering my own questions. I'm relatively new
>to database application development, so I don't know what the 'standard
>practices' are. I don't want to repeat lessons others have learned. So,
>I'm hoping to get some helpful comments on this.
>
>Thanks.
>
>-jay

This looks like a perfect candidate for a trigger. I would store the quantity on hand in my products table. and decrement it by means of a trigger whenever an order gets delivered. Your logs will soon become unmanageable, as you will have to keep the complete history.

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Feb 20 2001 - 04:43:35 CST