>>>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