[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Gnumed-devel] demographics.sql

From: Ian Haywood
Subject: Re: [Gnumed-devel] demographics.sql
Date: Tue, 9 Mar 2004 16:38:25 +1100

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

> I searched the list archive and (and can no longer search 
> 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 
(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.


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

Attachment: pgp3Kwmpjh3sH.pgp
Description: PGP signature

reply via email to

[Prev in Thread] Current Thread [Next in Thread]