Re: OT: Address Fields

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?

Advice from those who have successfully created a scheme with enough
flexibility and structure to be useful would be greatly appreciated.

Thanks,

Sam.

---
Posted via news://freenews.netfront.net
Complaints to news(at)netfront(dot)net

On Thursday 31 Jul 2003 10:39 in
<pan(dot)2003(dot)07(dot)31(dot)09(dot)39(dot)34(dot)792868(at)salterprojects(dot)com>, 2trax
(2trax(at)salterprojects(dot)com) wrote:

> 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?

There are ISO standard codes for countries: US, CA, GB, AU, etc. These each
fit into a fixed-width CHAR(2) field.

I would use a VARCHAR of some large size and map it into structured fields
using views. Each view is built with
WHERE country_code = 'xx'
for the matching 'xx' in the ISO standard. This will allow you to search in
a structured manner, provided you have the country code.

--
Regards,

Dave [RLU#314465]
======================================================
dwnoon(at)spamtrap(dot)ntlworld(dot)com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================

On Thu, 31 Jul 2003 11:19:25 +0100, David W Noon wrote:

> On Thursday 31 Jul 2003 10:39 in
> <pan(dot)2003(dot)07(dot)31(dot)09(dot)39(dot)34(dot)792868(at)salterprojects(dot)com>, 2trax
> (2trax(at)salterprojects(dot)com) wrote:
>
>> 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?
>
> There are ISO standard codes for countries: US, CA, GB, AU, etc. These each
> fit into a fixed-width CHAR(2) field.
>
> I would use a VARCHAR of some large size and map it into structured fields
> using views. Each view is built with
> WHERE country_code = 'xx'
> for the matching 'xx' in the ISO standard. This will allow you to search in
> a structured manner, provided you have the country code.

David,

Thanks for a good suggestion. I'll take a crack at it.

Cheers,

Sam.
---
Posted via news://freenews.netfront.net
Complaints to news(at)netfront(dot)net

> Just wondering if anyone has any tips on the best way to represent
> international addresses (ie from any country) in a database?

Not the *best* way but here is how we do it in GnuMed
(www.gnumed.org). Add in some convenient denormalizing views
that I did not include. Full schema in CVS on gnu.org, of
course.

PS: Mike, this is the schema that you helped getting
v_zip2data right on.

--- ===================================================================
create table country (
id serial primary key,
code char(2) unique not null,
name text not null,
deprecated date default null
);

--- ===================================================================
create table state (
id serial primary key,
code char(10) not null,
country char(2) not null references country(code),
name text not null,
unique (code, country)
) inherits (audit_fields, audit_mark);

--- ===================================================================
create table urb (
id serial primary key,
id_state integer not null references state(id),
postcode varchar(12) not null,
name text not null,
unique (id_state, postcode, name)
) inherits (audit_fields, audit_mark);

--- ===================================================================
create table street (
id serial primary key,
id_urb integer not null references urb(id),
name text not null,
postcode varchar(12),
unique(id_urb, name)
) inherits (audit_fields, audit_mark);

--- ===================================================================
create table address (
id serial primary key,
--- indirectly references urb(id)
id_street integer not null references street(id),
suburb text default null,
number char(10) not null,
addendum text
) inherits (audit_fields, audit_mark);

--- ===================================================================
create table address_type (
id serial primary key,
"name" text unique not null
);

--- ===================================================================
create table lnk_person2address (
id serial primary key,
id_identity integer references identity,
id_address integer references address,
id_type int references address_type default 1,
address_source varchar(30)
);

--- ===================================================================
--- organisation related tables
--- ===================================================================
create table org_address (
id serial primary key,
id_address integer not null references address(id),
is_head_office bool not null default true,
is_postal_address bool not null default true,
unique (id_address, is_head_office, is_postal_address)
) ;

--- ===================================================================
create table lnk_org2address (
id serial primary key,
id_org integer not null references org(id),
id_address integer not null references org_address(id),
unique (id_org, id_address)
);

Karsten Hilbert, MD
---
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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