The most logical postal address layout I have seen was in West Germany
when I was posted their (NATO). It went like this:
My Name
12345 Soest
Windmuhlenweg 35
Apartment 1
The postal code and city on the first line after the name, then the
street followed by the house number, then the apartment and so forth. If
one thinks about how one uses a map to find some place, this is exactly
how we would search - the city, then the strett, then the building, then
the apartment.
The only real things in the sample are the city name, street and number,
and the apartment number. I came back to Canada in 1967 and the memory
is failing.
Albert
On 23/02/2012 8:49 AM, Wills, Steve wrote:
William, may I encourage you to take a gander at that USPS document,
http://pe.usps.gov/cpim/ftp/pubs/pub28/pub28.pdf, beginning at Section
231, as it breaks-down the “shape” of the address, specifically the
“Address”, into their discrete parts :
ØPrimary Address (aka: Line 1)
oPrimary Address Number
oPredirectional
oStreet Name
oSuffix
oPostdirectional
oExample: “123 South Main Street West” (You can see that simple
concatenation creates output for all of us users, but you might also
begin to see why a standardized record layout could be useful in some
applications: let’s say I wanted to search for ‘Main’ AND ‘Street’,
excluding any ‘circle’, ‘avenue’, ‘cove’, ‘parkway’, etc.)
ØSecondary address (aka: Line 2, all that “Suite”, “Apartment”, “Unit”
stuff.)
I have seen such a record-structure several times, often related to
government/public records, such as property identification/location
(as in titles and 911 or “who owns this address and how do I get a
fire truck to it”), so I think it’s some sort of standard.
I can’t say when this structure was created or last revised, but,
regardless of what anyone might think about the USPS, they’ve been in
the “address” business for over 200 years.
And, since I had to double-check the PDF to be sure I wasn’t telling
you a lie, I discovered that it’s actually CHOCKED FULL of
seemingly-esoteric-but-potentially-useful information about any and
everything to do with any address to which the USPS delivers. IOW,
nothing about Canada, Germany, Ghana, New Zealand, etc, but I that
other link I sent yesterday might cover that topic to the same
excruciating degree of detail!
Another $0.02,
Steve
*From:*[email protected] [mailto:[email protected]] *On Behalf Of
*William Stacy
*Sent:* Thursday, February 23, 2012 9:22 AM
*To:* RBASE-L Mailing List
*Subject:* [RBASE-L] - RE: Too relational?
I think people can not only have several postal addresses, but they
can have several telephone #s, multiple work lines, faxes, etc. even
multiple e-mails are popular. But you're right about the shape thing.
I'm thinking that the street name and the city,state zip line probably
belong in their own tables as lookups. I think Folsom CA 95630 should
only exist once in my entire database, not the thousands of times it
now does. But that brings me back to my title question of this thread...
On Thu, Feb 23, 2012 at 4:38 AM, Bill Downall
<[email protected]
<mailto:[email protected]>> wrote:
William,
This is a design dilemma. Addresses are definitely a separate table,
because a person can have multiple addresses, and because the "shape"
of the data is different, (street address, city, state, postal code,
country). But do the phones and emails and twitter accounts link back
to the people, or to the addresses? I tend to go with linking to the
people, with phone types broken down in "home phone" "mobile phone"
"work phone", etc.
Bill
On Thu, Feb 23, 2012 at 7:25 AM, William Stacy
<[email protected] <mailto:[email protected]>> wrote:
Now this is interesting. Do you include Postal contact types
the same way, in the same table? Are these 2 columns part of a
personal demographic table, or a separate table. If the
latter, how do you link them up with the personal table? TIA
On Wed, Feb 22, 2012 at 5:59 PM, Bill Downall
<[email protected]
<mailto:[email protected]>> wrote:
I cannot see the future as well as you, Mike. But my more
recent designs do not have any columns with the letters
p-h-o-n-e in a column name. There is a column for ContactType,
and another for ContactValue. I could someday add a new
contact type of ipv6, in addition to existing types of email,
mobile, work, google voice, twitterID, etc. No schema change
needed.
Bill
On Feb 22, 2012 5:46 PM, "Mike Byerley" <[email protected]
<mailto:[email protected]>> wrote:
I started using nnn.nnn.nnnn for phone numbers
anticipating at some time sub
ipv6, phones will just be IP numbers. Just a guess though.
----- Original Message -----
From: "Bill Downall" <[email protected]
<mailto:[email protected]>>
To: "RBASE-L Mailing List" <[email protected]
<mailto:[email protected]>>
Sent: Wednesday, February 22, 2012 11:26 AM
Subject: [RBASE-L] - RE: Too relational?
It's nice to see Professor Wills here! You know a topic
like this would get
him going.
Bill, in my mind, a basic reason to normalize fully is to
create a database
that is least likely to need either schema changes or awkward
exception-handling down the road.
If you do not normalize, and you provide room for 3 phone
numbers, some day
you will have to put the fourth phone number in the
comments, or change the
schema to allow for 4 phone numbers.
Schema changes are expensive, because all forms and
reports and procedures
and eeps and views and rules and triggers and applications
that relate to
that data may have to be changed, too, and cannot be done
by users through
"settings", but have to be done by programmers.
Putting the data in the "wrong" place like the comments
means people won't
find that data with a normal search or query.
There are other good reasons to normalize, like not
"wasting" columns that
are usually blank, and not having to search three or five
columns instead
of one (For example, to determine what customer might have
sent us an
incomplete or garbled fax message or credit card
transaction where all we
know is that their address is "345 Main Street"). But
avoiding future
expensive schema changes is the main one.
Bill
On Wed, Feb 22, 2012 at 11:02 AM, Wills, Steve
<[email protected] <mailto:[email protected]>> wrote:
> “Too relational” is a state that is rarely achieved,
IMHO. I think your
> issue/question often and I like the direction of your
thinking. I guess
> that thinking about such makes me a little “twisted” to
some. I also own
> my own barcode-scanner - well enough about my
predilections!****
>
>
--
William Stacy, O.D.
Please visit my website by clicking on :
http://www.folsomeye.net
--
William Stacy, O.D.
Please visit my website by clicking on :
http://www.folsomeye.net
--- RBASE-L
=======================3D=======================3
D=
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
=======================3D=======================3
D=
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
=======================3D=======================3
D=
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
=======================3D=======================3
D=
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
=======================3D=======================3
D=
