OT: Address Fields

On Thursday 31 Jul 2003 10:39 in
<pan.2003.07.31.09.39.34.792868@salterprojects.com>, 2trax
(2trax@salterprojects.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@spamtrap.ntlworld.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.2003.07.31.09.39.34.792868@salterprojects.com>, 2trax
(2trax@salterprojects.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@netfront.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