Schema question

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 Received on Tue Feb 20 2001 - 01:41:27 CST