>>I seem to be having problems wrapping my head around this scenario.
>>
>>I have a table (Wire) where I want to keep track of what's on each of the two ends of the wire. Before, we just stored a foreign key to the Gadgets table for each end. Now the user would also like to attach a Gidget and a Gizmo to each end of the wire, which reside in their own respective tables. Each end of the wire can have only 1 type (Gadget, Gidget, or Gizmo).
>>
>>So would the best way be to store a WireEndType field (Either Gadget, Gidget, or Gizmo) and a WireEndKey field (Foreign Key to the another table based off of the WireEndType field)? Or is there an easier way to do this that I am just not thinking of. Gadets, Gidgets, and Gizmos are not alike enough to store in the same table.
>>
>>Thanks for any help on this problem!
>
>I think your way is good. Something like:
>
>wireID
>endPoint (enum:Right,Left)
>endPointType (enum:Gadget,Gidget,Gizmo)
>endPointID
>
>This structure is not only easy to deal with for T-SQL also it represents an easy model for hierarchical treeview likew chains. Keep away from storing right-left on same row (or in the future you might hit to another bottleneck - ie: what if a 3rd endpoint comes into play). IOW do not design your structure "pivot"ed.
>Cetin

Cetin,

Wouldn't this structure cause problems if you wanted to join to Gadget, Gizmo, and Gidget tables?