Microsoft® SQL Server® 2012 Bible

156

Part II: Building Databases and Working with Data

One-to-One Pattern

At the conceptual diagram layer, one-to-one relationships are quite rare. Typically, one-

to-one relationships are used in the SQL physical layer to partition the data for some

performance or security reason.

One-to-one relationships connect two entities with primary keys at both entities. Because a

primary key must be unique, each side of the relationship is restricted to one tuple.

For example, a Contact entity can store general information about various contacts at a

company. However, additional employee information may be stored in a separate entity,

as shown in Figure 7-5. Although security can be applied on a per-attribute basis, or a view

can project selected attributes, many organizations choose to model sensitive information

as two one-to-one entities.

FIGURE 7-5

This one-to-one relationship partitions contact data, segmenting additional employee infor-

mation into a separate entity.

Contact

Primary Key: ContactID

Gustavo Achong

Catherine Abel

Primary Key: EmployeeID Employee Information

Gustavo Achong Employee Stuff

Catherine Abel Employee Stuff

Frances Adams

Margaret Smith

Employee

Many-to-Many Pattern

In a many-to-many relationship, both sides may relate to multiple tuples (rows) on the

other side of the relationship. The many-to-many relationship is common in reality, as

shown in the following examples:

■ (^) The classic example is members and groups. A member may belong to multiple
groups, and a group may have multiple members.
■ (^) In a typical sales system, an order may contain multiple products, and each product
may be sold on multiple orders.
■ (^) In the AdventureWorks2012 sample database, a product may qualify for several
special offers, and each special offer may have several qualifi ed products.
In a conceptual diagram, the many-to-many relationship can be diagramed by signifying
multiple cardinality at each side of the relationship, as shown in Figure 7-6.
c07.indd 156c07.indd 156 7/30/2012 4:18:13 PM7/30/2012 4:18:13 PM
http://www.it-ebooks.info