Re: Rqst for Inventory Database Best Practices

[snip]
>>
>> My main question is regarding the managing of products in stock. As I see
>> it there are three ways
>> of accomplishing this:
>>
>> 1. Track only transactions (products received, products shipped, etc.)
>> and calculate the stock
>> based on the sum of all transactions. But as transaction volume
>> accumulates over time, this would
>> get very slow and cumbersome.
>>
>> 2. Have a table of "units in stock" and add and subtract to it as
>> transactions occur. This has
>> the advantage of always providing an instant snapshot of inventory
>> levels. But it makes it more
>> difficult to manage changes or corrections to a transaction once it has
>> been entered.
>
> Not necessarily. Have two states for a transaction (Finalized & Pending).
> When moving between the
> two states, update the table values accordingly. We have to do a similar
> thing with our system
> (different domain, but similar concepts).
>
> <snipped rest />
>
>

No matter what else you design, be sure to consider "SHRINKAGE" or theft by employees. These will not show up in sales statistics because no sale was made.

Also be mindful of mistakes on logging inventory into the warehouse, these are the most common causes of incorrect inventory on hand calculations.

BFaux - Received on Fri Feb 03 2006 - 01:11:51 CET