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/ !