Need DB Design gurus help please

[KB]

I have a complex relationship that I need to store in a database. What I have is some equipment in an equipment table. This equipment gets moved around and can be placed in one of many Warehouses, Vehicles or Vessels, each has their own table. The history of these moves needs to be tracked. How do I link these correctly?

I have thought of two options diagrammed in:
http://pics.bbzzdd.com/users/kbradl1/option.GIF

What is the better solution or is there a better solution?

 

[MrChad]

One possibility is to use option B in combination with an auditing table. The auditing table would track when equipment is assigned to/deassigned from Warehouses, Vehicles or Vessels. You can place triggers on your Warehouse_Equip, Vehicle_Equip and Vessel_Equip tables to write entries in the audit log.

The benefit of this solution is that allows you to easily determine where equipment currently lives, but it also allows you to maintain the history you're looking for.

 

[KB]

^

 

[Argo]

I'm undersanding equipment can move from vessen to vehicle to warehouse, etc. There's really no perfect solution to this design. What I would do is create uniform ID system for all 3 tables (vessel, warehosue, vehicle). Then create EQUIPMENT_MOVE table that will contain 4 columns:

EQUIPMENT_ID
MOVED_FROM
MOVED_TO
MOVE_TYPE

Where MOVE_TYPE is 0 if you're moving from vehicle to vehicle, 1 if move is from vessel to warehouse and etc, I believe you should have 6 types total. MOVED_FROM and MOVED_TO columns will contain IDs of vessel, vehicle or warehouse (that's the reason we needed a uniform id system).

**************

A better solution, but this would only work if Vehicle, Warehosue and Vessel objects can be stored in the same table (i.e. they have the same attributes). Put them all into the same STORAGE table and add an extra column to that table OBJECT_TYPE which will distinguish vehicle, vessel warehosue recrods. Then you would still have EQUIPMENT_MOVE table but you no longer need MOVE_TYPE column.