adding to inventory database

[Mike W]

I have been trying to add a purchasing component to the downloaded inventory
database with no luck. I would love to see a purchase component to this
database so I can order the items that are low in stock. I tried to combine
the purchase order database and change it, because it looks to be more geared
for selling items then purchasing them. I have worked through the tutorials
but with no luck. I can change many of the properties but I can't seem to
get things to work. If any one knows where or how I can go about this I
would be very greatfull

 

[Allen Browne]

There's a fair bit more to it than just adding a couple of tables, Mike.

Purchase orders involve multiple suppliers, with a many-to-many relation
between products and suppliers. Each purchase order can have line items at
varying stages of completion, e.g. for each row you could have:
- all items received,
- all items pending,
- some items received, and some on back-order,
- some items that will never be received (e.g. supplied discontinued stock),
- some items arrived damaged (returned for replacement? returned for refund?
returned, but claim refused? disposed of, not claimed?),
- additional items sent other than what was ordered (mistake? free samples?
....?),
- mismatch between what was ordered and what was sent,
and so on.

That's just one aspect of the issue. There may be others, such as multiple
warehouses each with their own stock levels, and stock arriving at the wrong
warehouse. Then there's the whole issue of periodic stocktakes, where you
re-adjust the actual quantity from the calculated quantities from
time-to-time.

In short, the sample database just illustrates some basic principles. It
cannot be a fully functioning application that handles all the quirks of
your unique situation. Hang, it doesn't even try to handle validation issues
or ensure that you entered sensible dates.

There is a great deal more work involved in writing a real application.

 

[gllincoln]

First of all - read Allen's response because he is absolutely correct....

However - in case you want to generate a suggested list of inventory items
to be restocked/ordered - and then incrementally add to the feature set of
your work.

I haven't checked out the downloaded inventory database - it may have most
of this stuff.

In the inventory table or in a related table, you need a minimum stocking
quantity, and a standard order quantity (set to zero if you do not wish to
stock the item), and a column that identifies your source. (Multiple sources
are beyond the scope of this primitive example) After you update the on
hand quantities - you would periodically run a query that would return all
inventory line items and the standard order quantity where the minimum
stocking quantity was greater than the on hand quantity and the standard
order quantity is greater than zero.

You might want to include a table of your vendors and be able to pull
multiple purchase order lists based on the source of the particular line
item(s). And, one step further - you build a purchase order table based on
the query and mark the line item as ordered with an order date and order
number and an order quantity. While this can be done with a related table,
many many production databases will go ahead and include these bits of
information in the main table for the sake of efficiency, particularly where
the data is being pulled across a LAN and/or in multi-user situations where
record locking is a problem.

The artof database design is to normalize until it hurts, then go the other
way until it works. Don't know who said that first but it's certainly a
truism.

The real power and labor-savings of combining purchasing with inventory
comes about when you have the ability to match up packing slips with
purchase orders and automate the inventory update with the received goods
and back orders, etc.

Regards,
Gordon