General information
Forum:
Microsoft SQL Server
Environment versions
SQL Server:
SQL Server 2000
>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!
Mike,
Is this an exercise for a class?
You have described a classic problem in data modeling. Unfortunately, there is no single "easier" solution to the problem. There are varying solutions and each has merits. The one that I like the best is as follows.
Tables
--------------
Wire (WireID)
Gidget (GidgetID)
Gizmo (GizmoID)
Gadget (GadgetID)
WireLeftEndGidget (WireID, GidgetID)
WireLeftEndGizmo (WireID, GizmoID)
WireLeftEndGadget (WireID, GadgetID
WireRightEndGidget (WireID,GidgetID}
WireRightEndGizmo (WireID, GizmoID)
WireRightEndGadget (WireID, GedgetID)
Wire -> WireLeftEndGidet -> Gidget
Wire -> WireLeftEndGizmo -> Gizmo
Wire -> WireLeftEndGadget -> Gadget
Wire -> WireRightEndGidget -> Gidget
Wire -> WireRightEndGizmo -> Gizmo
Wire -> WireRightEndGadget -> Gadget
You will also need a table constraint in each xref table to disallow duplicates.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only