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

Keith,
Both primary and foreign keys can have multiple columns. ie:
FK (gadget): [type],[id]
PS: I donit know what gadget,gidget,gizmo really mean (well maybe just gadgets, seen a lot and never understood - I simply assumed they were unrelated structured tables).
Cetin