Thread: 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@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
I think all your questions about postal standard ways of reperesenting addresses could be answered by this:
http://xml.coverpages.org/adis.html
My current development schema for addresses is:
/* */
/* File generated by "DeZign for databases" */
/* Create-date :8/11/2003 */
/* Create-time :8:53:32 AM */
/* project-name :OregonAl-AnonBackEnd */
/* project-author :Dennis Gearon */
/* */
CREATE TABLE Ctrys(
ctry_id serial NOT NULL PRIMARY KEY,
ctry varchar(64) NOT NULL,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
UNIQUE(ctry)
);
COMMENT ON COLUMN Ctrys.ctry_id IS 'integer surr primary key';
COMMENT ON COLUMN Ctrys.ctry IS 'country name(en, utf-8)';
CREATE TABLE States(
state_id serial NOT NULL PRIMARY KEY,
state varchar(64) NOT NULL,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
UNIQUE(state)
);
COMMENT ON COLUMN States.state_id IS 'integer surr primary key';
COMMENT ON COLUMN States.state IS 'name of state(en, utf-8)';
CREATE TABLE Cities(
city_id serial NOT NULL PRIMARY KEY,
city varchar(128) NOT NULL,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
UNIQUE(city)
);
COMMENT ON COLUMN Cities.city_id IS 'integer surr primary key';
COMMENT ON COLUMN Cities.city IS 'city name(en, utf-8)';
CREATE TABLE Locales(
locale_id serial NOT NULL PRIMARY KEY,
ctry_id int4 NOT NULL,
state_id int4 NOT NULL,
city_id int4 NOT NULL,
postal_code varchar(32) NOT NULL,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);
COMMENT ON COLUMN Locales.locale_id IS 'integer surr primary key';
COMMENT ON COLUMN Locales.ctry_id IS 'integer foreign key, for reference integrity deletions';
COMMENT ON COLUMN Locales.state_id IS 'integer foreign key, for reference integrity deletions';
COMMENT ON COLUMN Locales.city_id IS 'integer foreign key, for reference integrity deletions';
COMMENT ON COLUMN Locales.postal_code IS 'alphanumeric Postal code(en, utf-8) (Not set uniqe because multiple countries
mayhave duplicate postal codes<unlikely though>)';
CREATE UNIQUE INDEX Locales_No_Dupe_Ctry_Postal_Codes ON Locales (ctry_id,postal_code);
CREATE UNIQUE INDEX Locales_No_Dupe_Ctry_States ON Locales (ctry_id,state_id);
CREATE UNIQUE INDEX Locales_No_Dupe_Ctry_City_PostalCodes ON Locales (city_id,ctry_id,postal_code);
CREATE TABLE Addrs(
addr_id serial NOT NULL PRIMARY KEY,
locale_id int4 NOT NULL,
street_addr varchar(64) NOT NULL,
street_addr_extra varchar(64) DEFAULT 'none' NOT NULL,
latitude decimal(2,6),
longitude decimal(2,6),
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);
COMMENT ON COLUMN Addrs.addr_id IS 'integer surr primary key';
COMMENT ON COLUMN Addrs.locale_id IS 'integer foreign key, for reference integrity';
COMMENT ON COLUMN Addrs.street_addr IS 'number, street, apt/ds, or po box(en,utf-8)';
COMMENT ON COLUMN Addrs.street_addr_extra IS 'additional address info(en,utf-8)';
COMMENT ON COLUMN Addrs.latitude IS '>= -90.000000 and <= 90.000000';
COMMENT ON COLUMN Addrs.longitude IS '>= 000.000000 and <= 360.000000';
CREATE UNIQUE INDEX Addrs_NoDuplicates_Addr ON Addrs (locale_id,street_addr,street_addr_extra);
CREATE UNIQUE INDEX Addrs_NoDuplicate_Geocodes ON Addrs (latitude,longitude);
ALTER TABLE Locales
ADD CONSTRAINT FK_City_1T1__0TM_Locale_ODR_OUC_1
FOREIGN KEY (city_id) REFERENCES Cities (city_id)
;
ALTER TABLE Locales
ADD CONSTRAINT FK_Ctry_1T1__0TM_Locale_ODR_OUC_2
FOREIGN KEY (ctry_id) REFERENCES Ctrys (ctry_id)
;
ALTER TABLE Locales
ADD CONSTRAINT FK_State_1T1__0TM_Locale_ODR_OUC_3
FOREIGN KEY (state_id) REFERENCES States (state_id)
;
ALTER TABLE Addrs
ADD CONSTRAINT FK_Locale_1T1__0TM_Addr_ODR_OUC_1
FOREIGN KEY (locale_id) REFERENCES Locales (locale_id)
;
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?
>
> 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@netfront.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>