Thread
-
standard schemas for addresses, others?
Dennis Gearon <gearond@cvc.net> — 2003-01-22T22:18:47Z
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?
Bruno Wolff III <bruno@wolff.to> — 2003-01-23T13:53:18Z
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?
Will Trillich <will@serensoft.com> — 2003-01-23T18:01:19Z
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?
Will Trillich <will@serensoft.com> — 2003-01-23T18:03:55Z
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?
Dennis Gearon <gearond@cvc.net> — 2003-01-23T18:56:27Z
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?
Dennis Gearon <gearond@cvc.net> — 2003-01-23T19:01:21Z
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?
Will Trillich <will@serensoft.com> — 2003-01-23T20:52:13Z
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?
Peter Eisentraut <peter_e@gmx.net> — 2003-01-23T23:30:49Z
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?
Dennis Gearon <gearond@cvc.net> — 2003-01-24T00:20:40Z
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 > >