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

inserting a second. This horizontal assignment ensures a uniform distribu-
tion of rows across partitions and eliminates potential locking problems.

Time Stamps

If entity identifier problems prohibit the use of the original entity identi-
fier and the surrogate key is not a viable alternative, other options do exist.
A time stamp can replace the entity identifier or an entity identifier compo-
nent. Except in the case of batched arrival, uniqueness is ensured. A time
stamp is also meaningful if the arrival sequence is important to the entity.
An example of an ATM transaction follows:

ACCOUNT_NBR DEC(11)

ATM_NBR DEC(7)

TRANS_TS TIMESTAMP

A customer issues a transaction, which is represented by a transaction
code, against the bank account with the use of a specific ATM. Because a sin-
gle customer can do this multiple times in the course of one day, one trans-
action is distinguished from another through the addition of a time stamp,
which records the precise time in which the transaction was executed.

A time stamp, however, is 26 bytes in length, which increases the size of
the entity identifier and raises the issue of all the DASD, performance, and
index limits that are included in the discussion on surrogate keys. The
time stamp represents adding another column to the entity identifier so
that the multicolumn entity identifier considerations also apply, However,
this does not resolve the problems associated with externally controlled
business information because the only controllable part of the entity iden-
tifier is the time stamp. In addition, clustering and partitioning indexes
should not be defined on an entity identifier that consists of a time stamp
alone. Locking problems could result because DB2 can attempt to add all
inserts to the end of the table or partition, and this may cause contention
for that physical space.

Surrogate Key Components

Although a surrogate key may not work as the entity identifier, it may be
suitable as an entity identifier component. If the entity identifier data ele-
ments are not unique, the addition of a surrogate key in the form of a sequen-
tial counter may ensure uniqueness. This is commonly done for identifying
invoice or order line items, as illustrated in the following configuration:

ORDER_NBR DEC(11)

LINE_NBR DEC(3)