Thread: standard schemas for addresses, others?

standard schemas for addresses, others?

From
Dennis Gearon
Date:
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

Some rules:
-------------
A/ Do duplicate countries
B/ No duplicate states in Countries
    (states seem only to exist in 'Federal' governments)
C/ No duplicate postal codes in Countries
D/ Duplicate cities are allowed in Countries,
    and in the world.
E/ Duplicate cities are allowed in States.
F/ Cities can have multiple postal codes
G/ Postal codes are assigned to only one city.
H/ Postal codes are assigned to counties.
I/



Re: standard schemas for addresses, others?

From
Bruno Wolff III
Date:
On Wed, Jan 22, 2003 at 14:18:47 -0800,
  Dennis Gearon <gearond@cvc.net> 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
>
> Some rules:
> -------------
> A/ Do duplicate countries
> B/ No duplicate states in Countries
>     (states seem only to exist in 'Federal' governments)
> C/ No duplicate postal codes in Countries
> D/ Duplicate cities are allowed in Countries,
>     and in the world.
> E/ Duplicate cities are allowed in States.
> F/ Cities can have multiple postal codes
> G/ Postal codes are assigned to only one city.
> H/ Postal codes are assigned to counties.
> I/

The post office has information on US addresses. They are more complicated
than you might think. Puerto Rico especially.
Note that 5 digit zip codes can span states.

There is FIPS data that you can get from the government over the net
(at least until they realize terrorists might find the data useful)
that has information about streets in the US.

Re: standard schemas for addresses, others?

From
will trillich
Date:
On Thu, Jan 23, 2003 at 07:53:18AM -0600, Bruno Wolff III wrote:
> On Wed, Jan 22, 2003 at 14:18:47 -0800,
>   Dennis Gearon <gearond@cvc.net> 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
> >
> > Some rules:
> > -------------
> > A/ Do duplicate countries
> > B/ No duplicate states in Countries
> >     (states seem only to exist in 'Federal' governments)
> > C/ No duplicate postal codes in Countries
> > D/ Duplicate cities are allowed in Countries,
> >     and in the world.
> > E/ Duplicate cities are allowed in States.

but no duplicate cities per county. (right?)

> > F/ Cities can have multiple postal codes
> > G/ Postal codes are assigned to only one city.

maybe not. double-check that.

> > H/ Postal codes are assigned to counties.

for sparsely-populate counties i'd bet that one zip code can
cover a lot of area, maybe even crossing county boundaries.

> The post office has information on US addresses. They are more
> complicated than you might think. Puerto Rico especially.
> Note that 5 digit zip codes can span states.

really? that's just plain mean. (at least it backs up the
cross-county theory i whipped up, above.)

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

Re: standard schemas for addresses, others?

From
will trillich
Date:
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/ !

Re: standard schemas for addresses, others?

From
Dennis Gearon
Date:
Not sure this made it to the list
------------------------------------------------

I previously wrote:

>After a LOT of research, (LAUGH, by today's standard's using google), I found the answer.
>
>There is an emerging INTERNATIONAL ADDRESS standard for EDI and other applications, (If you're
>against globalization, sorry). It doesn't give relations between the data fields, just the
>available fields. Click on the links for:
>
>
>    'IAEC' on:
>
>http://www.eccma.org/downloads.php3
>
>1/22/2003 2:18:47 PM, Dennis Gearon <gearond@cvc.net> wrote:
>
>Are there any sites with 'standard schemas' for certain, repetitive database needs?



Re: standard schemas for addresses, others?

From
Dennis Gearon
Date:
Now THAT's a good idea, store a regex pattern for each country's zip code!

1/23/2003 10:01:19 AM, will trillich <will@serensoft.com> wrote:
>
>
>    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' );
>




Re: standard schemas for addresses, others?

From
will trillich
Date:
On Thu, Jan 23, 2003 at 10:56:27AM -0800, Dennis Gearon wrote:
> Now THAT's a good idea, store a regex pattern for each country's zip code!

> 1/23/2003 10:01:19 AM, will trillich <will@serensoft.com> wrote:
> >    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' );

i only bother taking credit for ideas which don't seem obvious
to me. this one sure did.

i'll make an exception in this case. glad to have contributed!

:)

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

Re: standard schemas for addresses, others?

From
Peter Eisentraut
Date:
Dennis Gearon writes:

> 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?

