Re: [Gnumed-devel] demographics.sql

[Richard Terry]

>
> 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.

I will mail you my contacts.mdb for your perusal Ian once I strip the data.

I experimented with this for a long time, and the design went through many 
interations, just because of the complexity of relationships which you 
presented below.

When I wrote the contacts it was for our local division of general practice, 
as they had been using ACT (I think) and had found it couldn't meet their 
needs. The database structure is an example of pragmatism and I'm sure Horst 
would not like it.

The simple way to manage these many relationships is via  a central links 
table. (see the png dump). This gives enormous flexibility because you can 
attatch limitless  specific instances to it aside from basic names and 
addresses (see in the dump the tables which they use to keep track of GP 
specific stuff.

The queries necessary to extract data may be exceedingly complex (don't get 
too scared when you see the query text dump, but even on a pentium 266 in 
access are very quick. The resultant information is so granular that one can 
display it in extremely useful ways (see screen dump of john hunter hospital 
departments and employees (nothing that isnt on the hunter area health 
directory so it is public information).

============================================
This is a typical single query from the database  - pulls out list of the 
organisastions
============================================
SELECT data_links.Link_ID, data_links.Deleted, data_links.Last_Updated, 
data_links.GP_PersonSpecific_ID, data_links.GP_PracticeSpecific_ID, 
data_links.GeneralPracticeSpecific_ID, data_links.Preferred_Address, 
data_links.Organisation_Communication_ID, 
data_links.Employee_Communication_ID, data_Communications_2.Work_Phone AS 
Employee_Work_Phone, data_Communications_2.Work_phone_confidential AS 
Employee_Work_phone_confidential, data_Communications_2.Fax AS 
Employee_Work_Fax, data_Communications_2.Fax_Confidential AS 
Employee_Fax_Confidential, data_links.Network_ID, data_links.Associate_ID, 
data_links.Memo_ID, Data_Organisations.Description, 
data_links.Organisation_ID, data_links.Address_ID, 
data_Communications_1.Work_Phone, 
data_Communications_1.Work_phone_confidential, data_Communications_1.Fax, 
data_Communications_1.Fax_Confidential, data_Communications_1.email, 
data_Communications_1.email_Confidential, data_Communications_1.internet, 
data_Communications_1.internet_Confidential, data_Communications_1.mobile, 
data_Communications_1.mobile_confidential, lu_categories.Category_ID, 
lu_categories.Category, Data_Addresses.[Street1] & " " & [Suburb] AS Address, 
Data_Addresses.Street1, Data_Addresses.Street2, Data_Addresses.Street3, 
Data_Addresses.Suburb, Data_Addresses.Postcode, Data_Addresses.State, 
Data_Addresses.Head_Office, Data_Addresses.Postal_Address, [Data_Persons].
[Firstname] & " " & [Surname] AS Name, Data_Persons.Surname, 
data_links.Person_ID, Data_Persons.Firstname, Data_Persons.Title, 
Data_Persons.Sex, Data_Persons.Birthdate, Data_Persons.Deleted AS 
Persons_Deleted, data_links.Person_Occupation_ID, lu_Occupations.Description 
AS Occupation, data_links.Person_Category_ID, lu_categories_1.Category AS 
Person_Category, Data_Persons.Person_Communication_ID, 
Data_Communications.Home_Phone, Data_Communications.Home_Phone_Confidential, 
Data_Communications.Work_Phone AS Person_Work_Phone, 
Data_Communications.Work_phone_confidential AS 
Person_Work_phone_confidential, Data_Communications.email AS Person_email, 
Data_Communications.Fax AS Person_Fax, Data_Communications.email_Confidential 
AS Person_email_Confidential, Data_Communications.Fax_Confidential AS 
Person_Fax_Confidential, Data_Communications.internet AS Person_internet, 
Data_Communications.internet_Confidential AS Person_internet_Confidential, 
Data_Communications.mobile AS Person_mobile, 
Data_Communications.mobile_confidential AS Person_mobile_confidential, 
Data_Associates.Member_Type_ID, Data_Associates.Inactive, 
Data_Associates.DueBack, Data_Associates.HunterGP, 
Data_Associates.Email_Delivery, Data_Associates.Deleted AS Associate_Deleted, 
Lu_Networks.Network_name, data_memo.Memo, 
data_GP_PersonSpecific.QA_Ref_Number, data_GP_PersonSpecific.Interests, 
data_GP_PersonSpecific.Experience_ID, data_GP_PersonSpecific.full_Time, 
Data_Gp_PracticeSpecific.Position_ID, Data_Gp_PracticeSpecific.Sessions, 
Data_Gp_PracticeSpecific.Payment_To, Data_Gp_PracticeSpecific.Payment_Method, 
lu_Occupations_1.Description AS Position, 
Data_GeneralPractice_PracticeSpecific.Pooled_Hours, 
Data_GeneralPractice_PracticeSpecific.Signed_Agreement
FROM (((((((((((((((Data_Addresses RIGHT JOIN data_links ON 
Data_Addresses.Address_ID = data_links.Address_ID) LEFT JOIN 
Data_Organisations ON data_links.Organisation_ID = 
Data_Organisations.Organisation_ID) LEFT JOIN Data_Persons ON 
data_links.Person_ID = Data_Persons.Person_ID) LEFT JOIN Data_Associates ON 
data_links.Associate_ID = Data_Associates.Associate_ID) LEFT JOIN Lu_Networks 
ON data_links.Network_ID = Lu_Networks.Network_ID) LEFT JOIN data_memo ON 
data_links.Memo_ID = data_memo.Memo_ID) LEFT JOIN data_GP_PersonSpecific ON 
data_links.GP_PersonSpecific_ID = 
data_GP_PersonSpecific.GP_PersonSpecific_ID) LEFT JOIN 
Data_Gp_PracticeSpecific ON data_links.GP_PracticeSpecific_ID = 
Data_Gp_PracticeSpecific.GP_PracticeSpecific_ID) LEFT JOIN lu_Occupations AS 
lu_Occupations_1 ON Data_Gp_PracticeSpecific.Position_ID = 
lu_Occupations_1.Occupation_ID) LEFT JOIN data_Communications AS 
data_Communications_1 ON data_links.Organisation_Communication_ID = 
data_Communications_1.Communication_ID) LEFT JOIN Data_Communications ON 
Data_Persons.Person_Communication_ID = Data_Communications.Communication_ID) 
LEFT JOIN Data_Communications AS data_Communications_2 ON 
data_links.Employee_Communication_ID = 
data_Communications_2.Communication_ID) LEFT JOIN lu_Occupations ON 
data_links.Person_Occupation_ID = lu_Occupations.Occupation_ID) LEFT JOIN 
lu_categories ON Data_Organisations.Category_ID = lu_categories.Category_ID) 
LEFT JOIN lu_categories AS lu_categories_1 ON data_links.Person_Category_ID = 
lu_categories_1.Category_ID) LEFT JOIN Data_GeneralPractice_PracticeSpecific 
ON data_links.GeneralPracticeSpecific_ID = 
Data_GeneralPractice_PracticeSpecific.GeneralPracticeSpecific_ID
WHERE (((data_links.Deleted)=False) AND ((data_links.Organisation_ID)<>0) AND 
((lu_categories.Category)<>"General Practice"))
ORDER BY Data_Organisations.Description, data_links.Organisation_ID, 
data_links.Address_ID, Data_Persons.Surname, data_links.Person_ID;
=================================================

>
> 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

employees.png
Description: PNG image

linktable.png
Description: PNG image