Re: [Gnumed-devel] demographics.sql

[Ian Haywood]

On Thu, 4 Mar 2004 22:49:47 -0800
Jim Busser <address@hidden> wrote:

> I searched the list archive and gnumed.org (and can no longer search 
> gnumed.net) but could find little background on what is intended. 
> Especially if you want / need more discussion than what gets offered 
> --- and even if not --- it could aid me and others for you to point to 
> a specific white paper or other background info, or else to elaborate - 
> thanks!
The only real "background info" is the file gmDemographics.sql, sorry.

but let me pose the question more generally.

Currently the database schema stores information about a flat list of 
individuals
(which can be patients, staff and contacts all mixed together, this decision 
[to mix
them all] was taken some time ago)

Richard Terry's contacts panel has a hierarchial structure, roughly
Organisations (hospitals, path. companies, clinics)
        -> Divisions (such as hospital departments)
                -> Branches (such as collecting centres for path. companies)
                        -> Individual people.

I am looking for the best way to model this structure in the SQL backend. 
Richard: it would be interesting to know how you did this in Access.

Its complex, as individuals may be associated with several entries (viz., 
surgeon with multiple rooms),
some organisations may have no divisions, and so on. But the structure needs to 
be simple enough
to avoid a blowout in table numbers and resultant middleware complexity. Also, 
organisations and individuals
have a lot in common (phone numbers, address, etc. work the same) we need to be 
able to use the same client
code to do this.

My current thinking:
        - table org, with a reference back to itself to represent divisions [of 
orgamisations, not GP divisions]. Toplevel 
organisations have NULL here.
        - 3 tables: lnk_org2comm_channel, lnk_org2ext_id, lnk_org2address
        - the equivalent 3 tables regularised in naming: 
lnk_identity2comm_channel, lnk_identity2ext_id,  lnk_identity2address,  so we 
can use the 
same client business code for both with these 3 aspects, as their behaviour is 
identical. I appreciate for other aspects (i.e. names) we need 
separate code as they behave quite differently.
        - no specific table for branches. Instead, we conceptualise branches as 
organisation X at address Y.
Similarily, individuals who are also have address Y as one of their addresses 
are "members" of that branch.

Ian

-- 
PGP public key E750652E at wwwkeys.pgp.net
9BF0 67B7 F84F F7EE 0C42  C063 28FC BC52 E750 652E

pgp3Kwmpjh3sH.pgp
Description: PGP signature