Microsoft® SQL Server® 2012 Bible

161

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

7

The classic example is the state, or region, lookup entity. Unless the organization regularly

deals with several states as clients, the state lookup entity serves only to ensure that the

state attributes in other entities are correctly entered. Its only purpose is data consistency.

Recursive Pattern

A recursive relationship pattern (sometimes called a self-referencing, unary, or self-join rela-

tionship) is one that relates back to itself. In reality, these relationships are quite common:

■ An organizational chart represents a person reporting to another person.

■ (^) A bill of materials details how a material is constructed from other materials.
Chapter 13, “Working with Hierarchies,” deals specifi cally with modeling and querying recursive rela-
tionships within SQL Server 2012.
To use the standard organization chart as an example, each tuple in the employee entity
represents one employee. Each employee reports to a supervisor who is also listed in the
employee entity. The ReportsToID foreign key points to the supervisor’s primary key.
Because EmployeeID is a primary key and ReportsToID is a foreign key, the relationship
cardinality is one-to-many, as shown in Figure 7-11. One manager may have several direct
reports, but each employee may have only one manager.
FIGURE 7-11
The refl exive, or recursive, relationship is a one-to-many relationship between two tuples of
the same entity.
Primary Key: ContactID Foreign Key: Reports ToID