>>>>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?
>
>IMHO it'd make them easier.
>Cetin

I'm trying to picture how to join the tables but I cannot think of a way that would work. Also, how would you define foreign key constraints with this structure?