Re: What is a surrogate identifier

On 2007-03-16, Marshall <marshall.spight_at_gmail.com> wrote:

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

I would accept that as a reasonable working definition, in practise if you let a surrogate key out of your control it's quite likely that your users will assign some sort of meaning to it which will then have to be factored into future designs.

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

Yes, it's a mess. But databases are descriptive rather than prescriptive. It would be nice to assign a nice simple identifier we don't really have the authority to do that, so we cope with what we get handed. Always remember that if databases were that easy you would probably be out of a job.

-- 
bap_at_shrdlu.com
In search of cognoscenti