Re: What is a surrogate identifier

On Mar 16, 9:03 am, "Roy Hann" <specia..._at_processed.almost.meat> wrote:
> "Marshall" <marshall.spi..._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.

Hmm, well, you didn't address the issue I was talking about.

And I think I pretty well disagree with every sentence above:

> I think you already blew it.

Did not. :-)

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

I can think of plenty of them. Laws vary from locale to locale, and writing code for that often requires dividing things up by state or some other level. Or consider even a very simple application: printing a label. That needs to know which fields are which. Sure you could parse it every time but that's error prone and more complicated than different fields.

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

In a simple many-to-many table with two foreign keys, the same argument applies.

> You have invited us to
> discern spurious internal structure in what should be an atom.

The structure is very real. Cities and States are unquestionably real-world constructs, as are streets. I have modeled those things in a very uncontroversial way; Aunt Mildred will have no trouble explaining to you what those fields each mean.

And what's your alternative? A string? That means pretty much every time you actually use an address you're going to have to try some error-prone technique to parse it.

Marshall Received on Fri Mar 16 2007 - 19:10:50 CET