Re: Surrogate Key vs Production Key

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

Those are interface questions, not really DB questions.

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.

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?

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.

(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??) Received on Wed Oct 13 2004 - 13:47:12 CDT