Troye Kauffman (AEZTROY@UICVMC.BITNET)
Fri, 4 Jun 1993 11:17:59 CDT
From: Troye Kauffman Bitnet: AEZTROY@UICVMC
(217) 244-6322 Internet: firstname.lastname@example.org
From a database design viewpoint, a unique identifier that has no other
meaning than to identify an entity is usually the best choice. There are
some major problems with using entity values as keys (such as SSN's or
names), and using derived keys (such as an SKU).
1. If there are duplicates (yes, there are duplicate SSN's), it is difficult
to uniquely identify an entity. An example of this in the scouting world
would be a new version of a manual replacing an old one. The name of the
old one might be the same as the old one, however for inventory and ordering
purposes, it is necessary to distinguish between the two.
2. If a field or attribute used as a key needs to be renamed, it may cause
a problem to find and change all of the entities which store the key. In
fact, some DB's won't allow changing a key value - you must delete the old
row and re-add it. This can cascade to other deletes, necessitating other
re-adds (a real mess!) For this reason, our DB shop does not use entity
values as keys.
This problem also exists with derived keys - if any of the fields that the
keys are derived from are changed, then the key must also be changed which
begets the described key change problem.
3. Value-based keys are often much longer than a key used only to uniquely
identify an entity. At first blink, one might be tempted to say "yes, but
the key value has to be stored anyway, so adding a unique key will only
add the amount of data stored, thus wasting space." The answer to this is
that keys are not stored once, but twice if they are put into an index.
The difference between storing a 20 byte key and an 8 byte key in an index
makes up for the added space that the 8 byte key would take up in a row.
Terry Howerton Sakima Group, Inc. SCOUTER Magazine Kansas City