I think the decision about whether or not to decompose the address string would
depend on the benefit that it delivered. I'm sure that it would be limited for
most applications. Of course, we could also consider it within the context
that I think is a worthy parallel to this, namely, that of "names" ... but that
might run the risk of extending this discussion-thread for quite some time!
The whole name-thang might be stickier than addresses, given language and/or
cultural differences. As a little example, Bill_D graciously used "Professor"
to refer to me in an earlier message. Such a `Name_Prefix`||`Address_As` value
implies high praise, especially coming from Bill. (Should I confess that I
would have responded to "Hey, You!", as well?)
As for me, who has gone by "Steve" since birth - well, "Stevie" until some
youthful age - but was legally named "Joseph Stephen Wills", I take umbrage
with forms, paper or electronic, that restrict the `MI` to a single character
or leave it off altogether. However, I take my "victimization" with a
Mona-Lisa-smile of knowing that regularly enjoy trying out 'J STEPHEN'
first-name inputs for electronic forms, often just to see if the programmers
had even reflected on the topic before they started generating lines of code.
I have had forms "choke" on the embedded space.
Well, regardless of programmers named Prometheus ("foresight") or a solid
record layout for `PERSON_NAME`, I still don't think it will help all those
folks who render my name as "Willis", rather than "Wills", as in Bob or Maury,
or (when I was younger) "chills, thrills, spills, and excitement".
;-)
From: [email protected] [mailto:[email protected]] On Behalf Of Javier Valencia
Sent: Thursday, February 23, 2012 12:17 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Too relational?
Steve,
The format you presented is pretty much what most GIS/Cadastral systems use.
Although it seems pretty straight forward, it can get very complicated. I had a
project working with a utility company in Arizona, and half of the street
names/addresses were in Spanish where the format is backwards (or is our system
backwards?). I had to write a program to parse the addresses to separate the
different components for cadastral compliance; it is a good thing that my
Spanish is still good and even then, many columns were left empty as it was not
feasible to parse the address and have the same result when put together.
I am in favor of simplicity. I normally have one column Address1 where the 5
components you mentioned are stored, and one column Address2 where any
additional information, such as Apt. No. go. This works for 99.99% of addresses
in this country. The duplication, blank columns and additional storage space do
not bother me as storage is dirt cheap.
Javier.
Javier Valencia, PE
O: 913-829-0888
H: 913-397-9605
C: 913-915-3137
From: [email protected] [mailto:[email protected]] On Behalf Of Wills, Steve
Sent: Thursday, February 23, 2012 9:50 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Too relational?
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)
o Primary Address Number
o Predirectional
o Street Name
o Suffix
o Postdirectional
o Example: "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]>
[mailto:[email protected]]<mailto:[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
