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(at)serensoft(dot)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/ !