Information générale
Forum:
Microsoft SQL Server
Catégorie:
Conception bases de données
Versions des environnements
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.
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement