[thelist] inventory database design question

[Luther, Ron]

Richard Bennett asked:

>>I'm working on an inventory application, and  have a question about the
>>database design.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Hi Richard,

Fun Stuff! I don't know if you are overlooking anything for the
application you are building - but I've seen some of this done a
little bit differently - and that might be helpful.

Some ideas:

* An inventory table often has two other attributes; a location field like
"bin" and a quantity field.  So it might look like Part_No, Bin, Sub-Bin,
QTY.  [The description field, along with other fields like commodity type
and things might either be included in this table - or pushed off into a
separate 'inventory master' table.]

You can have 'intelligence' in your bin numbering system or add a separate
field to allow you to differentiate between "stores" (inventory sitting on
the shelf), "WIP" (inventory in the process of being used to build stuff
to ship), "Shipping" (stuff packaged to ship that hasn't made it out the
door yet), "Rework", "Inspection", "Reject", and whatever other
specialized categories you might need.

It's also useful to have the 'bin' and 'QTY" fields so that your company can
conduct cycle counts to validate your inventory investment and to 'correct'
the accounting 'perpetual inventory' file.


** It looks like your tables combine some shipping/receiving and inventory
functions.  Some folks keep the vendor purchase order files separate from
inventory. This would be a table of open purchase orders (of various types)
- and a separate table of receipts ... since you can have multiple receipts
against a single line on a given purchase order. {I ordered 100 and wanted
the vendor to deliver 50 one week and 50 the following week.  The vendor
actually delivered 70 the first week, 20 the second week, and 12 the third
week. <No - my math isn't wrong - expecting things to always add up is
wrong!>}   ;-) [The price information would be in the purchase order tables.]

This would still let you do your 'FIFO Cost Layering' analysis for the
accounting folks -- by backing current inventory quantities through the
PO receipt table.

This would also let you conduct a performance analysis against your different
vendors with respect to price, quality, or on-time delivery percentage.

{Notice how I sidestepped the 'price out' issue?  If you really want to get
into margin analysis, then you're going to need to get into customer bulk
discount rates, contractual pricing, offsets from 'trade-ins' and maybe a
few other things on the revenue side ... and the rest of your costs; 'labor',
'inventory holding', and the many kinds of 'overhead' on your cost and COGS
side. }


*** The serial number information confuses me a bit.  Why would you want to
be able to select a specific serial number from inventory?  {The only reason
I can come up with is in handling a vendor product recall -- is there some
other reason?}

My impression is that many folks assign serial numbers as a part of the
shipping function.  That way a specific unit can be tracked to specific
customer and sale for warranty service.

>>>>>>>>>>>>>>>>>>>>
>>When an article leaves stock, we'd like to be able to say:
>>sold:
>>modem with serial nr111
>>3 x phone cable
>>>>>>>>>>>>>>>>>>>

Maybe I'm being too picky here ... and maybe your system is different, but
some companies issue a 'material movement' transaction to move the items
from stores/stock to WIP where they are put in a box and then another
'material movement' to SHIP where they wait for carrier ground
transportation. Once the items ships out the dock, then it might get
counted as a sale.  The movement from stock doesn't get counted as the sale.

I think it's also easier and more accurate to administer serial numbers as
a shipping rather than an inventory function ... if you have several
people fulfilling customer orders (pick, pack and ship) and they are
each working on an order for 10 systems that involve pieces-parts (like
modems) that might have serial numbers from the manufacturer ... it's
sometimes more efficient to have one of them go back to stores and
bring 30 modems up to the production line to share in packing than to
have each person go back and look for specific serial numbers.


HTH,

RonL.