155
Chapter 7: Relational Database Design and Creating the Physical Database Schema
7
When the entities — nouns and verbs — are organized, the next step is to determine the
relationships among the objects. Each relationship connects two entities using their pri-
mary and foreign keys.Clients or business analysts should describe the common relationships between the objects
using terms such as includes, has, or contains. For example, a customer may place (has)
many orders. An order may include (contains) many items. An item may be on many orders.Based on these relationship descriptions, you can choose the best data design pattern.One-to-Many Pattern
By far the most common relationship is a one-to-many relationship; this is the classic par-
ent-child relationship. Several tuples (rows) in the secondary entity relate to a single tuple
in the primary entity. The relationship is between the primary entity’s primary key and the
secondary entity’s foreign key, as illustrated in the following examples:■ In the AdventureWorks2012 sample database, each productsubcategory may con-
tain several products. Each product belongs to only one productsubcategory, so the
relationship is modeled as one productsubcategory relating to multiple products.
The relationship is made between the ProductSubCategories primary key and the
Products entity’s ProductSubcategoryID foreign key, as diagrammed in Figure
7-4. Each Product’s foreign key attribute contains a copy of its SubCategories’s
primary key.■ (^) Each customer may place multiple orders. Although each order has its own unique
SalesOrderID primary key, the SalesOrder Header entity also has a foreign key
attribute that contains the CustomerID of the customer who placed the order.
The SalesOrderHeader entity may have several tuples with the same CustomerID
that defi nes the relationship as one-to-many.
FIGURE 7-4
The one-to-many relationship relates zero to many tuples (rows) in the secondary entity to a
single tuple in the primary entity.
ProductSubcategory
ProductSubcategoryID
ProductSubcategoryID
ProductSubcategoryID
Primary Key: ProductSubCategory Primary Key: ProductSubCategory
Road Frames
Road Frames
Road Frames
Helmets
Helmets
HL Road Frame-Black, 58
HL Road Frame-Red, 58
Sport-100 Helmet, Red
Product
Product
PK ProductID
PK
FK1
c07.indd 155c07.indd 155 7/30/2012 4:18:12 PM7/30/2012 4:18:12 PM
http://www.it-ebooks.info