Re: standard schemas for addresses, others?

On Wed, Jan 22, 2003 at 02:18:47PM -0800, Dennis Gearon wrote:
> Are there any sites with 'standard schemas' for certain, repetitive database needs? For
> example, addresses and their components. Does anyone have a schema for addresses that will
> work for the USA *AND* internation addresses?
> 
> entities
> ----------
> Countries
> States
> Counties
> Cities
> street addresess.
> Postal Codes

if so, i'd like to find them, too. here's what were thinking of
using -- not so strict on the ruleset as you're hoping for (also,
no consideration for county):

	create or replace function check_zip(text,text)returns text as '
	    my ($zip,$pat) = @_;
	    $zip = "" if $pat && $zip !~ /^$pat$/i;
	    return $zip;
	' language 'plperl'; -- '

	drop sequence nations_id_seq;
	drop table nations;
	create table nations (
	    id       serial,

	    abbr varchar(8)  unique,
	    name varchar(40) unique,
	    zips varchar(20), -- regex (plperl) pattern for checking zips

	    primary key ( id )
	);

	insert into nations ( abbr,name,zips )
	    values ( 'USA','United States of America','\\d\\d\\d\\d\\d(-\\d\\d\\d\\d)?' );
	insert into nations ( by,abbr,name,zips )
	    values ( 'CANADA','Canada',               '[A-Z]\\d[A-Z]\\s+\\d[A-Z]\\d' );


	-- ================ --


	drop sequence states_id_seq;
	drop table states;
	create table states (
	    id       serial,

	    abbr   varchar(4)  unique,
	    name   varchar(30) unique,
	    nation varchar(8) constraint states_nation_ref references nations ( abbr ) not null,

	    primary key ( id )
	);

	insert into states(nation,abbr,name)values('USA','??','Unknown');
	insert into states(nation,abbr,name)values('USA','HI','Hawaii');
	insert into states(nation,abbr,name)values('USA','AK','Alaska');
	--<snip>--
	insert into states(nation,abbr,name)values('USA','PR','Puerto Rico');
	insert into states(nation,abbr,name)values('USA','GU','Guam');

	insert into states(nation,abbr,name)values('CANADA','AB','Alberta');
	--<snip>--
	insert into states(nation,abbr,name)values('CANADA','YT','Yukon Territory');


	-- ================ --


	drop sequence address_types_id_seq;
	drop table address_types;
	create table address_types (
	    id       serial,

	    name     varchar(20) not null,
	    score    smallint unique,

	    primary key ( id )
	);

	insert into address_types (name,score) values ('Office',          10);
	insert into address_types (name,score) values ('Secondary Office',20);
	insert into address_types (name,score) values ('Home',            30);
	insert into address_types (name,score) values ('Secondary Home',  40);
	insert into address_types (name,score) values ('Campus',          60);
	insert into address_types (name,score) values ('Family',          80);
	insert into address_types (name,score) values ('Friends',         90);
	insert into address_types (name,score) values ('Vacation',       100);
	insert into address_types (name,score) values ('Other',          250);


	-- ================ --


	drop view addresses;
	drop sequence _addresses_id_seq;
	drop table _addresses;
	create table _addresses (
	    id       serial,
	    created  timestamp(0) default current_timestamp,
	    modified timestamp(0) default current_timestamp,
	    by       bigint constraint _addresses_edited_by references _person ( id ) not null,

	    person   bigint constraint _addresses_person_ref references _person ( id ) not null,
	    type     bigint constraint _addresses_type_ref references address_types ( id ) not null,

	    addr     varchar(60),
	    city     varchar(30),
	    st       varchar(4) constraint _addresses_state_ref references states ( abbr ), -- allow null
	    zip      varchar(10),
	    notes    varchar(120),

	    primary key ( id )
	);

	create view addresses as
	select
	            a.id       ,
	            a.created  ,
	            a.modified ,
	            a.by       ,
	            a.person   ,
	            a.type     ,
	            a.addr     ,
	            a.city     ,
	    s.abbr as st,
	    s.name as state,
	            a.zip      ,
	    n.abbr as nation_abbr,
	    n.name as nation,
	    n.zips as zip_pattern,
	            a.notes
	from
	    _addresses a
	    left join -- in case we don't know the state, to begin with
	    states s
	        on (a.st = s.abbr)
	    left join -- if we don't know the state, we probly dunno the nation
	    nations n
	        on (s.nation = n.abbr)
	;

	create rule addresses_add as
	on insert to addresses
	do instead (
	    insert into _addresses (
	--        id       ,
	        created  ,
	        modified ,
	        by       ,
	        person   ,
	        type     ,
	        addr     ,
	        city     ,
	--        s.abbr as st,
	--        s.name as state,
	        st       ,
	        zip      ,
	--        n.abbr as nation_abbr,
	--        n.name as nation,
	--        n.zips as zip_pattern,
	        notes
	    ) select
	--        nope,
	        current_timestamp,
	        current_timestamp,
	                  NEW.by       ,
	                  NEW.person   ,
	                  NEW.type     ,
	                  NEW.addr     ,
	                  NEW.city     ,
	--        s.abbr as st,
	--        s.name as state,
	        states.abbr  ,
	check_zip(NEW.zip,nations.zips),
	--        n.abbr as nation_abbr,
	--        n.name as nation,
	--        n.zips as zip_pattern,
	                  NEW.notes
	    where
	        NEW.st is null -- if we don't know state at first
	        or (
	            states.abbr  = NEW.st
	            and
	            nations.abbr = states.nation
	        )
	    ;
	);

	create rule addresses_edit as
	on update to addresses
	do instead (
	    update _addresses set
	--        set id = much badness there, don't do it
	--        created  = no, no, no,
	        modified = current_timestamp,
	        by       = NEW.by,
	        person   = NEW.person   ,
	        type     = NEW.type     ,
	        addr     = NEW.addr     ,
	        city     = NEW.city     ,
	--        s.abbr as st,
	--        s.name as state,
	        st       = states.abbr  ,
	        zip      = check_zip(NEW.zip,nations.zips),
	--        n.abbr as nation_abbr,
	--        n.name as nation,
	--        n.zips as zip_pattern,
	        notes    = NEW.notes
	    where
	        id       = NEW.id
	        and (
	            NEW.st is null -- if we don't know state, right off
	            or (
	                states.abbr  = NEW.st
	                and
	                nations.abbr = states.nation
	            )
	        )
	    ;
	);

<asbestos suit at hand>
comments welcome. :)
</>

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !