Re: Natural keys vs Aritficial Keys

From: Walter Mitty <>
Date: Sun, 17 May 2009 07:54:03 GMT
Message-ID: <voPPl.1553$>

"paul c" <> wrote in message news:amIPl.28384$PH1.18664_at_edtnps82...
> Tony Toews [MVP] wrote:
> ...
>> Preumably though there are bar codes on the cargo containers and a simple
>> form where
>> the cargo master can remove selected containers from the airplane.
>> A welding shop client that empoyed hundreds of welders built and
>> assembled very
>> complex piping assemblies for refineries, power plants and oil sands
>> plants. These
>> are designed to, usually, fit on a 50' flat bed trailer. The client
>> number could be
>> in excess of 20 or 25 characters. The internal number they used went
>> from 1 to
>> whatever. It in turn was prefixed with a job number which started at 1
>> and went to
>> whatever. The system printed multiple weather proof tag with the
>> internal number as
>> well as the bar coded long client number.
>> (Occasionally they would have to rebuild a particular item. The gravel
>> pad at one
>> client where these are stored is about a mile square. Well, if the
>> plant has a
>> large expansion, and there's a lot of snow that winter, you can't find
>> the
>> assemblies. Until the expansion is finished a year or two later and
>> you're
>> looking at the excess assemblies which are laying on the gravel.. And
>> the folks at
>> the plant getting paid $25 and $30 an hour love being told to go through
>> all the
>> items on this gravel pad looking for particular assemblies. A great way
>> to spend a
>> shift rather than hauling stuff around or whatever.)
> The people who make international freight regulations remind me of the
> Canadian food inspectors from thirty years ago who assigned government
> grades for the mandated labels of canned vegetables - Canada Fancy, Choice
> or Standard. At the unloading dock, they would put a pea in their mouth
> and decide on the spot. Yanks had a gizmo called a 'tenderometer' whereas
> much in Canada depended on how hung-over the inspector was. I used to
> know a bit of what went on at the IATA so-called standards meetings and
> from their results I conclude the meetings were much similar to those of
> the SQL standards committee, basically add every arcane, legacy, idiotic
> feature that the sponsoring companies had in their products or systems..
> From what I saw fifteen or so years ago I think it was and probably still
> is generally true that the 'business experts' and other 'users' in the
> freight, customs, forwarding, warehousing and transportation businesses
> are not capable of deciding all requirements because most of them are
> stuck in the rut of their own past practices. When a designer is exposed
> to this, a moment's thought should be enough to see that the different
> parties operate from distinct concepts. Faced with this, a db designer
> must ask questions like "what is a shipment?", "what is a location?", as
> far as the system, not the application, is concerned, and these questions
> need to be asked not to the individual parties but in the context of
> everything the system must do. Some abstract key like 'shipment id' is
> sure to result, but the waybill numbers, flight segment keys, container
> ids, order ids, et cetera, et cetera must also persist.

That's data analysis. Back when I was doing data analysis, you had to ask the same question to a handful of people, and often the same question in different wordings to the same person again and again. I met someone who was better at data analysis that I was, and later found out what the user community thought of him: "He's not very bright, but at least he's thorough". They didn't realize that he was actually very bright.

Good coders making coding look easy. Good analysts don't make analysis look easy.

> If what I remember resembles real life, what does the db designer do in
> the face of enterprise experts who don't get it? There is only one way to
> do it and that is to go over the heads of the experts. That is a good way
> to risk contracts, but from what I saw the safest way to do it was start
> from the very top. However, the technical lingo doesn't get you anywhere
> in front of the board of directors. In my opinion, practical db design
> for large apps requires people who have multiple talents, enough gumption
> to make their point, but no more. An ignorant CEO might palm you off to
> numbskulls or you might get lucky and meet an underling who is actually
> running the business if not in name. Either way it buys you time to
> either find another job or another contract. Or one could just play it
> safe, go with the flow and preserve one's income indefinitely, but of
> course no such person would be posting on c.d.t!

It depends on who you mean by "the experts". Back in the day, the people I really relied on were the "subject matter experts". I'm not talking about IT experts here. I'm talking about the people who REALLY understand the data, in the context of making the data do useful work. Such people often do not know squat about database design. Why should they? The key to picking their brain is to ask them about the data, not about the logical structure of the data. Once you understand how the data really works, you can switch from analysis to design.

At design time, you need to trust what you know about logical database design (and also physical database design) and not rely on the wisdom of the subject matter experts. Some good designers switch prematurely from analysis to design, thinking they have grasped how the data really works when they have not.

Bad design can ruin good analysis, but good design can't rescue bad analysis. Analysis paralysis is a pitfall. But another pitfall is breezing through analysis with just a hazy idea of what the system goals are. And you are right that it is the system perspective, not the app perspective that matters. I like the phrase "in the context of everything the system must do." And I'm taking the system to mean a business system, that contains people doing job functions, as well as machines carrying out programmed actions. The people who understand data at a business system level are rare.

Thanks for some good points. Received on Sun May 17 2009 - 09:54:03 CEST

Original text of this message