table design question

[Travis Parrent]

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

 

[Tim Ferguson]

to access and couldnt' find a dbdesign group for sql,

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.

If you define a Location as somewhere that a part could be in, then you
have quite a lot of them:

General Inside Processed
General Inside Unprocessed
General Outside 1
General Outside 2
General Outside 3
General Outside 4
General Outside 5
General Outside 6
General Outside 7
General Outside 8
Scrap Unmolten
Scrap ForMelting
Repair Repairable
Repair GoneForEver
Recycle Glass
Recycle Aluminium

etc... You might want a convenient coding scheme for this, or you may even
need to do some subclassing (depending on what type of information you need
to manipulate in respect of the Locations). Nevertheless, this is what the
Locations entity has to look like, if you are going to put Quantities into
it.

Hope that helps


Tim F