Re: Surrogate Key vs Production Key

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

>> 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"?

I don't understand how you can divorce these from the attributes that must be stored for your "natural key".

> yes sometimes you use
> NAME VARCHAR2(60)
>
> othertimes it's
> FIRST varchar2(30)
> MIDDLE varchar2(30)
> LAST varchar2(35)
>
> still others use
> FNAME VARCHAR2(20)
> LNAME VARCHAR2(30)
> ALIAS VARCHAR2(55)
>
> one one of many other variations just on people's names.

So, lets assume you are accepting data feeds and loading a person table for all of us to view. If my company is sending you datafiles and we key our data by first/last names (and the horrid key is a purposeful example) while Noon's company sends you a file where his company keys their data by an internally generated sequence and my sister's company sends you data keyed by SSN, what would be the "natural key" to your "person" table?

> Note that a natural key is defined for a table, not for the
> application. Any DB has many natural keys. Why would that be a
> problem?

I'm talking about "the" natural key for a single table.

> At least with multiple natural keys I can look at a few tables
> and figure out something about the data relations. With
> surrogate keys, which are often just numeric you better have a
> documented data model defining how those tables link.

The RI will tell you this, but I use Erwin, so I have that.

> (sure there are sometimes obvious ones like ORDER_NUMBER, but
> does that relate to the PURCHSE_ORDER table, the WORK_ORDER
> table, or the SORT_ORDER table??)

-- 
Galen Boyer