<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en_US" lang="en_US">
<head>
<title> DbStructure < Gnumed < Foswiki</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <meta name="robots" content="noindex" /> <link rel="alternate" type="application/rss+xml" title="RSS Feed" href="WebRss.html" />
<link rel="icon" href="../rsrc/System/ProjectLogos/favicon.ico" type="image/x-icon" /> <link rel="shortcut icon" href="../rsrc/System/ProjectLogos/favicon.ico" type="image/x-icon" />
<link rel="alternate" href="http://wiki.gnumed.de/bin/edit/Gnumed/DbStructure?t=1362919413" type="application/x-wiki" title="edit DbStructure" />
<meta name="description" content="DbStructure" />
<!--[if IE]></base><![endif]-->
<style type="text/css" media="all">
@import url('../rsrc/System/SkinTemplates/base.css');
</style>
<style type="text/css" media="all">
@import url('../rsrc/System/SkinTemplates/default.css');
</style>
<!--[if IE]><style type="text/css" media="screen">
pre {
overflow-x:auto;
padding-bottom:expression(this.scrollWidth > this.offsetWidth ? 16 : 0);
}
</style>
<![endif]-->
<meta name="foswiki.PUBURL" content="http://wiki.gnumed.de/pub" /> <!-- PUBURL -->
<meta name="foswiki.PUBURLPATH" content="/pub" /> <!-- PUBURLPATH -->
<meta name="foswiki.SCRIPTSUFFIX" content="" /> <!-- SCRIPTSUFFIX -->
<meta name="foswiki.SCRIPTURL" content="http://wiki.gnumed.de/bin" /> <!-- SCRIPTURL -->
<meta name="foswiki.SCRIPTURLPATH" content="/bin" /> <!-- SCRIPTURLPATH -->
<meta name="foswiki.SERVERTIME" content="10%20Mar%202013%20-%2013:43" /> <!-- SERVERTIME -->
<meta name="foswiki.SKIN" content="twikinet%2c%20pattern" /> <!-- SKIN -->
<meta name="foswiki.SYSTEMWEB" content="System" /> <!-- SYSTEMWEB -->
<meta name="foswiki.TOPIC" content="DbStructure" /> <!-- TOPIC -->
<meta name="foswiki.USERNAME" content="KarstenHilbert" /> <!-- USERNAME -->
<meta name="foswiki.USERSWEB" content="Main" /> <!-- USERSWEB -->
<meta name="foswiki.WEB" content="Gnumed" /> <!-- WEB -->
<meta name="foswiki.WIKINAME" content="KarstenHilbert" /> <!-- WIKINAME -->
<meta name="foswiki.WIKIUSERNAME" content="Main.KarstenHilbert" /> <!-- WIKIUSERNAME -->
<meta name="foswiki.NAMEFILTER" content="%5b%5cs%5c*%3f~%5e%5c%24%40%25%60%22'%26%3b%7c%3c%3e%5c%5b%5c%5d%23%5cx00-%5cx1f%5d" /> <!-- NAMEFILTER --><!--JQUERYPLUGIN::FOSWIKI::META-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/jquery-1.4.3.js'></script><!--JQUERYPLUGIN-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/plugins/livequery/jquery.livequery.js'></script><!--JQUERYPLUGIN::LIVEQUERY-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/plugins/foswiki/jquery.foswiki.js'></script><!--JQUERYPLUGIN::FOSWIKI-->
<script type='text/javascript' src='../rsrc/System/JSTreeContrib/jquery.jstree.js'></script><!--JQUERYPLUGIN::JSTREE-->
</head>
<body class=""><div class="foswikiPage">
<a name="PageTop"></a>
<p></p>
<p></p>
<h1><a name="Principles_of_GNUmed_information_storage_45_Reasoning_behind_DB_design"></a> Principles of GNUmed information storage - Reasoning behind DB design </h1>
<p></p> <ul>
<li> the database schema is <a href="DatabaseSchema.html">here</a>
</li></ul>
<p></p>
<h2><a name="General_Concepts"></a> General Concepts </h2>
<h3><a name="A_1._Focus_on_clinical_information_for_GPs"></a> 1. Focus on clinical information for GPs </h3>
<p></p>
The first and foremost thing to keep in mind is that the whole DB is
designed to properly store: <strong>GP level care medical data</strong>
<p></p>
<h3><a name="A_2._Client_40presentation_mode_41_independence"></a> 2. Client (presentation mode) independence </h3>
<p></p>
Information is stored independently of how it is going to be displayed.
<p></p>
<h3><a name="A_3._Client_45to_45database_version_matching"></a> 3. Client-to-database version matching </h3>
<p></p>
Each version of the client will connect only to a particular version of the GNUmed database, whose structure (its schema, as reflected by its hash) must be valid.
<p></p>
The above remains true <em>except</em> when the GNUmed client is run in <code>-- debug</code> mode. People should not be using <code>--debug</code> mode in a production environment except when:
<p></p> <ul>
<li> they are doing so selectively, perhaps as directed, as part of further investigating a particular bug and/or
</li> <li> no mismatching versions of the GNUmed database are configured to be available to this client and/or
</li> <li> the user has a clear knowledge of which among potentially-multiple database versions to connect to
</li></ul>
<p></p>
<h3><a name="A_4._Data_protection"></a> 4. Data protection </h3>
<p></p> <ul>
<li> always use foreign keys, proper datatypes, check constraints, triggers, and rules to protect the integrity of clinical data
</li> <li> <strong>the database defaults to read only transactions </strong> <ul>
<li> if you simply connect to the database all you have is readonly access regardless of what your table permissions are
</li> <li> if you want to write data you need to set the transaction to read-write: <ul>
<li> <code>set default_transaction_read_only to off</code>
</li> <li> this applies as long as the connection is open
</li></ul>
</li> <li> if you access the database via the GNUmed middleware module <code>gmPG2.py</code> you must explicitely request a read-write connection if you need one
</li></ul>
</li></ul>
<p></p>
<h2><a name="Storage_of_Particular_Types_of_Data"></a> Storage of Particular Types of Data </h2>
<p></p> <ul>
<li> <a href="DbFormsStorage.html">forms</a> to be filled in and printed/faxed/emailed etc
</li></ul>
<p></p>
<h3><a name="A_1._Overview_of_clinical_tables"></a> 1. Overview of clinical tables </h3>
<p></p>
<em>[not complete! Want to add diagram that also shows relationships like 1:n or n:m for the important clinical tables]</em>
<p></p>
<strong>Ancestor table</strong> of all clinical information: <strong>clin_root_item</strong> (is <strong>abstract</strong>, ie it is never used to store information in, rather as a template to derive child tables from it) <br>
<br>
'clin_root_item' table passes inheritance of its fields to the <strong>descendant/children tables</strong> (listed here without the audit "log_" tables): <br>
<p></p> <ul>
<li> allergy
</li> <li> clin_aux_note
</li> <li> clin_medication
</li> <li> clin_narrative
</li> <li> form_instances
</li> <li> lab_request
</li> <li> referral
</li> <li> test_result
</li> <li> vaccination <br> <br>
</li></ul>
<h3><a name="A_2._Freetext_38_codes_47types_47categories"></a> 2. Freetext & codes/types/categories </h3>
<p></p>
The approach is to store freetext (because this is the main type of information for a GP) and enhance that via targeted tables such as the code or type tables (see 3.).
<p></p>
<h3><a name="A_3._Typing_and_coding_of_the_clinical_information"></a> 3. Typing and coding of the clinical information </h3>
<p></p>
<strong>Typing</strong> is used to label clinical data to be of a certain, well, <strong>type of information</strong> regardless of what the content of it actually is. One is then able to query on the type of data. Typing is inherently prone to type-content mismatch.
<p></p>
It is done in two ways:
<p></p> <ul>
<li> All clin_root_item descendents inherit the field soap_cat. In this field one of the fixed <strong>SOAP categories</strong> (S/O/A/P) must be assigned to the row. Some child tables have their soap_cat value fixed by a constraint.
</li></ul>
<p></p> <ul>
<li> <em>Any</em> row in a clin_root_item descendent can be "tagged" by arbitrary numbers of types (many-to-many relation between clin_root_item and clin_item_type via lnk_type2item).
</li></ul>
<p></p>
<strong>Coding</strong>, on the other hand, is concerned with the <strong>content of</strong> clinical <strong>information</strong>. A code is a replacement or corresponding value for a term/group of terms, within the constraints of a coding system. Any narrative field in any table can be "coded" in any number of coding systems. Codes are not directly linked to narrative terms in any given table. They only represent the same content by means of the term associated with them in the coding table being identical to the given narrative. So, getting codes for a term is active, on-demand.
<p></p>
<em>IOW, a type is an attribute of the content while a code <strong>is</strong> (represents) the content - expressed in the language of the "code".</em>
<p></p>
<em>Remark</em>: SOAP categories <br>
They are viewed as data origin or data certainty types, rather than arbitrary types of the clinical content. IOW, any clinical content can be categorized into the SOAP schema. Don't be fooled by the stock English meaning, rather view the categories as something like levels of certainty or types of information:
<p></p> <ul>
<li> S: this was reported (<em>Subjective</em>)
</li> <li> O: this was found (<em>Objective</em>)
</li> <li> A: this was thought of it (<em>Assessment</em>)
</li> <li> P: this was (intended to be) done (<em>Plan</em>)
</li></ul>
<p></p>
<h3><a name="A_4._Inheritance_from_clin_root_item"></a> 4. Inheritance from clin_root_item </h3>
<p></p>
There are many clinical tables like vaccination, allergy, lab_request (see 1.). All those are more specialized, enhanced
clinical tables going beyond (but often including) free text narrative. Some enhancing tables are still missing, of course, such as medication, etc.
<p></p>
The way to look at the schema for clinical stuff is this (or <strong>"Where put what clinical information?"</strong>):
<p></p> <ul>
<li> anything that is a clinical entity which cannot be split further without losing medical meaning (ie atomic) is put into a child table of clin_root_item
</li></ul>
<p></p> <ul>
<li> the clin_root_item table provides basic fields all clinical items shall have, eg auditing, encounter, episode, soap category and a narrative field
</li></ul>
<p></p> <ul>
<li> clin_root_item child tables enhance the simple narrative with more clinical fields
</li></ul>
<p></p>
<em>Example:</em> Vaccination table (one of the children of clin_root_item)
<p></p>
<pre>
encounter episode (-- optionally --> health issue)
\ /
clin_root_item --> type(s)
--------------
- soap_cat
- narrative
^
|
(inherits)
|
vaccination
-----------
- additional fields
- ...
/ \
vaccine schedule
</pre>
<p></p>
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 (don't get confused, there is a narrative field in the clin_root_item table which is consequently inherited to its children. But there is also a clin_narrative child table which actually then uses that field to store freetext).
<p></p>
Clinical narrative is thereby purposely aggregated, and not scattered across several tables. A key factor for easing full text searches!
<p></p>
<h2><a name="PostgreSQL_vs_other_databases"></a> PostgreSQL vs other databases </h2>
<p></p>
The project built itself around Postgresql on account of Postgresql strengths including its referential integrity, support of inheritance, triggers, and a number of other considerations. While some would argue that software could and should be able to work "with any back end", GNUmed would need to be rewritten to do so and, in the process, would lose both functionality and some of its existing clinical data safeguards.
<a name="TopicEnd"></a>
<p></p>
<p></p>
<p></p>
<p></p>
</div>
</body></html>
