Address Field Standards

[xochi]

(moderator -- not sure if this is the right forum, so please move it if it belongs elsewhere)

Does anyone know any standards for handling address fields for a USA-centric database? I'm migrating a FM6 database to FM7, and just recently went through a third-party NCOA (national change of address) and DSF (delivery sequence file/ address standardization) process, and came up with some questions.

• # of address fields: It appears that the USPS uses 2 address fields only, and can't handle more than 2. Is there ever a reason to keep 3?
  
• Foreign States & Zip Codes : our data is 99%+ USA addresses, but we have a few foreign ones (mexico and canada mainly). Is there a standard way of storing these in the US-fields? I.e. put the foreign "zip" code in the Zip code field, and the foreign territory/state/province in the State field?
  
• Zip+4 in one field vs. two fields? I was surprised to find that my vendor's NCOA service doesn't ask for Zip+4, but simply the 5 digit Zip code. Thus, I'm wondering if it makes sense to store Zip+4 in one field vs. two?
  

[stanley]

Xochi:

While the USPS only uses two lines, many companies use more than that for internal routing - especially in large buildings where you've got thousands of employees and multiple mail rooms - so I like to give an extra line for that kind of data.

Every solution I have seen uses the same field for US zip code and for foreign post code. Same thing for US state - it translates into territory/state/province/county overseas. So long as you are not enforcing US formats (i.e., 5-digit zip, two-letter all-cap state) across your solution, you're fine.

As far as zip+4 goes - surely you could do it either way, but I'd think you should keep it in one field. Perhaps someone else will pipe up on this point.

-Stanley

[xochi]

Correction -- my NCOA provider actually uses 3 fields "Address1, Address2, and Company", so 3 fields seems to be wise.

As for a single Zip code, vs. Zip5 and Zip4, I think what I'll do is keep a single Zip field with an auto-enter calc that reformats it to a the "XXXXX-YYYY" format, and then created calc fields that split it into 5 and 4 digit fields for exports, printing, etc.

[Brian C]

I dont know if you have data validation happening for your addresses, but you may want to keep in mind that the USPS uses Default Zip/City records as well as Acceptable Zip/City records.

This is done because new townships/subdivisions tend to pop up from time to time but do not have their own unique zip-code assigned as of yet. The USPS still accepts them as valid alternate city names for purposes of mail delivery.

In any case it is good thing to keep in mind for data validation purposes.