>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