Re: [Gnumed-devel] Collating basic information on GNUmed DB structure

[Karsten Hilbert]

Hi Thilo,

I heartily welcome your intent to document.

> I will share the compiled doc in the wiki.
You might compile it right inside the wiki.

> I want to start out with the reasoning behind the DB
> structure. Just in principle and what it is able to record.
The first and foremost thing to keep in mind is that the DB is
designed to properly store

 *GP level care* medical data

This relates to the types of information it currently stores.
It then tries to

 store data independant of how to display it

> For example as from what I have seen most of the clinical
> information is stored as freetext.
You would need to ask why. The answer is that a lot of
clinical information in a GP practice IS freetext.

The approach is to store freetext and enhance that via
targetted tables (such as the code tables).

> The only exception is the diagnosis which can be coded too.
No. *Any* row in a descendant table of clin_root_item can by
"typed" by arbitrary numbers of types. Also *any* row in
clin_narrative can be coded, it need to be a diagnosis. Any
number of codes can be attached to a clin_narrative row. The
distinction between a type and a code is not very good anymore
and at some later codes may get merged into types. Mainly,
codes are "more formal" types, eg those belonging to a coding
system. Also it does not make sense (with conventional) coding
systems to try to code some of the clin_root_item descendant
(because, for example, vaccinations can be such child tables
and which field in that table to you want to attach the code
to ?). It makes sense, however, to attach codes to
clin_narrative rows, eg a piece of free text being coded. OTOH
it also makes sense to be able to type any clin_root_item
descendant such as a vaccination, and be it to just type them
"vaccination". This disctinction is because types are more
generic. Logically, this means that codes are just more
specialized types which I only realized a short time ago. So,
at some point codes might get merged into types.

Now, you'll wonder why some types are handled explicitely at
the clin_root_item level ? Those are the SOAP categories... I
few them as data types rather than arbitrary types of the
clinical content. IOW, any clinical content can be categorized
into SOAP (don't be fooled by their stock English *meaning*,
rather view them as something like levels of certainty or types
of information, like "this I got told, this I found, this I
thought and this I did/intend to do").

Now, we also have vaccinations, we also have allergies, lab
request, lab results. All those are more specialized, enhanced
clinical tables going beyond (but often including) free text
narrative. Some enhancing tables are missing, of course, such
as family history, past history, etc.

The way to look at the schema for *clinical* stuff is this:

- anything that is a clinical entity which cannot be split
  further without loosing meaning is put into a child table of
  clin_root_item

- the clin_root_item table provides basic fields all clinical
  items shall have, eg auditing, encounter, episode, soap
  category and a narrative field

- clin_root_item child tables enhance the simple narrative
  with more clinical fields



encounter                   episode (-- health issue)
         \                /
           clin_root_item -- type
           --------------
           - soap_cat
           - narrative
               |
               |
           vaccination
           -----------
           - additional fields
           - ...
        /                     \
 vaccine                       schedule


All clinical item tables have this structure. You can overlay
them at the clin_root_item junction. The top part will always
stay the same, the bottom part will always be different.

One reason is to aggregate all clinical narrative and not
scatter it across several tables.


> I took a look at the schema generated by autodoc. Have you
> ever tried to display the graphical output?
No as I don't have any of the software to display it. But feel
free to provide me with information on how to generate it and
I'll feel happy to put it up on the server.

> Have you ever investigated webdot?
No. Feel free to suggest something.

> I have read that the DIA diagrams have the problem that all
> the tables are stacked on each other.
Horst said so, yes.

> Please let me know if you know about any floating informaton pieces about the 
> the DB like 
> http://savannah.gnu.org/cgi-bin/viewcvs/*checkout*/gnumed/gnumed/gnumed/client/doc/developer-manual/db-architecture.html
>  .
> Please also tell me how up-to-date the information is.
No, please tell us the URLs and we'll tell you how current the
information is.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346