Re: OT: Address Fields

> 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