160
Part II: Building Databases and Working with Data
Although you can use separate entities for customers and vendors, an alternative design
is to use a single Contact entity (the supertype) to hold every contact, regardless of
their type, and the attributes common to every type (probably just the name and contact
attributes). Separate entities (the subtypes) hold the attributes unique to customers and
vendors. A customer would have a tuple (row) in the contact and the customer entities. A
vendor would have tuples in both the contact and vendor entities. All three entities share
the same primary key (refer to Figure 7-9).Sometime data modelers who use the supertype/subtype pattern add a type attribute in
the supertype entity, so it’s easy to quickly determine the type by searching the subtypes.
This works well but it restricts the tuples to a single subtype.Without the type attribute, you can allow tuples to belong to multiple subtypes.
Sometimes, this is referred to as allowing the supertype to have multiple roles. In the con-
tact example, multiple roles (for example, a contact who is both an employee and customer)
could mean the tuple has data in the supertype entity (for example, contact entity) and
each role subtype entity (for example, employee and customer entities).Domain Integrity Lookup Pattern
The domain integrity lookup pattern, informally called the lookup table pattern, is common
in production databases. This pattern serves to only limit the valid options for an attribute,
as illustrated in Figure 7-10.FIGURE 7-10
The domain integrity lookup pattern uses a foreign key to ensure that only valid data is
entered into the attribute.Primary Key: RegionIDPrimary Key: ContactIDRegion DescriptionNorth CarolinaNew YorkColoradoForeign Key: RegionIDContactRegionEarnest Baked GoodNulls-R-UsFrank’s General StoreJohn NCNCNCNYNYCOCOCOPaulc07.indd 160c07.indd 160 7/30/2012 4:18:14 PM7/30/2012 4:18:14 PM
http://www.it-ebooks.info