Primary Keys and ID Codes

Clients often ask for "codes" so that they can easily identify accounts, invoices and the such. This post is about creating such codes.

The first property that codes need is that they need to be unique. They should also be unambiguous, so avoid any code that includes both zero and the letter O, the letter I and the number 1.

The story so far: unique, unambiguous.

I have one client who had "codes" for courses that were over 10 characters long which made them about as easy to work with as a software product activation key. Partly this was because they wanted the codes to contain embedded information about the course and venue. This is not necessary for the code to function: their purpose was to be typed into a web enrolment for by end users, and the usability was suffering badly because of their length. Also, they tended to be lots of 1s and 0s so reading them was difficult. So another property is that the codes should be as short as practical.

Update: unique, unambiguous, short.

So why not use a simple serial number for the code? Because if sequential serial numbers are used, a typing entry error might mean that the resulting code matches another code: the incorrectly entered code might match a different code, and create an undetected error. Thus there is an additional property for codes: the code needs to be error resilient detecting. (I'm not sure the word "resilient" is correct but I cannot think of another: what I mean is that the simple mis-typing of one code should not match another code. It might be "error detection".)

Unique, unambiguous, short, error detecting.

Fortunately, such technology already exists and is easy to implement: check digits. The last digit of your 16 character credit card number is actually a check digit for the previous 15. So changing the last number of a credit card won't create a new valid credit card number, and similarly a typo in any of the other characters has a low probability of creating another valid number.

The codes I'm creating don't need the security of a check digit, but adding an extra digit to an already unique serial number makes another uniquer code that is no longer as susceptible to typing errors because the resulting incorrectly entered code las a low probability of matching a real code.

In one client's system I've just created such codes for accounts, courses and students. These codes are the primary key (a simple serial number) plus a Luhn check digit added to the end. This creates a unique number (they were unique anyway) that is now non-sequential so simple typos don't easily match other keys. These have proven very effective as the codes that end users entered into a web enrolment form, and replaced the previously 10+ digit codes used last year. Simply typing the next number won't result in somebody else's code.

Examples:

primary key = 8098, Luhn check digit = 6, code = 80986
primary key = 8099, Luhn check digit = 4, code = 80994

As an added bonus I can look at the code and easily work out what the actual primary key value is by ignoring the last digit.

It looks as though this method will break after serial number 9999, but it doesn't. Remember that every serial number gets a check digit added, so there will never be any clashes and the method won't suddenly run out of numbers. The serial number 10000 will be come 10000X (where X is the Luhn check digit); the serial number 10000X becomes 10000XX. All will be unique.

Note that this method is used for generating a "code" field. These are fields that are displayed for the benefit of the end users and are only used on-screen or printed. This code is NEVER used for internal relationships: this is what the serial number is used for.

For variety it's possible to use the check digit to create an alpha character instead of a digit, and the new character need not be put at the end. The simplest method to convert a digit to an alpha character is to use a Choose function. The FileMaker code snippet below will do this:

Choose( LuhnCheckDigit( primarykey ) ; "K" ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "J" ; "L" )

Note that in this code I have chosen not to return the letter "i" since it will be confused with "1". Also note that the letters don't have to be sequential or in any particular order. Finally, the "L" should never be returned because the Luhn check digit function should only return values between 0 and 9 inclusive. If "L"s start appearing in codes there is some real trouble afoot.

For variety this alpha code can prefix or suffix the primary key. Prefixing the key value makes the code change significantly while scrolling through the records because the first character bounces around the alphabet.

The LuhnCheckDigit() custom function is available here:

http://www.briandunning.com/cf/707