Posted by DeepDiver on 01/30/06 11:49

I am developing an inventory database in SQL Server. I realize there are
many commercial (as well as some non-commercial) inventory offerings, but my
client has specific requirements that would necessitate significant
customization of any off-the-shelf application. In the end, we decided it
would be more feasible to build one to our specifications.

What I am looking for are a list of best practices/recommendations for the
architecture and modeling of an inventory database. This inventory
application will be for managing a wholesale products operation. It must
manage purchase orders to the manufacturer of the products, sales invoices
for the retail customers of the products, as well as manage product stock
levels in the warehouse. We will need a number of reports, including:

1. What products are in stock.
2. What products are on order from the manufacturer.
3. What products are needed to fulfill outstanding sales.
4. Warehouse operations (e.g., receipt of delivery, inspection, add to
inventory, pull from inventory, packing lists, invoices, return to
inventory, etc.)
5. Sales analysis (e.g., product velocity, sales by associate, etc.)

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.

3. A combination of the above two concepts. Choose a point in time (for
example, when the warehouse inventory is audited) and capture that in a
(semi) "static" table. After that, transactions are summed as needed against
those static numbers. The next time the warehouse is audited, the "static"
table is updated and all prior transactions are archived. New transactions
are once again summed against the most recent audit numbers. Of course, this
would mean halting operations at the time of the audit (probably not an
issue for my client's business).

Recommendations? Suggestions? Comments?

(Btw, I've tried to find this info in books and online, but so far have not
found anything that makes specific inventory system design recommendations.
If you know of a good reference, please let me know.)

Thanks in advance!
Michael

[Back to original message]