Microsoft® SQL Server® 2012 Bible

158

Part II: Building Databases and Working with Data

Figure 7-8 shows the associative entity with data to illustrate how it has a foreign key

to each of the two many-to-many primary entities. This enables each primary entity to

assume a one-to-many relationship with the other entity.

FIGURE 7-8

In the associative entity (SpecialOfferProduct), each special offer can be represented multi-

ple times, which creates an artifi cial one-specialoffer-to-many-product relationship. Likewise,

each product can be listed multiple times in the associative entity, creating a one-product-

to-many-special relationship.

Primary Key: ProductID HL Road Frame – Black, 58 Discounted Product Primary Key: ContactID

HL Road Frame – Black, 58 Seasonal Discount

Sport-100 Helmet, Red Discounted Product

Sport-100 Helmet, Red Excess Inventory

Mountain Bike Socks, M Excess Inventory

Discounted Product

Seasonal Discount

Excess Inventory

HL Road Frame – Black, 58

Sport-100 Helmet, Red

Mountain Bike Socks, M

Foreign Key: ProductID ForeignKey: SpecialOfferID

In some cases the subject-matter experts can readily recognize the associated table:

■ (^) In the case of the many orders to many products example, the associative entity is
the order details entity.
■ (^) A class may have many students, and each student may attend many classes. The
associative entity would be recognized as the registration entity.
In other cases an organization might understand that the relationship is a many-to-many
relationship, but there’s no term to describe the relationship. In this case, the associative
entity is still required to resolve the many-to-many relationship — just don’t discuss it
with the subject-matter experts.
Typically, additional facts and attributes describe the many-to-many relationship. These
attributes belong in the associative entity. For example:
■ (^) In the case of the many orders to many products example, the associative entity
(order details entity) would include the quantity and sales price
attributes.
■ (^) In the members and groups example, the member_groups associative entity might
include the datejoined and status attributes.
When designing attributes for associative entities, it’s extremely critical that every attri-
bute actually describe only the many-to-many relationship and not one of the primary
entities. For example, including a product name describes the product entity and not the
many orders to many products relationship.
c07.indd 158c07.indd 158 7/30/2012 4:18:13 PM7/30/2012 4:18:13 PM
http://www.it-ebooks.info