>>>>>>>>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.
>
>I don't know. If they're so irregular tables then also there is no sense to join them to a single table at the same time. ie: GadgetDescription field would be null for gidgets and gizmos.
>
>Probably new features would come to the rescue, like cross apply, partioning (if enterprise), CLR procedure/function/type, pivoting property-value pairs etc.
>
>Thinking it might be a molecules table with N binding positions to M different molecules and T-SQLs ability to deal with data that's not 'pivoted' I opt to use that model. Otherwise it'd be ugly having BindingNo1ID, BindingNo2ID, ... BindingNoNId (and almost impossible to create the full binding schema).
>Cetin

Cetin,

You couldn't join them separately either. E.g.

select * from endPoint e inner join Gadgets g where g.GadgetID = e.endPointID and g.endPointType = 'Gadget'

might fail if the domains of GidgetID, GizmoID, and GadgetID are not identical. New features might alleviate the technical problems, but it is still an incorrect design that is tied to implementation and not to set theory.

You don't need BindingNo1ID...nID in the design. If you use xref tables the joins will take care of themselves.