Re: [Gnumed-devel] Questions re database schema - normalization

[Karsten Hilbert]

> 2) NOMALISATION:
Now comes the tricky stuff.

> public.address
> 
>         id serial NOT NULL,
this is

 id serial primary key

in my file

>         id_street int4 NOT NULL, (presumably for key to public.street??)
Strange. In my SQL file it is:

 id_street integer not null references street(id)

(I just now added on update/delete actions.)

Likely your schema viewer doesn't show you referential
constraints ?

>         suburb text,    (then why not for key to public.urb)
Because NO ONE HAS YET SUCCEEDED IN EXPLAINING THE DIFFERENCE
BETWEEN URB AND SUBURB IN AU.

To my understanding the way suburb is used in AU is the same
we would use town/city/village. Eg. the geographical place a
person uses to verbally designate where she lives. If that is
the case the AU-"suburb" should be stored in the urb table for
what it's worth. That is the precise reason why it actually has
that strange name. URB. An unburdened term for "place of
living".

In DE we do have suburbs but they aren't used to "officially"
designate where you live but rather to be used in proximity
decisions such as which physiotherapy to attend. Hence the
extra field in the address. Which may be wrong for other
locales but at the time of designing the table no one cared.
Hence my recent question on whether we need to take care of
the suburb in another way to which no one answered.

>         number char(10) NOT NULL,
>         addendum text, (what is addendum)
Extra stuff like "second floor around that corner behind the
black crumbling door". Also note the appropriate field
comment.

>        How come has not got an address type_ID (eg mailing/home/office etc
Because type_ID would add *meaning* to the data. Someones home
address may be someone elses work address etc. The address
table is normalized out. Note how the table comment says "an
address aka a location". A location. No more no less.

> if id_street is aforeign key for public.street, how come public.street has 
> id_urb, and id_postcode?
Because that's the closest we can come to uniqueness.

 unique(street.name, id_urb, postcode)

will hold true in all but the most adverse circumstances.
After all, the post office needs a way to deliver (barring
poste restante).

> b) Table:public.urb
I improved the comments on this table to clarify the intent.


> Whilst on the subject of COMMENT ON TABLE:
>       - where a field is a foreign key eg id_state which I guess points to 
> public.state can this be included in the comment:

a) current policy is to name foreign keys:

    fk_* in tables
    pk_* in views

> e.g COMMENT ON TABLE public.urb IS
>       ' id_state' = foreign key to table public.state
>       bla bla perhaps to explain what a postcode is to non AU people.
>       bla bla perhaps to explain what each field is
You should like to take a look at field level comments, eg.

comment on field public.urb.name is '...';

They are (almost) all there. I am a great fan of inline docs
that can be extracted. Those are listed in the schema docs at
hherb.com/gnumed/schema/ , too, along with foreign keyness.

> c) Table Public.State
>       id serial NOT NULL,
>         code char(10) NOT NULL,  (eg ACT)
>         country char(2) NOT NULL, (eg AU)
>         name text NOT NULL,   ( Austalian Capital Territory)
>       
>       *****This table seems 100% fine to me for AU!!!!*****
:-)


> Anyway, Just a few thoughts. The reason for my analysing this is I want to 
> import all my demographics data + get better naming for the project.
Looking forward to that.

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