[Gnumed-devel] My Simple Brain - Data Storage - FH+Habits

[Richard Terry]

I've watched and watched the following sort of debate on this list for a long 
time:

==========================================
> 4 is only if we have some alternate mechanism to tag and later 
> subselect certain types of history as Syan was asking, for example I 
> am not sure how we expect to draw out stored info on risk behaviours 
> (EtOH, tobacco, sex & other so-called Social History), likewise 
> Family History if these are all supposed to be entered into 
> clin_narrative
Again, you can attach any number of arbitrarily created tags
to any clin_narrative item. It may indeed be useful to
pre-create a number of "well-established" tags.
=============================================

And I ask myself the same question, again, again and again. Why does it have 
to be so hard? Why not do the obvious (After all I did..... some 7 years ago 
and wrote a functional program in 6 months  - Yeah I know, not bullet proof 
and not client server).

You are not going to be able to 'draw out' certain types of history such as 
drugs, family history etc, unless they are appropriatley entered in a popup 
or stand alone editing area, as they will all have unique types of 
information and some sort of structure will need to be enforced.

Whearas you can allow the user to type in this info into the general SOAP 
stuff, you can then forget being able to do anything useful with it, even 
with clever parsers. What seems to be constantly forgotten, (and at the risk 
of being boring and annoyingly repetative) is that your user is not going to 
do what you think they are going to do. You have to think of all the idiotic 
things they can do and handle all these exceptions, and if you can't 
constrain them, then you have to enforce structure. [Garbage in Garbage out]

The family history question is a good point: Here are my tables, certainly 
different from gnuMed's. Note here to normalise the data a simple single 
table structure will not suffice:

Table: Data_FH_Conditions    
-----------------------------------
Condition_ID                                          Number (Long)             
               
Member_iD                                             Number (Long)             
               
Consult_ID                                            Number (Long)             
               
Condition                                             Text                      
              
Ozcode                                                Number (Long)             
               
Age_of_onset                                          Text                      
               
Cause_of_death                                        Yes/No                    
               
Comment                                               Text                      
              
Deleted                                               Yes/No                    
               

Table: Data_Fh_FMembers 
---------------------------------                                               
                             
Member_ID                                             Number (Long)             
               
Consult_ID                                            Number (Long)             
               
Name                                                  Text                      
              
Date_of_birth                                         Text                      
              
Relationship_ID                                       Number (Byte)             
               
Age_of_Death                                          Text                      
               
Deleted                                               Yes/No                    
               

Table: Data_Fh_Links            
---------------------------                                                     
                
Link_ID                                               Number (Long)             
               
Member_iD                                             Number (Long)             
                        
Patient_ID                                            Number (Long)             
               
Consult_ID                                            Number (Long)             
                        
Deleted                                               Yes/No                    
               

Table:lu_FamilyHistory_Relationships
-----------------------------------------------
Relationship_ID
Relationship    eg mother, father (I have 47 defined)


Now check out the png of the editing area from family history.
Note that the user can enter free text for the relatives name (not enforced), 
they cannot enter free text for the relationship (which the system must store 
as an ID), they are offered picklists for the condition (but free text 
allowed, with spelling validation of their entries), and you must also have 
validation on the age of death etc, otherwise you end up with garbage in your 
database.

You will have to enforce some sort of structure - not only for visible 
accuracy sake but medicolegally.

As for the rest of my tables, most contain the data they are named by e.g


Table: Data_RecreationalDrugs                                                   
                    
Habit_ID                     Number (Long)                            4 
Consult_ID                 Number (Long)                            4
Drug_ID                     Number (Integer)                         2 
qualifier                     Text                                    50
Deleted                     Yes/No                                   1

lu_RecreationalDrugs
Drug_Id                        
Description      e.g tobacco, heroin,amphetamine,alcohol etc

etc etc.

For the un(interested) I enclose a dump of a few of the some hundred odd 
tables in the database. Work well, quick and easy to understand.

Regards

richard

fh_1.png
Description: PNG image

fh_3.png
Description: PNG image

fh_2.png
Description: PNG image

data_alltables_medrec.txt
Description: Text document