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

[Richard Terry]

I was away the day(months) you all decided this doing my house renovations, so 
I guess I missed participating in the debate!

My impressions of this are as follows, (and please let me waffle for a moment 
and I stand to be corrected as to the logic behind it, but I'm just trying to 
understand so I can do some database work). Forgive the capitalisation - I'm 
not shouting, just trying to make the headings stand out for easier reading:
I've broken my comments/questions down into several areas:
        Nomenclature - where I think we are falling down on naming
        Commenting - within the definition to help it make sense to others 
trying to 
learn or maintain or contribute to the database
        Table Naming.

TABLE NAMING:
--------------------
One of my pet hates is not being able to find my way around a database 
quickly, especially with the passage of time. I enclose a small png showing 
part of my table naming in MedRec - for script items vs the current gnumed 
jumped naming schema.

Imagine the following scenario. At the moment gnuMed (yet in its infancy) has 
in only a tiny part of the database over 160 tables, which read like a dogs 
breakfast. We should be aiming to visually keep all similar data together ie 
same sort of object heirachy that one should be using in descriptive terms in 
the python code:

eg    demographics_country
        demographics_states
        demographics_streets
        demographics_suburbs (or my hated urb)
        demogaphics_address
        demographics_address_type
        demogaphics_address_info
        demographics_persons_names
        demogaphics_persons_occupations
        demographics_lu_maritalstatus
        demographics_lu_titles

or.. to separate out all lookups from actual patient data one could adopt a 
schema like:

        demographics_lu_countries or .. lu_countries
        demographics_lu_states      or ..lu_states
        demographics_lu_streets    or .,.lu_streets
        demographics_lu_urb          or ..lu_urb
        demogaphics_address_data
        demographics_address_lu_type
        demogaphics_address_data_info (whatever that table is)
        demographics_persons_data_names
        demographics_persons_lu_occupations
        demographics_persons_lu_maritalstatus
        demographics_persons_lu_titles

eg    admin_staff_names
        admin_staff_roles
        admin_billing_ bla bla

eg    patient_allergies
        patient_recalls_recallfor
        patient_recalls_recalldue
        patient_script_rxdetails
        patient_script_items


or whatever...........

        

Why do I argue for this
Try scrolling down 167 tables trying to decipher what they all do:
        come to  address (at the top)
                     scroll a while....
                                 names (of what)

                      state (of what - matter!!!!!), etc etc
        

similarly I guess for views but I havn't gone there yet e.g

        view_person_firstname_lastname (not v_basic_person which gives no clue 
to 
what the view shows
         etc etc
        view_person_communications etc etc


1) NOMENCLATURE in table generating queries:

        -Given that one will be doing various joins (and I'm not sure if 8.0 
supports 
outer), is there a reason not to name the ID's and feids  better:

===================================
CREATE TABLE public.country
(
  id serial NOT NULL,  (eg id_country serial NOT NULL)
  code char(2) NOT NULL, (eg country_code....
  name text NOT NULL,     (country_name)
  deprecated date,               (country_deprecated)

CREATE TABLE public.country
(
  id_country serial NOT NULL,
  country_code char(2) NOT NULL,
  country_name text NOT NULL,   
  country_deprecated date,..... etc
====================================

I've done many hundreds of hours of complex databaseing in Access (and have 
apps running in real-life offices with dozens of concurrent users - contact 
manager), so have real life experience in the difficulties of implementing 
and maintaining code - hence my personal insistance on good naming practices

I'd be more than happy to go through each of these tables and rename them into 
something that makes more legible sense.

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:

public.address

          id serial NOT NULL,
          id_street int4 NOT NULL, (presumably for key to public.street??)
          suburb text,    (then why not for key to public.urb)
          number char(10) NOT NULL,
          addendum text, (what is addendum)
         How come has not got an address type_ID (eg mailing/home/office etc

if id_street is aforeign key for public.street, how come public.street has 
id_urb, and id_postcode?

a) Table: public.street
         id serial NOT NULL,
          id_urb int4 NOT NULL,
          name text NOT NULL,
          postcode varchar(12),

I take it that public.street  is an attempt to normalise street names, if so, 
why is _urb and postcode included in this.

b) Table:public.urb
        id serial NOT NULL,
         id_state int4 NOT NULL,
        postcode varchar(12) NOT NULL,
        name text NOT NULL,]

COMMENT ON TABLE public.urb IS 'cities, towns, dwellings ...'

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:

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



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.

Time to go home.

Regards

richard

access_gnumed.png
Description: PNG image