Microsoft® SQL Server® 2012 Bible

162

Part II: Building Databases and Working with Data

FIGURE 7-12

The conceptual diagram of a many-to-many recursive relationship shows multiple cardinality

at each end of the relationship.

Part

An associative entity is required to resolve the many-to-many relationship between the

component parts being used and the part being assembled. Figure 7-13 illustrates the BoM

(bill of materials) associative entity that has two foreign keys that both point to the Part

entity. The fi rst foreign key points to the part being built. The second foreign key points to

the source parts.

Part A is constructed from two parts (a Thing1 and a bolt) and is used in the assembly of

two parts (Widget and SuperWidget).

FIGURE 7-13

The physical implementation of the many-to-many refl exive relationship must include an

associative entity to resolve the many-to-many relationship, just like the many-to-many two-

entity relationship.

Part

Part B

Part B

Widget

Widget

Primary Key: ContactID

ForeignKey: AssemblyID ForeignKey: ComponentID

Super Widget

Super Widget

Part A

Part A

Thing 1

Thing 1

Bolt

Bolt

Part C

Part C

Part B

Part A

Thing 1

Part C

Widget

Widget

Super Widget

Super Widget

Part A

Part A

Part A

BoM

c07.indd 162c07.indd 162 7/30/2012 4:18:15 PM7/30/2012 4:18:15 PM

http://www.it-ebooks.info