Re: Modeling Address using Relational Theory

Tony Andrews wrote:
> Marshall Spight wrote:
> > Ha ha! I'm posting on this topic again. Clearly
> > I have some kind of brain disorder.
>
> If not, you soon will ;-)
>
> Dawn, it seems to me that there is something fundamental to Marshall's
> argument that you just don't "get",

I'm certain of that, but it isn't for lack of trying.

> perhaps because you are so
> ingrained with address lines from everyday use.

I'm sure I'm only interested in address lines for use, but I do want to model them in an accurate, relational way.

> There is *nothing*
> intrinsically ordered about addr1, addr2.

You are right that I just don't get that. Why do you name them with words that indicate an ordering then?

> Putting addr1 before addr2
> on an envelop is a convention, that's all.

I decided to bite the bullet and read the latest standards from usps, in spite of recalling them not to be very entertaining. The USPS domestic standards says

"If all Delivery Address Line information cannot be continued in the Delivery Address Line above the city, state, and ZIP Code, place secondary address information on the line immediately above the Delivery Address Line"

So, the apt number is put somewhere in the "street address" line or, if it doesn't fit there, on the prior line. The usps international standards say:

"LINE 1: NAME OF ADDRESSEE
LINE 2: STREET ADDRESS OR POST OFFICE BOX NUMBER LINE 3: CITY OR TOWN NAME, OTHER PRINCIPAL SUBDIVISION (ie, PROVINCE, STATE, COUNTY, ETC.) AND POSTAL CODE (IF KNOWN) (Note: in some countries, the postal code may precede the city or town name) LINE 4: COUNTRY NAME (UPPERCASE LETTERS IN ENGLISH)" However, I think that sometimes with international mail, line 2 above turns into lines 2 & 3.

I looked more closely at the ideaalliance site at

http://www.idealliance.org/adis/

and I see that they named the two lines in the middle and they do not explicitly indicate a country name line (since this is for data exchange -- if you know the country, you can put it as the last line)

Full Street Address Line
Full Delivery Service Line
Full Sectoral Address Line
Full City-State-ZIP Line

This is the best shot at two names for these lines that I've seen.

I know a lot of people have spent a lot of time trying to figure out and name addr2 & addr3 and perhaps that is, in part, because they don't like this numbering-your-attributes approach any more than I do. It seems that when all is said and done, if you can't fit the entire "street address" on one line, then you split it up and put some more specific portion of the designator on a prior line (domestic US mail) or on a subsequent line (international mail), but that same designator can be at the end of the street address line with a # before it too.

So, it still appears to me that there is just one attribute here (unless you have a ton of attributes for street number, street name, apt #, po box, etc) and sometimes you split it into two lines for representation issues. I almost like Gene's idea of a single marked-up line of text, but the up-to-two lines of text in a single attribute that I have used in the past works well too.

> The naming addr1, addr2 is
> based on that convention, of course, which perhaps adds to the
> confusion.
>
> What is addr1? Clearly it is the *most specific* part of the address,
> just as Country (if you have that) is the *least specific* part. One
> could come up with various alternative names for addr1 -
> most_speficic_addr, building_and_street (perhaps, but then again
> perhaps that isn't always an appropriate name), ...
>
> When you print an address, you follow convention and put the attributes
> in the order addr1, addr2, city, state, zip, country (or whatever).
> But you don't *have* to! In some countries, it might well be the
> convention to start with the country at the top and work down to the
> most specific address line.
>
> Also, for some reports you may want to work backwards, breaking on
> state then city then addr2 then addr1.
>
> The same applies to names. Some usages will be "Mr Fred Bloggs",
> others will be "Bloggs, Fred (Mr)" etc.
>
> So while there is a *conventional display order* for address lines,
> there is no *intrinsic* ordering.
>
> Part of the problem is that addresses are quite complicated, which
> makes coming up with good attribute names difficult. addr1 and addr2
> reflect that difficulty.

Yes indeed. That is also what makes me think that there is only one attribute for that portion of the address (unless, again, you split it out into a ton of attributes)

> I haven't said anything here that Marshall hasn't already said
> (better), I just thought I'd summarise the main points as I see them,
> and add my weight to the attempt to tip you over the edge where you can
> suddenly see what Marshall means!

You did prompt me to look up the current standards. They convinced me that there is, indeed, only one single attribute, but if it is "too long" (a representation issue?) then you split it out into multiple lines. I can see now that you could have a street address attribute and a "more specificity" attribute and sometimes paste them together on one line, sometimes put the specificity before the street & sometimes after, but I don't think that covers all of the cases either.

I'll let it drop, but I don't have a clear resolution. Thanks. --dawn Received on Mon Sep 05 2005 - 16:17:12 CEST