Re: Surrogate Key vs Production Key

Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<u1xg1bi3i.fsf_at_standardandpoors.com>...
> On Wed, 13 Oct 2004, damorgan_at_x.washington.edu wrote:
> > Galen Boyer wrote:
> >
> >> On Mon, 11 Oct 2004, damorgan_at_x.washington.edu wrote:
> >>
> >>>Galen Boyer wrote:
> >>>
> >>>>What does one do when the "natural keys" are actually
> >>>>different based on the source? Maybe someone needs to define
> >>>>a "natural key". Seems to me, it is a key that some other
> >>>>system has already defined.
> >>>
> >>>The natural key is well defined by the work done by Date and
> >>>Codd. That which uniquely defines a record in one set does
> >>>not necessarily define a unique record in another set.
> >> So, what does one do when there are multiple sources of data.
> >> Suppose you are storing loans for customers who want to login
> >> to your analytic application and analyze their loans. One
> >> customer's representation of the loan is a string, one is a
> >> number and one has a two-part key. Whats the "natural key"?
> >
> > Then by definition you don't have a natural key.
>
> ? We do have multiple natural keys. I'm wondering how to solve
> that. (Well, I've solved it with a key translation set of tables
> and a surrogate key for my primary keys, but I'd like to hear the
> "natural key" proponents answers)
>
> > What is a natural key in one system may not be in another. To
> > try to treat it otherwise is tortured logic.
>
> But I'm talking about a well-known financial instrument, like a
> loan or a bond. Why is the "natural key" theory not supporting
> my needs?

When I bought my house, it turned out it and the house next door (which were built by the same speculator and bought by a brother and sister and their respective spouses) had several things confused, including which loan went with which house. I still get junk mail reflecting that, nearly a decade later. This came about in part due to the division of a tax parcel into multiple parcels, all of which have similar looking numbers like 03 and 04 and 14 buried within identical numbers.

When I built my swimming pool, there was a holdup because my house showed an incomplete previously issued permit. It turned out there was another house in another city with the same street number and name, and my postal city (which is different from where the house physically is, it's not in a city), is a substring of that other city.  Man, try to get that "natural key" corrected!

Systems include users.

jg

--
@home.com is bogus.
http://arcc.co.san-diego.ca.us/services/grantorgrantee/index_adv.asp
Rancho Cantaffordya - One suggestion for what to call a newly
developed area, as opposed to Route 56 Corridor.