Re: OT: Address Fields

On Thu, 2003-07-31 at 05:39, 2trax wrote:
> Hi everyone,
>
> Just wondering if anyone has any tips on the best way to represent
> international addresses (ie from any country) in a database?
>
> It seems to me that the most flexible way is to use a text field to hold
> everything, apart from the country which suits a varchar? and perhaps have
> another dedicated varchar field to hold USA ZIP codes / UK post codes for
> easy searching?
>

Here is an idea write a small parsing routine, so that you can use a
table to do it.  Something like this:

%1 - suite, %2 - street number, %3 = street, %4 = city, %5 = district %6
= postal-code, %7 = country, %n = new-line.

Then create a table to hold the information, possibly with different
formats whether suite numbers are used or not.  Something like this:

create table country_formats (
country  char(2),
country_name varchar(30),
with_suite varchar(60),
without_suite varchar(60));

Then add records like this:

"CA", "%1-%2 %3%n%4, %5%n%6 %7", "%%2 %3%n%4, %5%n%6 %7"
"US", "%1-%2 %3%n%4, %5%n%6 %7", "%%2 %3%n%4, %5%n%6 %7"

Now you enter addresses as individual components, using a single
standard data entry screen, and let the parser do the formatting.

W