Multiple Location Inventory Design

[Fairlight]

This public service announcement was brought to you by Fairlight:
> When asked his whereabouts on Thu, Mar 25, 2010 at 05:37:26PM -0400,
> Scott Walker took the fifth, drank it, and then slurred:
> > I have an order processing system.  Currently it only handles inventory kept
> > in one location.  Now a customer wants to start keeping inventory at
> > multiple locations.  So the same part# could be in the inventory at the main
> > office and also at several branch offices.  I'm just starting to think of
> > the design of this.  Should I have a separate record in the inventory file
> > for each part#/location combination?  Should I use a qualified file for each
> > of the inventory of each location? (I don't use qualified files for anything
> > at the moment).   Of course, this design decision permeates itself
> > throughout the system (ie.  Order Entry, Shipping, Purchasing, etc).  Any
> > design ideas or thoughts would be appreciated.
> 
> I'd go with a location field, and just have part#/location specific
> records.  Seems easiest to me.

Actually, I just thought about this from the perspective of, say, Best
Buy's web site, where you can check on store availability of and SKU.  When
you think about it in this particularly context, the ease shifts a bit.

Perhaps it would be better to maintain part# records with no availability
information directly in those records, and header/detail to a separate
"instock" table that is relational on part#, but -that- contains
part#/location/qty.  That'd make it easier to manipulate, perhaps.
I'd also actually tend to populate a record in that table for every
part#/location, and just set the quantity to 0 if it's not available there.
No adding/removing of records logic required, then.

Just some additional thoughts.

mark->
-- 
Audio panton, cogito singularis,