Message-ID: <3F37BCF1.2060909@cvc.net>
Date: Mon, 11 Aug 2003 08:57:37 -0700
From: Dennis Gearon <gearond@cvc.net>
Reply-To: gearond@cvc.net
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.4) Gecko/20030624
X-Accept-Language: en-us, ru, es-mx
MIME-Version: 1.0
To: 2trax <2trax@salterprojects.com>
Subject: Re: [GENERAL] OT: Address Fields
References: <pan.2003.07.31.09.39.34.792868@salterprojects.com>
In-Reply-To: <pan.2003.07.31.09.39.34.792868@salterprojects.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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 may have 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
>