Re: How should I type my data- ZIP codes and States/Regions
Date: 2000/05/07
Message-ID: <39161a3a_2_at_corp.newsfeeds.com>#1/1
First, let me say the biggest reason I *always* use strings for Zip is simple: many begin with a 0. Storing as a number presents all sorts of havoc when you try to match later.
Anyway...
I did a similar thing on one job and we purchased a Zip-City-State lookup file. I think it was around $100 and we got quarterly updates for something like $20. (I apologize, but I have no clue what it was. I remember doing an Internet search at the time and finding a dozen or so that were all similar.) It seems like the whole file was only about 4 MB, so even small workgroup databases can use it fine.
The only hitch is that a Zip code doesn't yield a *unique* city/state. The nice thing about this particular file was that it had sub-Zip ID for each unique city-name in the Zip code.
For example: you program the application to prompt for Zip first. Then it looks up in this table and returns a list of possible cities. Sometimes there's one, other times there are half a dozen. When the user selects the correct city, you can just store two numbers: the Zip code (as a string!) and a second ID for the city, like "2" or something. So, zip 97005 might have Beaverton, Portland, Tigard. To store Beaverton, you'd store Zip: 97005, Sub-zip: 1.
It all worked quite well.
Eric M. Wilson
Database Architect
Household Technology Services
<chrisc_at_pittauto.com> wrote in message news:8esgve$iup$1_at_nnrp1.deja.com...
> In article <g5vbfsk9otqnajdhhg5r45qp6r79a97n6b_at_4ax.com>,
> Fernando <spamers_at_must.die> wrote:
> > On Thu, 13 Apr 2000 10:19:31 -0400, "Jon M." <Jonatmfr_at_eei.org> wrote:
> >
> >> In my experience it is far better to store zips as strings,
> >> especially if you ever intend to store international addresses
> >> (ever see a Canadian zip code?). I'm not sure I understand why
> >> you would even consider storing a state as a number.
> >
> > I guess he isn't sure if storing the states as strings (and repeating
> > n times the same string) or using a number: a foreign key from a table
> > consisting in state_id and state_name
> > // Fernando Rodriguez Romero
>
> I'm not sure I see the advantage of this... You'd have the same number
> of numeric foreign keys as you would 2-digit strings...
>
> On the other hand, I've been toying with the idea of only including
> zipcode in the table that stores addresses and using that as a key in
> another table that stores the city, state, and county. (International
> addresses are not an issue with this database). These other fields
> would be automatically populated once the zipcode is entered...
> unfortunately, I haven't yet found a list of this data - anyone know
> where I might?
> > >
> Sent via Deja.com http://www.deja.com/
> Before you buy.
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- Received on Sun May 07 2000 - 00:00:00 CEST