https://tools.usps.com/go/ZipLookupAction!input.action is the usps zip+4 lookup tool which is pretty nice and shows proper formatting. A little funky, but it's free...
On Thu, Feb 23, 2012 at 8:48 AM, Wills, Steve <[email protected]> wrote: > Die Deutschen sind ganz logisch ... makes sense to me. > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Albert > Berry > Sent: Thursday, February 23, 2012 10:44 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: Too relational? > > 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 > > > > > > > -- William Stacy, O.D. Please visit my website by clicking on : http://www.folsomeye.net
