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

enation of the columns that contain the atomic data elements may not
return any matching rows for the row that contains the null-valued column.
If the procedure requires sorting, ordering, grouping, or range checking,
a denormalized entity identifier may be a problem. It is impossible to define
a substring of the group data element as the basis for a sort or order by,
either alone or in conjunction with, another data element. Similarly, it is
impossible to range check or group business information through a sub-
string of the group data element.
The administrator must ensure that the physical domain of the foreign
key matches that of the entity identifier to maintain referential integrity
and enforce logical domains. If one application uses atomic data elements
as an entity identifier and a subsequent application uses a group data ele-
ment, referential integrity and logical domains must be enforced. This must
occur through the application logic rather than the DBMS because of the
difference in the physical domains of the primary and foreign keys. In addi-
tion, the string operators and data conversions that are required by the use
of denormalized entity identifiers often preclude the use of an index to
retrieve business information and delay the retrieval process stage that
evaluates the selection criteria in the predicate.
If denormalized entity identifiers are discovered in data modeling, cor-
rections can be made if the participating business groups are open-minded
and committed to the data sharing process. To prevent denormalized
entity identifiers, the business information must be understood by either
the data modeler or someone involved in the data modeling process. Once
the business information has been modeled, all entity identifiers should be
carefully examined, especially those that may not be randomly assigned.
The most knowledgeable members of the participating groups must be
questioned as to their meaning and composition. AU identifiers should be
assumed to be group data elements unless proven otherwise. If a denor-
malized entity identifier is found, the group data element should be decom-
posed into its component atomic data elements. AU non-key data elements
must also be checked for violations of second normal form and entities and
relationships should be created as necessary if any violations are found.
It is more difficult to correct denormalized entity identifiers if they are
discovered after implementation. The entity identifier should be normal-
ized and the data model and physical data base structures should be mod-
ified to reflect the normalization results. This usually means that the
administrator must redefine existing tables, redefine indexes, and define
new tables for the new entities that have been uncovered. It may also
involve extensive programming changes. Depending on the extent of these
changes, this alternative may not be feasible even though it is best for the
organization if the application database structures are the cornerstone for
future systems development.