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

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
>