Re: What is a surrogate identifier

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:1174060127.110635.173510_at_e65g2000hsc.googlegroups.com...
> On Mar 16, 6:38 am, "Cimode" <cim..._at_hotmail.com> wrote:
>
> So, just considering a very simple version of an address:
>
> addr1
> addr2
> city
> state/province
> postal code

I think you already blew it. There might be an argument for isolating the postal code, but it is a very rare business problem that requires one to think about the address as N distinct dimensions. In fact, I would suggest that you have violated 1NF since almost none of those "dimensions" is meaningful except in the company of the others. You have invited us to discern spurious internal structure in what should be an atom.

> Is there *anything* like a key in there? Nope.

For the reason I note above.

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

See above. :-)

Roy Received on Fri Mar 16 2007 - 17:03:33 CET