Re: Rqst for Inventory Database Best Practices

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

.

Relevant Pages

  • RE: Normalised Stock Table
    ... The current version is an Invoicing & stock control system set up to handle ... tracking and inventory tracking of individualized/serialized items and items ... cause all transactions and tracking to occur at the individualized ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Inventory
    ... You can do an update query to adjust the quantity value in your inventory ... bill should probably include the ability to have a qty for each bill item. ... link to the Bill of Materials table to release those items from inventory. ... > I need it to subtract from my Inventory Transactions table per the example ...
    (microsoft.public.access.tablesdbdesign)
  • Rqst for Inventory Database Best Practices
    ... I am developing an inventory database in SQL Server. ... What products are in stock. ... Warehouse operations (e.g., receipt of delivery, inspection, add to ... calculate the stock based on the sum of all transactions. ...
    (comp.databases.ms-sqlserver)
  • Rqst for Inventory Database Best Practices
    ... I am developing an inventory database. ... What products are in stock. ... Warehouse operations (e.g., receipt of delivery, inspection, add to ... calculate the stock based on the sum of all transactions. ...
    (comp.databases.theory)
  • RE: Normalised Stock Table
    ... Next you have to define your mission in detail with respect to transaction ... tracking and inventory tracking of individualized/serialized items and items ... cause all transactions and tracking to occur at the individualized ...
    (microsoft.public.access.tablesdbdesign)