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

entity identifiers it may not be possible to define a unique index on the
entity identifier, depending on how the chosen DBMS handles the nulls. If
one entity identifier data element is null (e.g., when someone does not have
a middle name), a null column's concatenation with a nonnull column
results in a null value unless the VALUE function is used as shown in the fol-
lowing WHERE clause:

WHERE ADDRESSEE NM =
FIRST_NM VALUE (MIDDLE_NM, '') LAST_NM

When business information is ordered, nulls either sort to the end or the
beginning 4 the output list, depending on the chosen relational DBMS.

Denormalized Entity Identifiers

Another entity identifier problem occurs during the concatenation of
multiple data elements into one denormalized entity identifier data ele-
ment. Any entity identifier that is not randomly assigned has meaning built
into its various substrings (e.g., for geographic location or type of account)
and contains hidden denormalization. If any part of the entity identifier
data element can potentially change or become null, it is much more diffi-
cult to deal with than if the data elements remain separate. For example, in
the account number for a home telephone, the first three characters are
the area code that identifies the state or geographic division where the
phone is located. The second three characters identify the local exchange
or geographic location within the area code. The next four characters are
the number for the line and the final three characters contain a customer
code that makes the account number unique in case the phone number is
reassigned to a different customer. Here, the entity identifier customer
account number actually comprises four atomic data elements, as follows:

Denormalized Entity Identifier:
Normalized Entity Identifier:
CUST_ACCT_NBR CHAR(13) AREA_CD CHAR(3)

LCL_EXCH_CD CHAR(3)

LINE_NBR CHAR(4)

CUST_CD CHAR(3)

Denormalized entity identifiers may have adverse effects on the accu-
racy of the data model. Because the entity identifier's component atomic
data elements are not identified, undiscovered violations of a second nor-
mal form may exist. Any partial key dependencies that are left denormal-
ized may introduce redundant business information into the data base
structures that are designed from the data model.