Avoiding Pitfalls in Data Modeling - High-Performance Web Databases: Design, Development, and Deployment - page 290

Exhibit 18-17. Associative entity created to keep information about rela-
tionship.

associative. The time aspect is implied and is often made part of the key to
make the associative entity occurrence unique. Associative entities are
often used to record information about a time-related event (e.g., ship-
ment, assignment, and flight). Last, an associative entity may not have
attributes of its own when it is created only for resolving a many-to-many
relationship. Otherwise, there should always be attributes.

Associative entities are common in data modeling. In many situations,
however, they create confusion for modelers and users alike. The most
common pitfall is the incorrect use of cardinality for the parent entities of
the associative entity. Another problem is the use of cardinality solely for
resolving many-to-many relationships.

Recursive Relationship

A recursive relationship depicts a situation in which an entity has a rela-
tionship to itself. When the relationship exists, there is no stipulation whether
the two entity occurrences are the same when they participate in the relation-
ship. More often than not, the two entity occurrences are not the same (e.g.,
in an employee-supervises-employee relationship, it is reasonable to assume
that an employee may not be allowed to manage himself or herself).

The following examples illustrate some
common types of recursive relationships. A bill-of-materials example
(recursive) typically represents a many-to-many relationship and there-
fore, an associative entity must be present in the model to resolve the rela-
tionship. Exhibit 18 is a sample of a typical structure of bill-of-materials sit-
uations in which a part consists of part of a part.

Recursive Relationship Patterns.

Exhibit 19a shows the construction of a one-to-many scenario. The
structure is one-to-many because an employee can report only to one