Re: Surrogate Key vs Production Key

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 25 Oct 2004 07:58:19 -0500
Message-ID: <uzn2bvtut.fsf@standardandpoors.com>

On 22 Oct 2004, ed.prochak_at_magicinterface.com wrote:
> Galen Boyer <galenboyer_at_hotpop.com> wrote in message
> news:<uacug3pue.fsf_at_standardandpoors.com>...

>> On 21 Oct 2004, ed.prochak_at_magicinterface.com wrote:
>> > wizofoz2k_at_yahoo.com.au (Noons) wrote in message
>> >> > Why because that is not relationship between the
>> >> > Surrogate key value and the data it is supposed to
>> >> > connect. It is often just a value
>> >> 
>> >> This is where your examples and arguments are completely
>> >> wrong.  If you do not establish a referential integrity
>> >> constraint between ANY two PK and FK keys (natural or not
>> >> is COMPLETELY immaterial), then you WILL have the potential
>> >> for wrong data.
>> 
>> So, what if the multiple sources of your data have different
>> natural keys?

>
> I'm not exactly sure what you mean here. the "completely wrong"
> comment was Noons' not mine.
>
> But if that question is for me, then I'd say you have different
> entities. You no longer are talking about the same thing.

This is where I disagree. Just because different groups came up with different representations of a loan, doesn't mean a loan isn't a loan isn't a loan...

> A lot of my database work is on the conversion side, so dealing
> with multiple sources is a big part of that. Merging and
> extracting data is what you have to do sometimes to map a
> source that uses a different datamodel onto your model. That's
> exactly when Surrogate values get in the way. They contain NO
> INFORMATION of themselves.

But, they do mean something to the source system, that is for sure. Therefore, they have to mean something to you.

> Simple example. you would think getting a file of address data
> would be easy right? well in one conversion the old company
> saved space using surrogate keys to keep the streen name
> separate. so addresses were in one file with house number and
> street key, streetnames were in another file indexed by the
> key. At the time of the live conversion, somehow they failed to
> do the lookup for a large number of addresses. Whether
> malicious or accidental? who knows? Was it due to a programming
> bug on their part, corrupted data, or maybe just a mistakenly
> truncated street file? I cannot say. But we got street
> addresses that looked like: 123 ZZ123 678 ZT876 and so on.
> That was a great experience of surrogate keys, NOT.

I don't quite get how surrogate keys versus natural keys would have saved you on this. Sounds like they just plain gave you incomplete source files.

> Now based on your other post, I think you are referring to
> dealing with the data from different live sources
> (applications). During the design, with suurrogates there is a
> temptation to put unrelated things together just to make the
> application "easier". With a surrogate, it's tempting to put
> all CDs in one table when some of them are video CDs or DVDs,
> aome are Audio, and some are data file CDs. Then when someone
> gives you a "shoebox of receipts" you try to shoehorn it into
> the same table. (after all it's still just "storage media").

A loan, a bond ...? These are common financial instruments with many different representations based on different organization's views of them. The natural key theory seems to almost suggest that if one has a natural key for an entity, then, it truly is the only key. I would define the "natural key" as the key from the source system. And because of this definition, the "natural key" holds no more data integrity or stronger design than a surrogate key. And, I would further say that "natural key" proponents that almost blindly yell that they are the only keys are missing the point that most of the time, then natural key is probably flawed because of some other systems architect's flaws.

> Yes I pushed the metaphor to the limits there, but I think
> you'll see my point.

I just don't trust outside sources to define my keys.

-- 
Galen Boyer

Received on Mon Oct 25 2004 - 07:58:19 CDT