Microsoft® SQL Server® 2012 Bible

159

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

7

Supertype/Subtype Pattern

One design pattern that’s not used often enough is the supertype/subtype pattern. The

supertype/subtype pattern is also perfectly suited to modeling an object-oriented design in a

relational database. For the application this model provides advantages that may not be realized

with the traditional relational database design patters. However, because this model has the

potential to increase the number of tables by several times, writing queries for reporting pur-

poses could become an arduous task. Figure 7-9 provides a detail illustration of this method.

FIGURE 7-9

The supertype/subtype pattern uses an optional one-to-one relationship that relates a

primary key to a primary key.

Customer

Contact

Vendor

Primary Key: ContactID

Primary Key: ContactID

Primary Key: ContactID

Earnest Baked Good

Earnest Baked Good

Nulls-R-Us

Nulls-R-Us

Frank’s General Store

Frank’s General Store

Always fresh

Never know when he’ll show up

Dependable

Vendor Status

John

John Paul

Paul

10 Points

3 Points

Customer Loyalty data

The supertype/subtype relationship leverages the one-to-one relationship to connect one

supertype entity with one or more subtype entities. This extends the supertype entity with

what appears to be fl exible attributes.

The textbook example is a database that needs to store multiple types of contacts. All

contacts have basic contact data such as name, location, phone number, and so on. Some

contacts are customers with customer attributes (credit limits, loyalty programs, and so

on). Some contacts are vendors with vendor-specifi c data.

c07.indd 159c07.indd 159 7/30/2012 4:18:14 PM7/30/2012 4:18:14 PM

http://www.it-ebooks.info