No chance.  You need to cut a compromise between structure and
flexibility.  If you just want to save, say, shipping addresses, then make
them free text -- person name, address information, country.  (Possibly
divide the address information into street'ish and city'ish, but that's
already pushing it.)  If you need the addresses to be structured so you
can do data analysis then you need to define your actual needs.  Probably
you don't have customers *everywhere*.

--
Peter Eisentraut   peter_e@gmx.net


Re: standard schemas for addresses, others?

From
Dennis Gearon
Date:
Oh,
    but I WANT customers everywhere, and ....

    my money for nothing and my chicks for free of course! Just trying to plan ahead for
when I can hire everyone else on this list!

    I guess I've been spending too much time next to a monitor :-)

1/23/2003 3:30:49 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

>Dennis Gearon writes:
>
>> 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?
>
>No chance.  You need to cut a compromise between structure and
>flexibility.  If you just want to save, say, shipping addresses, then make
>them free text -- person name, address information, country.  (Possibly
>divide the address information into street'ish and city'ish, but that's
>already pushing it.)  If you need the addresses to be structured so you
>can do data analysis then you need to define your actual needs.  Probably
>you don't have customers *everywhere*.
>
>--
>Peter Eisentraut   peter_e@gmx.net
>
>




Re: standard schemas for addresses, others?

From
Oliver Vecernik
Date:
Dennis Gearon schrieb:


>> 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?
>
>


There is a great source of information on:

http://www.upu.int/post_code/en/addressing_formats_guide.shtml

But it also shows how difficult the actual situation is. Even if you're
sending to the same addressee the format is depending on the origin from
where it is sent. Refer to:

http://www.upu.int/post_code/en/formatting_international_address_en.pdf

If you're interested I can post the structure of Mozilla address book.
It seems to be sufficent for my needs.

HTH
Oliver

--
VECERNIK Datenerfassungssysteme
A-2560 Hernstein, Hofkogelgasse 17
Tel.: +43 2633 47530, Fax: DW 50
http://members.aon.at/vecernik



Re: standard schemas for addresses, others?

From
Dennis Gearon
Date:
Please do post the Mozilla address format.

> There is a great source of information on:
>
> http://www.upu.int/post_code/en/addressing_formats_guide.shtml
>
> But it also shows how difficult the actual situation is. Even if you're
> sending to the same addressee the format is depending on the origin from
> where it is sent. Refer to:
>
> http://www.upu.int/post_code/en/formatting_international_address_en.pdf
>
> If you're interested I can post the structure of Mozilla address book.
> It seems to be sufficent for my needs.
>

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
                         Sincerely, Dennis Gearon

Re: standard schemas for addresses, others?

From
Oliver Vecernik
Date:
Dennis Gearon schrieb:

>Please do post the Mozilla address format.
>
>
CREATE TABLE mab (
  "first_name" text,
  "last_name" text,
  "display_name" text,
  "nickname" text,
  "primary_email" text,
  "secondary_email" text,
  "unknown_1" text,
  "unknown_2" text,
  "work_phone" text,
  "home_phone" text,
  "fax_number" text,
  "pager_number" text,
  "cellular_number" text,
  "home_address" text,
  "home_address_2" text,
  "home_city" text,
  "home_state" text,
  "home_zipcode" text,
  "home_country" text,
  "work_address" text,
  "work_address_2" text,
  "work_city" text,
  "work_state" text,
  "work_zipcode" text,
  "work_country" text,
  "job_title" text,
  "department" text,
  "company" text,
  "work_web_page" text,
  "home_web_page" text,
  "birth_year" text,
  "birth_month" text,
  "birth_day" text,
  "custom_1" text,
  "custom_2" text,
  "custom_3" text,
  "custom_4" text,
  "notes" text
);

Some notes on this: it is interesting to mention that there are two
fields I don't know what they are but they exist after export to a tab
delimeted file (I'm using Mozilla 1.0.0 on Debian/Woody). But you have
to leave them out if you import the data back again. I found the
birth_year, birth_month and birth_day by accident and they are not used
within Mozilla. One field is missing: message format preferation. Maybe
there is already a corrected update.

HTH
Oliver

--
VECERNIK Datenerfassungssysteme
A-2560 Hernstein, Hofkogelgasse 17
Tel.: +43 2633 47530, Fax: DW 50
http://members.aon.at/vecernik