Microsoft® SQL Server® 2012 Bible

153

Chapter 7: Relational Database Design and Creating the Physical Database Schema

7

TABLE 7-2 Common Relationship Cardinalities

Relationship Type First Entity’s Key Second Entity’s Key

One-to-one Primary entity–primary key–single

tuple

Primary entity–primary key–single

tuple

One-to-many Primary entity–primary key–single

tuple

Secondary entity–foreign key–

multiple tuples

Many-to-many Multiple tuples Multiple tuples

Optionality

The second property of the relationship is its optionality. The difference between an optional

relationship and a mandatory relationship is critical to the data integrity of the database.

Some relationships are mandatory, or strong. These secondary tuples (rows) require that the for-

eign key point to a primary key. The secondary tuple would be incomplete or meaningless with-

out the primary entity. For the following examples, it’s critical that the relationship be enforced:

■ (^) An order-line item without an order is meaningless.
■ (^) An order without a customer is invalid.
In the AdventureWorks2012 sample database, a salesorderdetail without an associated
product is a useless detail. Conversely, some relationships are optional, or weak. The second-
ary tuple can stand alone without the primary tuple. The object in reality that is represented
by the secondary tuple would exist with or without the primary tuple. For example:
■ (^) A customer is valid with or without a discount code.
■ (^) In the AdventureWorks2012 sample database, an order may or may not have a
sales person. Whether or not the order points to a valid tuple in the sales person
entity, it’s still a valid order.
Some database developers prefer to avoid optional relationships, so they design all relation-
ships as mandatory, and point tuples that wouldn’t need a foreign key value to a surrogate
tuple in the primary table. For example, rather than allow nulls in the discount attribute
for customers without discounts, a “no discount” tuple is inserted into the discount
entity, and every customer without a discount points to that tuple.
There are two reasons to avoid surrogate null tuples (pointing to a “no discount” tuple):
The design adds work when work isn’t required (additional inserts and foreign key checks),
and it’s easier to locate a tuple without the relationship by selecting where column is
not null. The null value is a standard and useful design element. Ignoring the benefi ts of
nullability creates additional work for both the developer and the database.
From a purist’s point of view, a benefi t of using the surrogate null tuple is that the “no dis-
count” is explicit and a null value can then actually mean unknown or missing, rather than
“no discount.”
c07.indd 153c07.indd 153 7/30/2012 4:18:12 PM7/30/2012 4:18:12 PM
http://www.it-ebooks.info