The PNR Datatype

About sixty years ago, American Airlines, in conjunction with IBM, revolutionized an industry by introducing the first computer-based inventory management system called SABRE.  This was revolutionary in many ways. Not only did it change the way the travel industry booked plane tickets and hotel rooms, but it also gave the public their first view of compuers in practical use. Culturally, this introduced the concept of a confirmation code. SABRE, like other systems, uses a six character code. This is frequently called a PNR or a record locator code. Next time you get a boarding pass for your flight, find your PNR. It’s not too hard to spot.

SABRE has evolved over the years, but the PNR code has persisted. Imagine making a flight reservation and having them give you a confirmation number that more closely resembled your bank account number. Yuck.

The more I think about this, the more fascinated I am with using a six character code as a data type.  In a modern relational database system, it may be completely impractical. But think back several decades. It was revolutionary.

Imagine using all 26 letters in the English alphabet plus 8 numbers. We don’t want to use 0 and 1 because they can be confused with O and I, respectively.  That’s 34^6 or 1,544,804,416 records that can be held before needing to recycle. Even by today’s standards, it’s quite a lot of records.

Using this datatype today could be a bit messy. Because we don’t store things on sequential punch cards, we’d need to have an algorithm to find the next available value. And putting a clustered index on this as a column would cause page splits for days.

I’ve been chewing on this idea for close to a year now, and I still can’t decide if I love it or hate it more.

One Response to “ “The PNR Datatype”

  1. Rob Volk says:

    I know this is over a year old, but you don’t need anything fancy to generate the PNR. Store it as a regular integer and do the conversion with the following:

    DECLARE @PNRvalue INT=POWER(34,6)-2;
    SELECT
    SUBSTRING(’23456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’, @PNRvalue / POWER(34,5) % 34 + 1, 1) +
    SUBSTRING(’23456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’, @PNRvalue / POWER(34,4) % 34 + 1, 1) +
    SUBSTRING(’23456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’, @PNRvalue / POWER(34,3) % 34 + 1, 1) +
    SUBSTRING(’23456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’, @PNRvalue / POWER(34,2) % 34 + 1, 1) +
    SUBSTRING(’23456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’, @PNRvalue / POWER(34,1) % 34 + 1, 1) +
    SUBSTRING(’23456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’, @PNRvalue / POWER(34,0) % 34 + 1, 1)

    Instead of a variable, PNRValue can be a regular identity or a sequence in SQL 2012, which is handy because you can have the sequence restart automatically. With a small modification that code can be used as a computed column.

    Big big thanks to Itzik Ben-Gan’s article in SQL Server Mag July 2005 for helping me figure this out. Total genius!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>