Re: Surrogate Key vs Production Key

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. 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.

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.

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").

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

  ed Received on Fri Oct 22 2004 - 13:12:51 CDT