Re: What is a surrogate identifier

On Mar 16, 6:38 am, "Cimode" <cim..._at_hotmail.com> wrote:
> On Mar 16, 3:32 pm, "JOG" <j..._at_cs.nott.ac.uk> wrote:
>
> > I would also add in hindsight that the distinction between something
> > that identifies a proposition and something that identifies the
> > entities about which those propositions speak is an extremely
> > important distinction to make. I think this often gets confused,
> > generating confusion and miscommunication. Indeed I imagine this is
> > the very reason why Codd chose the term 'key' instead of 'identifier'
> > when it came to defining attributes which make a certain proposition
> > unique.
> >From Date's relational dictionnary
>
> surrogate key
>
> A single-attribute key with the property that its values serve
> solely as surrogates-hence the name-for the entities they stand for
> (in other words, they serve merely to represent the fact that the
> corresponding entities exist, and they carry absolutely no additional
> information or meaning).

Nice.

An example that I keep coming back to is Addresses. In more complex systems, there is a need for Addresses : Customers being many : one. (Billing address, shipping address, etc. etc.)

So, just considering a very simple version of an address:

addr1
addr2
city
state/province
postal code

Is there *anything* like a key in there? Nope. The whole bundle together is the key. But if you did that, it means you're putting all five attributes in any other table that needs to refer to an address. This is theoretically sound but painful in practical terms. So people create a numeric id. Ideally the mapping is is a bijection, but that doesn't tend to happen; it's id -> address only. One time I once counted a customer who the same address in his associated address appearing 76 times! I mean character-for-character identical!

Anyway, this seems like a mess to me. Anyone have any comments?

Marshall Received on Fri Mar 16 2007 - 16:48:47 CET