Unique identifier - Key selection & design

KS-1

danorm2.gif (3831 bytes)

Selecting primary key

Will not change it's value for the life of the instance.
Eg. Lastname a poor choice since in a persons life may change (eg. married).
Value can never be null. (If you have a compound key - all key attributes cannot be null).
Avoid intelligent keys, such as classifications or locations. Often change when conditions change.
Consider creating a single-attribute key for large composite keys.
Eg. Course:Module:Class (CBC:HF100:1) with a numeric key.

Identification keys need to be easy to write, easy to read, and easy to hear. There are several rules to follow when designing keys:

Numeric keys should be used in preference to alphabetic keys
Keys must be unique, and once assigned should never change
Avoid the letters O, I , Z and V because they can be confused with 0, 1, 2 and U
Avoid letters that sound similar ie M and N or B,C,D,G,P,T, and V
Break codes of more than four numbers into chunks eg 255678996 as 2556-7899-6
Record time using 24 hour clock
Avoid using letters if you are likely to transmit codes over a touch phone
Use codes that are intuitive as possible
room C617 is obviously building C on 6th floor but room 3617 is not obviously building 3 floor 6 (could be building 36 floor 1)