Thread: standard schemas for addresses, others?
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/
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.
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/ !
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/ !
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?
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' ); >
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/ !
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
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
>
>
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
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
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