Apologies in advance if this isn't the right group for this question!

Forewarning: I'm not a DBA, I have limited experience working with
MySQL on a personal, small-scale level.

I'm trying to design a functional database for some inventory
tracking. Here is the structure I'm currently working with. All IDs
would be surrogate, auto-generated keys...I've omitted fields that are
useless for this scenario, such as serial number.

Device
DeviceID (PK)

Chassis
ChassisID (PK)
RackID (FK)

Rack
RackID (PK)

Office
OfficeID (PK)

The idea is that we can find out, through the DB, exactly where a
Device is. It could be in an Office. It could be in a Rack. It
could also be inside a Chassis that is inside a Rack (a chassis is -
always- inside a Rack, hence the FK).

Every halfway sane scenario I can come up with involves either a FK
that points to multiple tables (which I gather is a big no-no), or
having a nullable series of FKs, possibly in a separate location
table:

Location
LocationID
RackID
ChassisID
OfficeID

So a device in a rack would have a LocationID FK, and that LocationID
would have a RackID, but null ChassisID and OfficeID.

Both of these seem like sub-optimal solutions.

Ideas? Am I missing an obvious solution? Do I need to go back from
square one and re-work this? Any guidance would be greatly
appreciated.

On Mar 19, 11:09*am, JayCee <trackmyg... (AT) gmail (DOT) com> wrote:
No DeviceID?
They are.
You are designing a tracking DB and do not identify exactly what you
are tracking?!

Questions:
If the "device" is in the "chassis", is it now part of the chassis?
Are these "locations" really different? If the goal is to track
devices, do you really need to know that a chassis is in a rack? If
device A is in chassis B is in Rack C then isn't A just in C?
Apparently a chassis is not a location, so this problem is not well
defined.

Another probably poor design is:

Table LOCATION
type enum device, office, chassis, rack
item_id number
LOC_ID PK

Sample data
Type item_id loc_id
device 5 1
office 5 1
office 6 3
chassis 7 4
chassis 8 5
rack 1 5
device 3 5
rack 2 6
device 4 6
device 9 9

I'll try to think some more about this, meanwhile, could you answer my
questions.

Ed

I could put DeviceID as a FK, pointing to the Device table. Or I
could put a LocationID as a FK in the Device table that points to the
Location table. Either way would be Ok to me, but I don't think
either is an optimal solution.

I'm not sure what this is in reference to. Everything (rack, chassis,
devices) is identified in each respective table, but you may be
talking about something else. Specifically you may be referencing the
Rack and Chassis definitions, which I've included below.

I don't really know how to answer this question because I don't know
what you mean by "part of". It is a "part of" the chassis, as much as
a cup in a cabinet is "part of" that cabinet. Again, this may be due
to my lack of defining my terms! I've done so below.

Yes.

That is my fault for not clearly defining the storage locations.

A rack has vertical storage space. A typical rack will have 42 units
of storage. A device could occupy 1 or more units if directly
installed in a rack.

A chassis has horizontal storage space. A typical chassis will occupy
8-10 units of vertical rack storage, but adds 12-16 units of
horizontal storage within itself.

So a chassis will -always- be in a rack. However, a device may be in
an office, rack, or chassis.

I've thought about this as well, but it seems to revert back to the
problem where I have what is essentially a field that is pointing to
multiple tables.

I've also considered having separate tables for Devices
(OfficeDevices, RackDevices, ChassisDevices), but then I would have
duplicate tables that store the same information, and just reference
different locations.

I think a simpler stating of this problem (and what I can't wrap my
head around) is, what is the optimal way to store location information
for an item that can be in more than one type of location. I don't
think the Office table is even relevant right now...whatever solution
for the Chassis/Rack table problem would encompass it. More than one
location shouldn't present a problem (i.e. items in different
addresses), it is the location type that is throwing me off. Every
solution I can think of involves either having a field pointing at
multiple tables, or having one non-null value amongst other null
values to indicate location type.

Thanks for taking the time to ponder my predicament.

On Mar 19, 10:39*pm, JayCee <trackmyg... (AT) gmail (DOT) com> wrote:
[]
Re: "part of"
I was considering the possibility that a chassis was a something like
a System bus and a device was like an expansion card, hence becoming
part of the chassis. I think I might have caught on if you had called
them parts. But that is what discussions are for, readers have
different notions than writers. If we all thought the same, we
wouldn't be here.

Anyway...

I think (it's late now) is that you have an entity: LOCATION. And a
location can then be more specifically defined as RACK, CHASSIS, or
OFFICE.

So a device has a location ID attribute (an FK to LOCATION).

LOCATION has the ID as its primary key,

RACK has a primary key which also happens to be a FK to the location
ID.
CHASSIS has a primary key which also happens to be a FK to the
location ID.
OFFICE has a primary key which also happens to be a FK to the location
ID.

Note none of those three tables have an auto increment ID. They are
specializations of location.

There may be better ways, since in your model the LOCATION might not
have any other attributes. But that would work.

So LOCATION might have four rows with ID values of:
1
2
3
4
5
6

then in RACK might have
ID description other attributes
1 lane 1 first on right ...
3 lane 2 first on left ...

CHASSIS might have
ID RACKID(FK to RACK) description other attributes
2 1 first chassis ...
5 1 second chassis ...
6 2 first chassis ...

OFFICE might have
ID description other attributes
4 Bill's Office

Most queries are going to join all four tables to find the complete
information of a location. Point is, the DEVICE table has one FK
location attribute (the location ID).

Hope that helps.
Ed

On 03/19/2010 04:09 PM, JayCee wrote:
And in this case you should also have a check constraint which avoids
illegal combinations.

One solution you apparently did not yet consider is inheritance. Your
containers could inherit an abstract base class solely for the purpose
of containing Devices. You could model that with an additional table
which carries a PK column and possibly also a type discriminator. All
other container tables and the Device table then had FK's to the base
class table

Drawback is that you now make all your tables depend on the base class
table which could impose a bottleneck - performance wise.

Another solution would be to have a single pseudo FK column and a type
discriminator which denotes the target table but I don't like that
because it is not "type safe" - the FK type needs to be a superset of
all FK's used which might work in your case because they are all
surrogate keys but the DB has zero chance of checking integrity
efficiently (you could implement it with triggers but I'd rather not go
down that road - it's too fragile and error prone).

All in all your suggestion with the various FKs augmented with a
constraint avoiding illegal combinations of FK's seems the best
compromise to me. The DB can automatically check integrity and your
FK's are "type safe" meaning you use exact the proper FK types for each
container table. The cost is of course a larger table - large in terms
of columns not necessarily storage. Btw, you can also put this in a
separate table which only defines relationship between Device and its
various containers.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Mar 20, 5:31*pm, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
Thanks Ed, Robert. I'm working right now with the tables using the
LocationID as a FK in the separate locations and it seems to be doing
what I want!

On Mar 22, 10:16*am, JayCee <trackmyg... (AT) gmail (DOT) com> wrote:
[]
Now your next big step is to get some data modelling training to
prevent your next project from going off the deep end.

Ed