118 Chapter 9 / Non-Data-Warehouse Antipatterns
into a single dimension entity. For example, Figure 9.11 collapses the Product dimension of
Figure 6.6 into a single entity.
9.5 Chapter Summary
An antipattern is a characterization of a common software flaw. The antipatterns in Table 9.1
simplify reading but compromise the ability of database structure to enforce quality. These
antipatterns are often acceptable for data warehouses, but you should avoid them otherwise.
Figure 9.11 Combined entity types: Collapsing a snowflaked dimension. It is ac-
ceptable to combine entity types for a dimension of a data warehouse.
Product
productNumber
categoryName
industryName
productName
(a) Snowflaked dimension (b) Collapsed
*
11
*
Product
productName
productNumber
Category
categoryName
Industry
industryName
dimension
Antipattern
name
Observation Exceptions Resolution Frequency
Derived data
A model has ele-

ments that are not
fundamental.
OK for critical
elements, bottle-
necks, and data
warehouses.
Rework the
model to elimi-
nate derived data.
Common
Parallel
attributes
An entity type has
groups of similar
attributes.
Often used for
data warehouses.
Abstract and fac-
tor out common-
ality.
Occasional
Parallel
relationships
Two entity types
have several simi-
lar relationships.
Can be accept-
able for a data
warehouse.
Abstract and fac-

tor out common-
ality.
Occasional
Combined
entity types
An entity type has
disparate attri-
butes.
OK for I/O stag-
ing and data
warehouses.
Make each con-
cept its own
entity type.
Occasional
Table 9.1 Summary of Non-Data-Warehouse Antipatterns
119
Part III
Archetypes
Chapter 10 Archetypes 121
Archetypes are deep abstractions that often occur and transcend individual applications. You
should keep them in mind as you construct models.
This chapter presents both UML and IDEF1X diagrams so that the meaning of the ar-
chetypes is clear for readers who prefer one or the other notation.
121
10
Archetypes
Archetypes are abstractions that often occur and transcend individual applications. You
should keep them in mind as you construct models. The use of an archetype can lead to a
breakthrough. By necessity, the list in this chapter is arbitrary and incomplete. The arche-

types themselves are also incomplete so you will need to add detail as you include them.
Many of the archetypes relate to each other. This chapter uses an entity type icon
(UML—no attribute section, IDEF1X—ellipsis for attributes) for references to entity types
that are defined elsewhere in the chapter.
Throughout the chapter the shaded boxes are metadata and the white boxes are data.
10.1 Account
An Account is a label for recording, reporting, and managing a quantity of something. Ap-
plications involve a variety of account types:
• accounting applications — general ledger account, account payable, account receivable
• business applications — bank account, credit card account, customer account, expense
account, purchase account, sales account
• computing applications — computing account, email account
• travel applications — frequent flyer account, travel account
Each Account has an owner (a TangibleActor, in this case a person or organization, see Sec-
tion 10.2). Typically an owner can have multiple accounts for an account type (Figure 10.1,
Figure 10.2). Some of these accounts might be unwanted duplicates and remain undetected
until after data has been posted. The notion of AccountEquivalence can logically combine
accounts without having to move data (see the Symmetric Relationship Antipattern). In ad-
dition, since the merge is logical and not physical, it can be undone.
122 Chapter 10 / Archetypes
10.2 Actor
An Actor is someone or something that is notable in terms of data or relationships. An actor
is not the real world thing but is the computing counterpart for attaching information.
An actor provides a “hook” for interchangeable data (Figure 10.3, Figure 10.4). An Ac-
tor is a Person, Application, Organization, ActorRole, or ActorRoleType. An application in-
cludes this model and relates relevant entities. As an example, an actor can provide the basis
for assigning permissions. Sales documents may be accessible by Joe Smith (a Pers on ), the
sales department (an Organization), and the head of marketing (an ActorRoleType). Addi-
tional examples involve approval and logging. For many business processes, the efforts of
Persons, Applications, Organizations, ActorRoles, and ActorRoleTypes are interchangeable

and Actor can abstract across them.
A Person is someone who is important to an application. An Application is a reference
to software — an intelligent Application can take the place of a Person in some situations.
For example, expert system logic can determine whether to grant approval for a credit card
charge. An Organization can be a business entity, a grouping of organizations, or a part of
an organization. A TangibleActor is a Person, Application, or Organization.
An ActorRoleType is a job function that a TangibleActor can perform; examples include
clerk, manager, administrator, customer, and vendor. An ActorRole is the combination of a
TangibleActor and an ActorRoleType (see Section 10.18). Examples include John Doe the
manager and John Doe the user. You can think of an ActorRole as a TangibleActor wearing
a hat (the hat is an ActorRoleType).
Figure 10.1 Archetype Account: UML model. An Account is a label for
recording, reporting, and managing a quantity of something.
*
0 1
Account
accountNumber {unique}
accountName
AccountType
name {unique}
1
*
TangibleActor
*
creationDate
1
AccountEquivalence
owner
Figure 10.2 Archetype Account: IDEF1X model.
accountTypeID

AccountType
accountTypeName (AK1.1)
accountID
Account
accountNumber (AK1.1)
accountName
creationDate
accountTypeID (FK)
accountEquivalenceID (FK)
ownerID (FK)
accountEquivalenceID
AccountEquivalence
tangibleActorID
TangibleActor
. . .
10.2 Actor 123
Actor
name
Figure 10.3 Archetype Actor: UML model. An Actor is someone or
something that is notable in terms of data or relationships.
*
1
effectiveDate
expirationDate
*
1
OrganizationApplicationPerson
TangibleActor ActorRole ActorRoleType
tangibleActorDiscrim
actorDiscrim

Figure 10.4 Archetype Actor: IDEF1X model.
actorID
Actor
actorName
effectiveDate
expirationDate
actorDiscrim
ActorRole
actorRoleID (FK)
tangibleActorID (FK)
actorRoleTypeID (FK)
TangibleActor
tangibleActorID (FK)
tangibleActorDiscrim
ActorRoleType
actorRoleTypeID (FK)
Person
personID (FK)
Application
applicationID (FK)
Organization
organizationID (FK)