[thelist] inventory database design question

Richard Bennett richard.bennett at skynet.be
Mon Mar 10 15:40:00 CST 2003

----- Original Message -----
From: "Luther, Ron" <Ron.Luther at hp.com>
> Some ideas:
> * An inventory table often has two other attributes; a location field like
> "bin" and a quantity field.  <snip>
Good points.

> ** It looks like your tables combine some shipping/receiving and inventory
> functions.  Some folks keep the vendor purchase order files separate from
> inventory.<snip>
> [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.
True, but purchase order handling isn't currently in the specs, I'll keep it
in mind though.

> {Notice how I sidestepped the 'price out' issue?  If you really want to
> 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;
> 'inventory holding', and the many kinds of 'overhead' on your cost and
> side. }
It's actually used for sales, not for analysis so much.
It was part of my thinking that one product can not have more than one
selling price, even if parts of the stock were purchased at  different
prices. If the buying-price changed so much, that the selling price needed
adjusting, that would either be done globally for all of that product's
stock, or would be registered as a new product.
So priceIn can be different for each instance of the product, priceOut is
global for that product, and I could add a priceSold field to each instance
for analysis, although each transaction is logged, and that log might be a
better source for analysis.

> *** The serial number information confuses me a bit.  Why would you want
> be able to select a specific serial number from inventory?  {The only
> 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.
Yes, it is used for that.
But we're not actually talking about PC assembly here, but parts sale
(telephone diallers), so each separate article needs it's serial number
logged for the warranty.
You did get me thinking though, that maybe the serial number is best stored
at the time of sale, in the sales table, and not at the time the stock is
I have to check, I got the impression that the serial number would be read
by the barcode reader, as it is the number underneath the barcode, but now
that I think about it, that's probably not the case, and entering them all
by hand when they come in stock might not be such a good idea...

> 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
Too picky :o) In this case someone does all the work, and the paperwork is
done at the end.

> 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.
Yeah, that's what I was thinking a few paragraphs up.

Thanks for the detailed reply,

More information about the thelist mailing list