NYCPHP Meetup

[Phil Duffy]

Russ,

> -----Original Message-----
> From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org]
> On Behalf Of Russ Demarest
> Sent: Tuesday, October 04, 2005 11:16 AM
> To: NYPHP Talk
> Subject: Re: [nycphp-talk] data modelling vs. db design (was: ER Diagram
> toolfor MySQL/OS X)

>  From a data point of view you would normalize it all and I would
> suggest a states and countries table to be linked to addresses as a
> well as address type. But when you are trying to come up with all the
> users with a address in NY who are related to other tables, i.e.
> groups, you may wish you have it all in one table.

We found FIPS Pub 55-3 ( http://www.census.gov/geo/www/fips/fips.html ) to
be helpful for creating the geographical sub-division structure within a
country and FIPS 10-4 (http://earth-info.nga.mil/gns/html/fips10-4.html ) to
be useful for country coding and the first level of country sub-division.
The latter introduces the issues of multiple sub-division types within a
country (US has states and a district, Canada has provinces and territories,
and Argentina has districts, national territories and provinces).  FIPS 10-4
also reveals the native names for these sub-divisions, e.g., distrito,
territorio nacional and provincia for Argentina and velayats for
Afghanistan.

You may have an issue with postal codes since these vary by country.  We
addressed that by establishing different format masks such that multiple
countries could share the same postal code mask for editing.  Canada, UK and
the US all used different postal code masks, which were attributes of the
country table.

> 
> If you get really "normal" you could have a "user_addresses" table
> which would contain the address_type field so if you have 2 users at
> the same address the address would only be in the address table once
> but related to both users. :)

Absolutely.  When the oldest daughter in a 12-child family moves out, only
her association with the address is changed.  This can be a real issue in
medical information systems in which the caregivers are responsible for
multiple family members.

Phil