Rqst for Inventory Database Best Practices
Date: Mon, 30 Jan 2006 01:41:59 -0800
Message-ID: <43dddf75$0$95987$742ec2ed_at_news.sonic.net>
I am developing an inventory database. 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:
- What products are in stock.
- What products are on order from the manufacturer.
- What products are needed to fulfill outstanding sales.
- Warehouse operations (e.g., receipt of delivery, inspection, add to inventory, pull from inventory, packing lists, invoices, return to inventory, etc.)
- 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:
- 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.
- 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.
- 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
Received on Mon Jan 30 2006 - 10:41:59 CET