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

PRODUCT_NBR CHAR(6) PLANNING

PRODUCT_CD CHAR(2) MANUFACTURING

MANU_ITEM_NBR CHAR(8)

STOCK_UNIT_CD CHAR(10) WAREHOUSING

PRODUCT_NBR CHAR(12) MARKETING

In addition, each department's definition of a product may differ. What the
planning department defines as a product may be unbundled into multiple
products or bundled with other products by the marketing department to
maximize the number of market segments in which it can be sold. The plan-
ning department's product entity may be more of a product line, while the
marketing department's product is a salable good or service. Therefore, the
product entity is defined at different levels in different departments.

For this situation to occur, data models for each functional area must be
developed independently, otherwise the discrepancies in entity definitions
and identifiers should have been uncovered and resolved by the data admin-
istrator. However, the data administrator faces a monumental task when
establishing a shared data environment. The administrator must consolidate
the function-specific data models into an integrated enterprise data model
and document all discrepancy resolutions in the repository. The results
must then be socialized to all affected departments so that they can begin to
use common names and entity identifiers to refer to common concepts.

It is impossible to enforce organizationwide uniqueness when each
department operates independently when choosing an entity identifier.
Allowing end users to access and understand the business information
across departments is similarly impossible and prohibits any meaningful
cross-departmental or historical tracking. Nor can the entity identifier be
considered stable when it takes on different physical representations, val-
ues, and meanings that are department-specific. As a result, the administra-
tor cannot establish or maintain cross-departmental referential integrity.

SOLUTIONS

When the entity identifier problems described here exist, there are sev-
eral solutions that can be implemented to help correct these problem situ-
ations. The most common solutions include the use of surrogate keys, time
stamps, surrogate key components, substitute keys, redundant business
information, and application workarounds.

Surrogate Keys

Surrogate keys (i.e., artificial, meaningless arbitrary identifiers) are
often proposed as an alternative when problems with entity identifiers
occur. But which factors should the data modeler and data base designer