Mfg Inventory Structure

[SAC]

Could someone please help me with general design concepts in making a
manufacturing inventory.

I'm think I would use three tables:

Raw Material
Work In Process
Finished Goods

The finished goods inventory could have a part (assembly previously made) in
it which could be used in the work in process inventory to make another
part.

So it gets confusiong to me as to how to design it, etc.

I would appreciate any general ideas or a suggestion as to where to look or
if there's a sample somewhere.

Thanks for your help.

 

[Fred]

As a veteran in this area I can say:

Creating a database / tables that stores current inventory is not hard.
You're probably better off with a single table and three fields for those
three types of inventory.

Setting it up so that inventory is tracked by recording transactions
(manufacuring which "uses" and "creates" items) requires a huge amount of
things to be in place at your company. Procedures so that everything that
modifies inventory (shipping, receiving, moving inventory from one area to
another, manufacturing) gets "captured" as an inventory transaction. If
youre company already has that in place, you're ready to start on the DB
side. Otherwise step 1 is to reorganize 3/4 of your company's informaiton
system operations and step 2 is the DB work.

 

[SAC]

Thanks, Fred. We have the procedures in place so we're ready for the db.

Appreciate your input.

 

[Fred]

It's a pretty big job to implement transaction based inventory in DB...too
much for a post, but here's my advice on a structure: (of course my names
are just examples)

(Besides your people "procudures" you need bills of materials for each
product / process which creates or consumes inventoried items. Even for
simple situations, e.g. combining product and packaging if such uses and
created different part numbers. And a part number for each inventoried
item.)

An "item table" which includes every inventoried item. Fields including
(but limited to) part number, description, notes defining the units of
inventory (e.g. are you tracking rolls of wire or feet of wire?) And fields
for the current inventories of it in the 3 (raw, WIP, finished) categoris
that you want to track. You might want to take a hard look at whetehr you
really want to track all three of those seperatel on an ongoing basis. Or
whether those are really three different types of inventory vs. three
different types of items. (I'm talking databases here, not legal or
marketing, where, of course, those have meaning) The answer to this
question will dramatically change what follows, so from here out I'm vague at
this point.

Load the above described BOM's into a BOMItems table. One record for each
instance of use of a part number to make something. So, three fields
AssemblyPartNumber, ItemPartNumber, ItemQuantity.

Make a table for each instance of "manufacturing" an assembly. Including a
field to say the it's inventoryu modifications have been executed, as you
have to do it once and only once. And a query or procedure that makes the
appropriate additions and deductions when recording that the assembly has
been built.

If not already a part of your transactions, a table that records shipment of
items, and makes the appropriate deductions once and only once. for each such
transaction. Again, a query or procedure which executes it's deductions
from inventory

I'm assuming your purchasing is not so tidy that it's data systems are not
so perfect that they deal strictly in your part numbers. In that case you
need a table which records all other instances of adding or taking away
parts. (or parets from the inventory). E.G. receiving, scrapping /
write-off, changing between statuses (raw/WIP/Finished) if you really are
tracking those seperately for each item, adjustments to match physical
inventory. And again, query or procedure to execute those additions /
deductions once and only once for each such transaction.

Good Luck!

 

[SAC]

Thanks, Fred.

I really appreciate the input!

It's a pretty big job to implement transaction based inventory in DB...too
much for a post, but here's my advice on a structure: (of course my names
are just examples)

(Besides your people "procudures" you need bills of materials for each
product / process which creates or consumes inventoried items. Even for
simple situations, e.g. combining product and packaging if such uses and
created different part numbers. And a part number for each inventoried
item.)

An "item table" which includes every inventoried item. Fields including
(but limited to) part number, description, notes defining the units of
inventory (e.g. are you tracking rolls of wire or feet of wire?) And
fields
for the current inventories of it in the 3 (raw, WIP, finished) categoris
that you want to track. You might want to take a hard look at whetehr you
really want to track all three of those seperatel on an ongoing basis. Or
whether those are really three different types of inventory vs. three
different types of items. (I'm talking databases here, not legal or
marketing, where, of course, those have meaning) The answer to this
question will dramatically change what follows, so from here out I'm vague
at
this point.

Load the above described BOM's into a BOMItems table. One record for each
instance of use of a part number to make something. So, three fields
AssemblyPartNumber, ItemPartNumber, ItemQuantity.

Make a table for each instance of "manufacturing" an assembly. Including
a
field to say the it's inventoryu modifications have been executed, as you
have to do it once and only once. And a query or procedure that makes
the
appropriate additions and deductions when recording that the assembly has
been built.

If not already a part of your transactions, a table that records shipment
of
items, and makes the appropriate deductions once and only once. for each
such
transaction. Again, a query or procedure which executes it's deductions
from inventory

I'm assuming your purchasing is not so tidy that it's data systems are not
so perfect that they deal strictly in your part numbers. In that case
you
need a table which records all other instances of adding or taking away
parts. (or parets from the inventory). E.G. receiving, scrapping /
write-off, changing between statuses (raw/WIP/Finished) if you really are
tracking those seperately for each item, adjustments to match physical
inventory. And again, query or procedure to execute those additions /
deductions once and only once for each such transaction.

Good Luck!