Re: [Gnumed-devel] Questions re database schema:street:address:urb:country

[Karsten Hilbert]

[on wildly varying standards re "address" definition]

The one thing to remember is that we are trying to usefully
identify patients based on their demographics, not trying to
make every meaningfully separable bit of demographic
information fit for algorithmic handling. We are not trying to
write the US Postal Service part of the We-Own-You database of
the US Homeland Security Office.

So ...

Liz wrote:
> >We definitely have sub-urbs
> >in our town as well as villages outside the town
> >we use the town (urb) name and the village name in an address but not the
> >sub-urb name. They exist on signs and people use them to describe which 
> >part of the town,
Same here.

> Liz mentioned that a Village name can be important inside an URB for 
> example for mailing purposes.
Here in Germany it is often helpful if one doesn't remember
the coplete postcode but knows the suburb. I sometines do end
up mailing to Leipzig/Mockau if I don't remember the postcode
for a street but know that it is in the Mockau part of
Leipzig.

> In Canada, line 2 of the address requires the form:
> <building number> <street name> <street type> <direction> [comma] 
> <within-building info>
Which is fine. Just input it that way. A canada specific
module might run verifications on the format.

> my office address would be
> Dr J Busser
identity.title
names.firstnames
names.lastnames

> 575 8th Ave W, Suite 350
address.number address.id_street.name address.addendum

> Vancouver BC  V5Z 1C6
address.id_urb.name address.id_urb.id_state.name address.id_street.postcode

A CA-specific formatter needs to pick the right values and
arrange them.

> (in Canada, line 2 could alternatively have been 350-575 8th Ave W)
Likewise.

> Sometimes it is necessary for mailing to provide Postal Box 
> information and in Canada this must be provided on a separate line 
> below the address line.
It is workable to put it into the addendum. However, that may
not be conpatible with rules for formatting addresses in CA.
Adding an addr_line2 is IMO quite ugly. My suggestion (based
on yours) would be to have

address."aux_street"
 - this would allow for any additional instructions at the
   street level, eg. postal box or even a comment (yes, there
   are Russian cities that have several Green Streets which
   are literally *told* apart by verbal description)
 - this would be a "text" field and thus allow for multi-line
   content if that is needed

address."subunit"
 - unit_number would be too restrictive
 - would include any instructions *inside" of address.number
 - would be "text" as well to accomodate numbers *and* prose

address."addendum"
 - this would hold any data that still didn't fit in the
   framework above.
 - same here, "text"

Locale-specific address formatters would, of course, need to
know which field to print/show where.

Comments please.

> Also, the schema presently requires me to store "8th Ave W" all 
> together as the street name. This seems workable and may even be 
> desirable. Adding separate fields <street type> and <direction> 
> would be more fiddly although without such fields more care is 
> required by the office staff during the creation (input) of new 
> street values.
That care can, should, and must be enforced by locale specific
verification code. In Germany it hardly makes much sense to
have "street_type" and "direction" as parts of "street".

> I just realized what a tremendous headache it will be trying to 
> import non-normalized patient demographic data into GnuMed - groan!
One could always use a staging table, eg.

create table legacy_address (
    pk serial primary key
    data text not null
    ... plus all the fields we have in street/address etc ...
);

and normalize/transfer to the real tables as and when the need
arises (eg. once daily for the day's patients or so). At
initial import everything is put into "data" except for what
is known to be something else. Later on successively refined
scripts can parse parts of "data" into the other fields and
transfer the row to the real tables once "data" is empty.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346