Re: Rqst for Inventory Database Best Practices

From: Murdoc <murdoc_0_at_hotmail.com>
Date: Mon, 30 Jan 2006 11:56:15 +0000 (UTC)
Message-ID: <xn0ehuxz9938p8000_at_news-south.connect.com.au>


DeepDiver wrote:

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

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

Murdoc

-- 
Received on Mon Jan 30 2006 - 12:56:15 CET

Original text of this message