Re: OT: Address Fields - Mailing list pgsql-general

> 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