Re: Schema question

Answer embedded.

J.Teo <jcteo_at_yahoo.com> wrote:

>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.

There are good and bad aspects to store vs. calculate in an operational database, and no one answer fits every situation. Point-in-time access to various quantities in the database will require a good amount of CPU power to scale, and typically isn't provided for in a transactional system unless absolutely required.

Note that I say transactional system, and I'm not including reporting as part of that. I try to design so that these tasks are as logically seperated as much as possible.

>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

You have to weigh the cost of calculating the stock level for every request vs. storing the current level and making sure your log tables are up-to-date.

Repeated calculations, especially running a complex view on a row-by-row basis, can be very taxing on the CPU (and hard to tune) if you're doing a lot of queries. Look at the typical scenario of where the stock level is used and decide if it's practical to support that.

My preference would be to have updates to the product table drive the log file (trigger based), as opposed to updates to a log file driving the product table (view based).

> 2. we need the ability to generate stock report for any point
> in time, so the above-mentioned view is needed in any case

If point-in-time requests are only necessary for reporting purposes, I'd recommend only storing the current value in the product table and have an auxillary table log the changes(trigger based). A good rule of thumb is to segment your reporting and operational procedures early on, because if your database grows to any worthwhile size, you will _need_ to distribute them. If everything is woven together with a web of triggers (logs updating product tables and vice-versa), it becomes very hard to do.

>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.

Under your approach, I would think a transaction would simply involve adding a row to the outgoing table. ???

> 2. may not scale well ... as the logs get longer, it takes
> longer to figure out the qty on hand

I've worked with systems where this approach has scaled to about 10 million rows over several tables (on beefy servers), so it can be supported if you need it. At some point you will want to do roll-ups of older transactions if you're doing any kind of volume. Received on Tue Feb 20 2001 - 14:33:18 CST