I am working with msde2k, but figure this problem could relate equally to
access and couldnt' find a dbdesign group for sql, which is why i'm
crossposting to these 2 groups.

I am working on an inventory type application db design.  We are tracking
only total quantities of parts and not individual parts.  The primary
inventory is of different types of reusable containers.

The overall goal is to simply say how many of X container do we have in Y
location(s) at a given time.

The locations for the parts are first what site it is at, followed by one of
4 states (General, Scrap, Repair, or Recycle).  The General State is further
broken down into Inside and Outside Inventory.  Inside Inventory is broken
down into Processed and Unprocessed, and Outside is broken down into various
zones (currently zones 1 through 8), but each site could have a different #
of zones.

I'm trying to follow good db design as well as I can here, but the issue
comes into Null values, etc.

My first idea was to just have the Inventory table be PartID, SiteID,
StateID, LocationID, SubLocationID and Quantity, with all of the ID's being
a cumalitive primary key.  I didn't really like this since Location
(inside/outside) and Sublocation(processed, unprocessed, zones) couldn't be
null for other states (repair, scrap, etc).

Then I thought I would define part locations in a seperate table:
Inventory : PartID, PartLocationID, Qty
PartLocation : PartLocatinID (identity), SiteID, StateID, LocationID,
SubLocationID (putting a unique contraint on site,state,loc,subloc combo)

But again I end up with null values in the locationID and SubLocationID,
which I think its a bad idea to have null values when its a foreign key to
another table and part of a unique index/constraint.  Each of the ID's above
has an associated table so that they can add Sites, States, etc later on.

I just didn't think this was a good design and would like to hear other
suggestions before I get to far into this.

Thanks,
Travis