Re: [Gnumed-devel] family history table

[Richard Terry]

snip.....

> The disadvantage is to have to jump through some hoops when
> searching the clinical record.

Why? The fundamental thing about database is that normalising shouldn't make 
any difference. The query across any number of tables just pulls out a single 
recordset.

> Another problem is that one may
> want to store FH for "Martha, aunt" for several people while
> all the while it's a quite different Martha. Hence we might
> still have several "Martha, ..." in the table while actually
> they are all the same Martha. 

>We could solve that by putting a
> unique constraint on name_relative so that people would have
> to write "Martha (of Duncan family branch)" to separate the
> Marthas.> The advantage of "my" way of storing FH is that the
> triple-Martha issue does not arise.

Remember in my table structure the linkage is between the Patient identifier 
and the family member ID, so it is irrelevant how many Marthas are in the 
system who are 'aunt', they can only be linked to the correct patient!. The 
beauty of sql.

> The other advantage is
> that FH is immediately available for searching the EMR.

The family history is instantly available using both my schema and my screen 
design. You never have to search the EMR to view the patients family history 
at all - never. It is there for constant display every time you boot up the 
patient. Also, BTW I use this feature many many times every single day of my 
consultating.

I hear your comment below, however I think there is no excuse for bad table 
design, and that is what has troubled me about much of the table design on 
gnuMed (before you jump on me and say where, the reality is I can't currently 
remember which ones trouble me).

Though I concede there can be situations where not normalising the table is 
acceptable, I don't beleive this is one of them.

We have experienced just this problem in AU, with medical director, where lack 
of normalisation has led to big problems. It is just poor design. In the 
early days of doing project work with MDW when I worked in the division of GP 
we used to pull our hair out because of the lack of normalisation.

The example you give below is not unreasonable, however any good program 
should have a user friendly gui-screen which will enable the user by a drop 
down box tor text box entries generate queries which they can thenbe  stored.

In my contact manager program I instuted this, and saved the queries back to 
the database, each of which was then always present to be chosen from a list 
to generate a report to be printed/exorted etc. That way the user can select 
anything   eg all GP's working part time in street starting with R, who are 
paid by cheque, male with one left hand (yeah - it searched the memo field as 
well). The user need have no knowledge whatsoever of SQL.

Regards

Richard


> Assume I hear there's a new treatment in town for Chorea
> Huntington. Now I want to find all of my patients for whom I
> have ever noticed down anything of relation to that condition.
> I obviously also want to find those that have a *family
> history* of CH ! If the family history condition is stored
> directly keyed to the patient (eg my way) I can just search
> the clinical narrative and find it. If it is stored out of
> line (eg your solution) I will have to use a dedicated query
> to take account of that fact. Sure, one will cry out "Now,
> that's the beauty of SQL that you indeed *can* do that !"
> However, we aren't going to know ahead of time *which* dedicated
> queries the user will need down the road. One solution is to
> offer a free-form SQL searcher to users (we do). Another is to
> somehow integrate the information with the clinical narrative
> of the patients.
>
> What do people think about the following:
>
> 1) Normalize tables as Richard suggests.
> 2) When inserting/updating family history in his out-of-EMR
>    table fire a trigger to insert/update a particular
>    narrative item in the corresponding patients' EMRs to
>    "relationship (name): condition"
> 3) Rejecting direct inserts/updates to that narrative so it
>    cannot get out of sync.
>
> Sounds like a plan to me. Suggestions ?
>
> So much for "dumb backend" :-)
>
> Karsten