Inventory DB

[Mark L. Breen]

Hello All,

My questions is regarding the structure of a database that stores inventory.

I have two types of transactions, one the increases the effective inventory
level and one that decreases the effective levels. To calculate the actual
inventory level, I can sum all the increases and subtract from that all the
decreases. This would give me my inventory balance.

Obviously, that query could get quite slow, so it is desirable to store the
actual balances in an inventory table. This would give you an immediate
record of what is in stock for a particular part. I am considering
including an Adjustments table. This will basically keep track of the old
balance, the adjustment and the new balance.

My question is how do you guys handle this kind of stuff? Is there a
recommended way to handle inventory? I am nervous that I will end up with
the calculated stock not being equal to the static stock. I know I am
breaking the rules of normalisation by storing a calculated field, but even
the banks use this tactic.

You advice will be much appreciated.

Thanks in advance,

Best Regards

Mark Breen
Solution Providers Ltd
Ireland

[Gene Wirchenko]

"Mark L. Breen" <ma...@solution-providers.ie> wrote:

>Hello All,
>
>My questions is regarding the structure of a database that stores inventory.
>
>I have two types of transactions, one the increases the effective inventory
>level and one that decreases the effective levels. To calculate the actual
>inventory level, I can sum all the increases and subtract from that all the
>decreases. This would give me my inventory balance.
>
>Obviously, that query could get quite slow, so it is desirable to store the

^^^^^
And maybe it won't. I'd check this assumption first.

>actual balances in an inventory table. This would give you an immediate
>record of what is in stock for a particular part. I am considering
>including an Adjustments table. This will basically keep track of the old
>balance, the adjustment and the new balance.
>
>My question is how do you guys handle this kind of stuff? Is there a
>recommended way to handle inventory? I am nervous that I will end up with
>the calculated stock not being equal to the static stock. I know I am
>breaking the rules of normalisation by storing a calculated field, but even
>the banks use this tactic.

Banks also charge obscenely high services fees, so not all they
do is right. <g>

Check that it would be significantly slower. If you denormalize,
then you have to worry about balances getting out of sync. You either
ignore this or have to write a special program to deal with it.

Having said that, some possibilities:

1) Consolidate old transactions. You might keep only the last
year's transactions and consolidate the earlier ones.

2) If you have a definite closing of a period, you might store the
balance as of the last complete period. This could get you into the
same hassle above, but if you don't post to prior periods, it could be
a reasonable kludge.

>You advice will be much appreciated.

Oh, good! Gratitude is nice.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

[Mark L. Breen]

Hello Gene,

Interesting suggestions. You were the only one I mentioned this question to
that expressed reservations regarding the tactic. I am interested in why
you suggest,

> And maybe it won't. I'd check this assumption first.

Would you care to elaborate on this?

Anyway, thanks for your time and effort, I genuinely do appreciate it.

--
Best Regards

Mark Breen
Solution Providers Ltd
Ireland

Gene Wirchenko <ge...@shuswap.net> wrote in message
news:38a43553...@news.shuswap.net...

[Donald J Kinney]

Mark L. Breen <ma...@solution-providers.ie> wrote in message
news:38a3...@royan.d-n-a.net...

> Hello All,
>
> My questions is regarding the structure of a database that
stores inventory.
>
> I have two types of transactions, one the increases the
effective inventory
> level and one that decreases the effective levels. To
calculate the actual
> inventory level, I can sum all the increases and subtract
from that all the
> decreases. This would give me my inventory balance.
>
> Obviously, that query could get quite slow, so it is
desirable to store the

> actual balances in an inventory table. This would give
you an immediate
> record of what is in stock for a particular part. I am
considering
> including an Adjustments table. This will basically keep
track of the old
> balance, the adjustment and the new balance.
>
> My question is how do you guys handle this kind of stuff?
Is there a
> recommended way to handle inventory? I am nervous that I
will end up with
> the calculated stock not being equal to the static stock.
I know I am
> breaking the rules of normalisation by storing a
calculated field, but even
> the banks use this tactic.
>

> You advice will be much appreciated.
>

> Thanks in advance,

>
> Best Regards
>
>
> Mark Breen
> Solution Providers Ltd
> Ireland

Keep one database with records for the different parts with
two fields for each period of time one for Items received
and one for Items sold. Also have a Total on Hand field and
of course the part number fields. Using months as your time
period you will have a total of 26 fields.
This will also give you a way to see how fast the turn
around on the parts is. You will see when you received the
parts and the time it took to sale the parts before
reordering.
Update the total field either daily or when you receive sale
the part as part of your program.

Donald