I recently made a reservation on American Airlines and noticed that my record locator was a six-character alphabetic code. This automatically generated code reminded me of a funny story regarding database design from early in my career. I was working as a consultant for a large multi-national automobile company in England, which shall remain nameless to protect the innocent. We were busy developing a purchasing system that would be used by the company's buyers throughout Europe. It was quite sophisticated for the time (around 1985) and included automatic bid generation and recording using an IMS Database on the IBM MVS platform and supported five native European languages - English, Spanish, Italian, German and Flemish. It was the first project I was on that spent some serious time and effort designing the database before the application. A great foundation.

The multi-lingual aspects of the application made it most interesting, forcing us into a data driven approach where all form prompts and messages had to be stored on the database in the five languages. When a buyer would logon, they could choose the language to be presented on the application forms which would be built dynamically at runtime. The original database design stipulated that the Purchase Order number would be a 6 character alphabetic code that would be automatically generated by the system starting with AAAAAA then AAAAAB and so on. This Purchase Order number would be sent out to suppliers of the large multi-national company and would be used for tracking with the external companies. All was well and good. The project was running on time, on budget.

However, one bright spark (maybe it was me) raised the issue of potentially naughty words appearing as purchase orders that would be generated over time. Using a very British example, let's say a purchase order of ‘BLOODY' might appear eventually. (I can think of other more fruity examples but I will spare you...). Can you imagine a reputable multi-national company issuing such a purchase order to an external company? Well, we mentioned this to the Project Manager who decided that this would be unacceptable (as well as embarrassing ...). So we were asked to avoid some obvious embarrassing combinations which we could hard-code in the application. However, the application itself was entirely data-driven anyway so the decision was made to create a data store that would contain the offending words. So we would create a "Dirty Word Database."

This would work quite well, because new dirty words might become in vogue and we could quickly eliminate the possibility of them being used as purchase orders. We would have to build a user interface of course so we could record the latest dirty words in the Dirty Word Database. Hang on, this was a multi-lingual system, so we couldn't just concentrate on English dirty words, we had to also exclude rude words in Spanish, Italian, German and Flemish. But the development team was hardly multi-lingual so we had to rely on our colleagues in the other European countries to help us populate this database. We could plan a multi-lingual dirty word conference with representatives from all five countries. Maybe we would meet once a year to record the latest vulgarities to be excluded. Can you imagine being at such a meeting? Translating naughty words into five different languages. Now that sounds like fun. (It reminds me of youth hostelling across Europe when I was younger). The end deliverable would be an "International Dirty Word Database." Maybe we could resell this "intellectual capital" to other companies? As you can probably guess, after a few minutes of literally crying with laughter, our Project Manager made the unilateral decision that the Purchase Order number would now be a combination of two alphabetic characters, followed by two numbers and another two alphabetics.

Spoil sport.
I wonder what American Airlines does? Keep an eye on those record locators!
Cheers
Brian

Recent blog posts...

Sliding Doors or Sliding Windows?

Database Design – build a blueprint for your database

Intellisense in SSMS at last