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

Cetin,

I understood that they were three different tables too - but tables that would be joined to in a query. In that case, it would not be possible to create a foreign key constraint on the child column(s) in the endPoint table that dynamically refers to one of the three tables. The design requires xref tables to handle the 'flexibility' of having zero to three foreign key references.

Also, we are operating under the assumption that the primary keys for each of the three parent tables are the same data type and length. If GadgetID is a varchar while the others are int, then we will have to write a separate query to join endPoint to Gadget in every stored procedure.

The reason that I bring this up is that I am actually working on a project that was not properly designed and I am spending a huge amount of effort writing queries to join tables where only a subset of the rows can logically be joined to another table. This presents all kinds of problems in SQL Server because even with the proper filter criteria and using derived queries, the optimizer can choose to perform the join on the entire table thus causing it to fail because of data conversion errors. The steps that are required to force SQL Server to join on a subset of the rows are very complex and are never 100% reliable.