THE DATA ADMINISTRATION NEWSLETTER – TDAN.com
ROBERT S. SEINER – PUBLISHER

A rose by any other name would be a pain to find in the system’s data dictionary. But even worse is when the various databases and other data sources don’t quite agree on exactly what a rose is, even when they use the same name.

As an example of what I mean, consider a data warehousing project for a major airlines that was attempted a decade or two ago (yes, I am being deliberately vague). It was a classic data warehouse situation. Disjointed, uncoordinated silos of data had been created all over the company, and it was time to try to bring them together into a data warehouse that would give a consistent, single view of the enterprise. Good people were trying to do the right thing.

Since this was an airline, the two most important things they deal with are passengers and freight. Freight was a secondary business and largely handled by outside companies. About all the company did with freight was to announce that it had a certain number of cubic meters and a total weight available for use on a given flight. The nice thing about freight is that it comes in a container. It stays there and does not talk; it does not worry about seating arrangements, meals, drinks or upgrades.

A stewardess friend of mine in the 1970s remarked that she wanted to have Gidney and Cloyd’s Scrooch Gun from the ROCKY & BULLWINKLE cartoon show. This was a ray gun that would turn people into statues for a fixed amount of time. The duration she had in mind was one plane trip. Turning passengers into cargo would have made life so much easier.

You would assume that an airline would have a definition of a passenger. Wrong! They had about fifteen definitions. Each definition leads to different results in various reports and differences in how customer service is handled. Just run through a few cases that are fairly common and relatively simple:

1. Mr. A buys a ticket in advance, shows up at the airport and sits in a seat on the plane and has no other demands. Hopefully, this is the most common situation.
   
   
2. Mr. A buys a ticket in advance, shows up at the airport and misses his flight. If he reschedules another flight, is he still the same passenger? If he just goes home was he a passenger or not.
   
   
3. The XYZ company pays for (n) seats in advance, but does not have names associated with the seats. They are part of a trip to Hawaii for the best salesmen of the year, and the year is not over yet.
   
   
4. An airline employee is getting a free ride. Is this person a passenger if this is a company-related trip to get them to another airport? Is the person a passenger if this is a personal standby trip? Is this employee a passenger if they sit in a jump seat?
   
   
5. Is a baby in arms, who did not buy a ticket, a passenger? Many years ago, I was at a baggage carousel when some empty baby safety seats came onto the carousel. It was too good a straight line, so I had to horrify the little girl standing next to me with the declaration that “I just hate when parents don’t strap their kids in tight when they check them as baggage! They always fall out in the airplane!”
   
   
6. Are non-humans passengers? Is a seeing eye dog, who did not buy a ticket, a passenger? Does he get a seat or not? If not, you will need to put him in a row with extra leg room. What about other service animals today? What about Yo Yo Ma’s Cello, which travels with him in first class?
   
   
7. Food service does not have a “passenger concept” in their data model. They have a certain number of meals of particular types that belong to a flight. If the meal is one of the special types (Kosher, diabetic, peanut-free, etc.), do they worry about getting a particular meal to a particular seat (or a particular person, if they change seats)? If one of several options is offered as a “generic meal” (Would you like the rubbery chicken or leathery beef?), then each person picks on the fly (sorry for the pun), and they have no idea who ate what.
   
   
8. Marketing has weird ideas about passengers. There are no individual passengers in their world; there are only members of demographics. They do not sell seats but generate business for the company as a whole. A passenger is the target of a promo, not a seat on plane. Joe Celko, the international flight passenger, might get counted as one passenger in one category; Joe Celko, the Las Vegas Geek Show flight passenger, is a totally different passenger in a second category; Joe Celko, the “Disneyland Uncle” who buys tickets for his godchildren, is a third passenger in their scheme. They all get emails; there is no “take your godchildren to Bulgaria for a data modeling conference” email.
   
   
9. Mr. A buys a ticket with two legs to it. Is he one passenger or two passengers? The answer is actually “yes,” depending on which system you were in. This is a real mess when you have a single person book a flight with (n) legs, and they skip (k: k less than n) legs of the booking; the situation where (k = n) was much easier to model.
   
   
10. It gets worse from here.

Suddenly, there was a revelation. A person and a “passenger” are almost – but not quite – the same thing! The example I used was ISO sex codes. When you start a project, you think that gender comes in {male = 1, female = 2} as the only options. Since you have not done business with a multi-sexual Martian, you think you are done. Then you hire Alex Mack (note for geeks: Google “The Secret World of Alex Mack” and the ambiguous name) and suddenly you need {0 = unknown, 9 = lawful person} to handle ignorance and corporations.

Some data elements are simple because they are well defined. They are industry standards with narrow scope and a committee behind them. But the really basic data elements are harder to define because they are so basic. Is a rose not always a rose?