The Politics of Entity Identifiers - High-Performance Web Databases: Design, Development, and Deployment - page 302

ENTITY IDENTIFIER PROBLEMS

Because the data elements that aspire to be entity identifiers must
adhere to an extensive list of qualifications, many problems may occur dur-
ing this process. These problems may result in volatile, denormalized, all-
purpose, or nonuniversal or parochial entity identifiers.

Volatile Entity Identifiers

A volatile entity identifier is one whose value is subject to change,
become null, or that comprises potentially non-key data elements. An
example of a volatile entity identifiers is an addressee on an electronic mail
system. Addressees can both send and receive electronic mail messages.
The entity identifier comprises three data elements-the addressee's last
name, first name, and middle name. This identifier reads as:

LAST_NM CHAR (15)

FIRST_NM CHAR (15)

MIDDLE_NM CHAR (15)

Assuming that these three data elements guarantee uniqueness (which
is possible, but highly unlikely), if any part of an addressee's name
changes, a duplicate name may exist. For example, a woman marries and
takes her husband's name. Either her husband's surname replaces her last
name or it is appended to her name with a hyphen. The middle name may
also be affected because she may take her maiden name as her middle
name. This situation may compromise the entity identifier's uniqueness
and stability. In addition, if the entity identifier becomes null, its consis-
tency and explicitness are in jeopardy because a null does not signify a
valid value.

Non-key entity identifier data elements affect the identifier's stability,
consistency, and explicitness. The data administrator must update the
data model and repository entries to reflect the data element's removal
from the entity identifier. In addition, volatile entity identifiers may affect
physical data base design and application programming by creating prob-
lems with the maintenance of referential integrity. If an entity has a relation-
ship with other entities and the entity identifier of one occurrence of the
entity changes, then the foreign key of all corresponding occurrences in
the related entities must be changed to maintain referential integrity.

Joins and such operations as data element concatenation are also
impacted by nulls. For joins, when null values are used, the results are
unpredictable depending on whether the chosen relational DBMS treats all
null values as equal or as distinct. It is questionable whether a query that
joins two tables can return any matching rows at all or whether it may erro-
neously pair rows that are truly unrelated. When dealing with nullable