Re: Normalization was Left AS/400 and Returned

Booth,

> I supposed every post office had its own zipcode.  Some towns have more than
> one post office, but is there a place where towns share a post office?

Yes.  For example, some rural areas don't even have a post office yet
have a different town name in the mail delivery address.  And in
metropolitan areas, I think you will find postal carrier routes (and
thus zip codes) can cross into neighboring suburbs quite frequently.
 
> But beyond that, doesn't zip + 4 give you right down to the apartment number
> and street?

Zip+4 can give you an accurate city and state (if your tables are up
to date and you have a good Zip+4), but then you still aren't really
dealing with a good way to normalize the data and not store the
city/state with the address.  Zip codes can and do change.  You use
the street address, city, and state to have a zip code program assign
the correct current Zip+4, not the other way around.  (And they also
standardize the street address, correct spellings, etc.)

I doi keep zip code tables to assit with data entry on both my PC and
400 based programs.  On the PC I advance the cursro from the street
address to the zipcode.  As soon as they key the 5th digit I insert
the "primary" city and state for them but allow them to override it if
it doesn't match what they expect.  On the 400, if the user leaves the
city / state blank, I'll fill it in for them and repeat the screen to
give them a chance to verify and correct it if needed.  It is correct
enough of the time to save a bunch of effort when keying, but wrong
enough that you can't just use the primary city / state and not store
it in your database.

I've often thought about keeping the full list of all cities in a zip
code, and when more than one exists, popup a window to let the user
select the desired one (or enter one of their own).  It just never
made it high enough up my list of things to do...

Doug