Re: How should I type my data- ZIP codes and States/Regions

"David Cressey" <David_at_DCressey.com> wrote:

>If you store ZIP codes as strings, then when you begin to want to store
>Canadian Postal Codes,
>you will be able to use the same column. If ZIP is numeric you will need a
>different column for
>Canadian Postal Codes.

     . . . or if you then decide to use ZIP+4.

     Make the field a bit bigger to allow for other countrys' mail code systems. Remember when doing validation on the value to allow for the trailing blanks (if it's not a varying length field).

>As far as state is concerned, the best bet is to use the same 2 letter
>codes the US Postal Service does.
>There are two letter codes for overseas territories, like PR = Puerto Rico,
>and for
>Canadian Provinces, like PQ = Quebec. I don't know where, but I'm sure
>there's a website that
>lists all of them.

     USPS publishes a ZIP book. The U.S. stuff will be in there. (Go to a good-sized library.) As to Canada:

          BC   British Columbia
          AB   Alberta
          SK   Saskatchewan
          MB   Manitoba
          ON   Ontario
          QC   Quebec
          PQ   Quebec     Yes, Quebec has two.  This is the older one.
          PE   Prince Edward Island
          NB   New Brunswick
          NS   Nova Scotia
          NF   Newfoundland
          YT   Yukon Territory
          NW   Northwest Territory

[snipped previous]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.