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

[Ian Haywood]

> 2) NOMALISATION:
> 
> I guess there is a difference between being pragmatic and practical and ideal 
> - however - sticking to the idea of 'full normalisation' can someone please 
> explain some stuff.
> 
> I'm tyring to understand the logic behind address vs street etc. There seems 
> to be a stack of duplication in these tables:
Normalisation is designed to achieve data-integrity. An example:
In MIMS there is a text field for drug units, which contains a dog's breakfast
of values: "mcg", "micrograms", etc., depending on what mood the pharmacist was 
in that day,
I suppose.

Drugref (which is highly normalised) has a separate table for drug units, and 
the drug
tables contain a link to it, this prevents users from making up units as they 
go along, and
guarantees consistency.
 
> public.address
> 
>         id serial NOT NULL,
>         id_street int4 NOT NULL, (presumably for key to public.street??)
Yep.
>         suburb text,    (then why not for key to public.urb)
I agree this should be dumped.
>         number char(10) NOT NULL,
>         addendum text, (what is addendum)
Extra information to define the address (granny flat etc.)
>        How come has not got an address type_ID (eg mailing/home/office etc
Good question.

However an address can be a work address for one person, and a home address for 
another
For example, Mildura Base Hospital is currently my work *and* my home (sob)
This is why address type is in link_person_org_address

> I take it that public.street  is an attempt to normalise street names, if so, 
> why is _urb and postcode included in this.
Yes. The idea is that is several patients live on one street, there is only
one entry in street.
If you get another patient on that street, you can use the street phrasewheel, 
which
can then auto-complete the rest (postcode, urb). This is not yet working, but 
easy to do.
In some locales, there may be a pre-existing database of all streets linked to 
urbs and
postcodes which can be loaded into gnumed at installation

Ideally this database would be linked to geographic information, then we can 
have
interesting queries like "which cardiologist is closest to this patient's 
house"?

having said all that, I agree full normalisation is not always possible or may 
be too slow
for real use, (but as I have pointed out, gnumed's current slowness is not 
because of the
speed of individual queries)

>       - where a field is a foreign key eg id_state which I guess points to 
> public.state can this be included in the comment:
All foreign keys have a "references" clause in the definition which points to 
the 
table and field, so

        id_state integer references state (id), 

The autogenerated HTML docs have this too.

Ian

pgpVHXISYUOoE.pgp
Description: PGP signature