Databases Reference
In-Depth Information
consider to determine whether a surrogate key should take the role of pri-
mary identifier? Although other data elements that better fulfill the proper-
ties of an entity identifier, known as an alternate key, may exist, a surrogate
key should always be considered. Because of the rapidly changing nature
of business, any entity identifier that is not factless may sometimes
become volatile. Before the decision to use a surrogate key is made, how-
ever, a number of other factors must be weighed.
The length of the entity identifier should be considered. Long entity
identifiers have implications for direct access storage device (DASD) con-
sumption to store the indexes that support the entity identifier and its
occurrence in other tables as a foreign key. A long entity identifier also
affects the processing time for such utilities as load, backup, and recovery
and such operations as joins, scans, inserts, and deletes. A surrogate key,
however, has the advantage of being relatively short by comparison. If the
entity identifier is a composite or multicolumn key, all entity identifier col-
umns must be specified to retrieve a row or to perform a join. If the admin-
istrator creates views to define selection or join criteria or creates syn-
onyms for column concatenation, this situation may be avoided. All
columns must also maintain referential integrity. A multicolumn entity
identifier may be long, with all the concerns listed previously. A surrogate
key is usually a single data element, which simplifies SQL statement coding
and referential integrity.
Index limitations posed by the DBMS must also be considered. Limits on
the length of an index or the number of columns it can reference may pre-
clude the use of an index to enforce uniqueness. In this case, uniqueness is
enforced through application logic and a surrogate key may provide an
attractive alternative. The existence of any of the previously mentioned
factors will tip the scales in favor of using surrogate keys. The following
considerations may weight the decision in the other direction.
Installation-specific limitations also affect the decision to use surrogate
keys, particularly when coupled with a requirement for end-user access by
the original entity identifier instead of the surrogate key. The administrator
may place a limit on the number of indexes that can be defined on a table.
As more indexes are defined on a table, the amount of required DASD
increases, as does the probability of locking problems; the processing time
for inserts, updates, reorganizations, recovery, backup, load, and statis-
tics-gathering; the object administration that creates the indexes; the prob-
ability of reaching a limit on the number of open data sets; and the com-
plexity of table or partition recovery. If end users need access through the
entity identifier and there is an installation limit on the number of indexes
allowed, the use of a surrogate key may not be feasible.
The primary factor in determining whether to use a surrogate key is
which organization owns the business information and assigns the entity
Search WWH ::
Custom Search