gnumed-doc 1.1.7-1 / usr / share / doc / gnumed / user-manual / GmManualReferenceData.html

<h1><a name="Reference_data"></a>  Reference data </h1>
<p />
In some cases, it is enough to reference external data (like a web page) via a link. GNUmed provides for these in a variety of menus and configurable buttons.
<p />
In other cases, it is necessary to import reference data into GNUmed, to enable it to form part of the patient record (medication use, diagnostic and therapeutic codes, etc.).
<p />
While such importing can be done manually by using such tools as psql (together of course with the gm-dbo password), GNUmed release 1.0 brought support for scripted data packs.
<p />
<div class="twikiToc"> <ul>
<li> <a href="#Data_packs"> Data packs</a>
</li> <li> <a href="#Overview_of_reference_data_const"> Overview of reference data constraints and requirements</a> <ul>
<li> <a href="#packaging_Medication_reference_d"> packaging Medication reference data</a>
</li> <li> <a href="#altering_Medication_names"> altering Medication names</a>
</li></ul> 
</li></ul> 
</div>
<p />
<h2 class="twikinetRoundedAttachments"><span class="twikinetHeader"><a name="Data_packs"></a> Data packs </span></h2>
<p />
Data packs are GNUmed's way to make available a wide variety of reference data on an as-needed, on-demand basis. By such means,
<p /> <ul>
<li> there is no need to pre-load the GNUmed database with potentially-unwanted reference information
</li> <li> downloads can be reduced in size
</li> <li> easy one-step installation and updates, via GNUmed menu, which any GNUmed praxis administrator (armed with the gm-dbo password) can do
</li></ul> 
<p />
Currently-available data packs can
<p /> <ul>
<li> install Canadian drug brands and their ingredients and populate the vaccines table
</li> <li> fill in missing Anatomical Therapeutic Chemical (ATC) classification codes for drug names which match the INN names or mapped synonyms
</li> <li> modify the (upper / lower) case of drug names using the TALLman system of better-distinguishing sound-alike drugs
</li> <li> hosted here: <a href="http://www.gnumed.de/downloads/data/" rel="nofollow" target="_top">data packs</a>
</li></ul> 
<p />
Configuration file in which these packs are referenced:
<p /> <ul>
<li> <a href="http://www.gnumed.de/downloads/data/data-packs.conf" rel="nofollow" target="_top">data pack list</a> configuration file
</li></ul> 
<p />
General data pack preparation instructions:
<p /> <ul>
<li> put all the SQL into a file called <code>install-data-pack.sql</code>
</li> <li> create a zip file with an appropriate name which contains the <code>install-data-pack.sql</code> file at the top level
</li></ul> 
<p /> <ul>
<li> SQL file cannot use psql-level <code>\copy</code>
</li> <li> SQL level <code>COPY</code> cannot be used either because: <ul>
<li> the data file would have to reside on the server machine
</li> <li> the data file would have to be readable by the server demon user
</li> <li> connection as superuser (postgres) would be needed
</li></ul> 
</li> <li> do <strong>not</strong> modify the database schema outside of schema <code>staging.</code> - the data pack will fail
</li> <li> you may use schema <code>staging.</code> as needed
</li> <li> <code>DROP</code> your staging tables first, wrapped in <code>\unset ON_ERROR_STOP ... \set ON_ERROR_STOP 1</code>
</li> <li> then <code>BEGIN</code> a transaction
</li> <li> then (re-) <code>CREATE</code> your staging tables and do all the work including transferring data to production tables
</li> <li> then <code>commit</code> the transaction
</li> <li> then <code>DROP</code> your staging tables, again wrapped in <code>\unset ON_ERROR_STOP ... \set ON_ERROR_STOP 1</code>
</li> <li> the above can be repeated within one <code>install-data-pack.sql</code> if several chunks of self-sufficient data is available (say, ATC codes and vaccines)
</li> <li> the data pack must be re-runnable without any adverse effects, regardless of whether already having been run or not, and whether having ended in it failing, or not - that means it must <strong>check</strong> for existence first before <code>INSERT/UPDATE</code> of data
</li></ul> 
<p />
<h2 class="twikinetRoundedAttachments"><span class="twikinetHeader"><a name="Overview_of_reference_data_const"></a> Overview of reference data constraints and requirements </span></h2>
<p />
<h3><a name="packaging_Medication_reference_d"></a> packaging Medication reference data </h3>
<p />
Medication reference data may include, from potentially multiple sources:
<p /> <ul>
<li> proprietary or <em>brand</em> data (this may include branded vaccines) <ul>
<li> stored in <code>ref.branded_drug</code>
</li></ul> 
</li> <li> active ingredient or <em>consumable substance</em> information <ul>
<li> stored in <code>ref.consumable_substance</code> where the row must be distinct along three attributes {description, strength, units}
</li></ul> 
</li></ul> 
<p />
Branded drugs gets foreign-key referenced in two directions. Most commonly-used will be the link <code>ref.lnk_substance2brand.pk</code> by which means the consumption of a brand can be linked to a patient's record of substance_use. The second direction is that of vaccines, where the brand key is directly referenced in the table of vaccines clin.vaccine.
<p />
You can track the source of your praxis' brands data by use of ref.branded_drug columns <code>fk_data_source</code>, <code>external_code</code> and <code>external_code_type</code>.
<p />
Clinical records capture, in the case of each substance used by a patient, needs a minimum of the consumable_substance that is being used or, in the case of a branded drug, a link to the brand. The brand is a much more manageable way to capture a drug combination (whose parts cannot be taken independently of each other) and brands are a convenient way to capture drugs that are being supplied and taken in multiple strengths, for example, a triphasic oral contraceptive pill.
<p />
When reference data wants updating, it must further be taken into account that emptying the branded drug and consumable substances tables may be inadvisable <em>even</em> when their records not been used (hence bound by foreign keys), because these tables may also be holding: <ul>
<li> in the case of <code>ref.branded_drug</code> &#8230; <ul>
<li> multiple instances of an <code>is_fake</code> 'generic vaccine' to cover a variety of conditions when the actual preparation was unknown (but, having said this, GNUmed comes with a
</li></ul> 
</li></ul> 
function to re-create those should any happen to get removed) <ul>
<li> in the case of <code>ref.consumable_substance</code> &#8230; <ul>
<li> a variety of non-prescription (and potentially unapproved-of) substances
</li></ul> 
</li></ul> 
<p />
Having said that, if it should be desirable to clean out brands data, the above-described ref.branded_drug columns provide the means to keep clear the records of interest.
<p />
When aiming to remove brands, it may help to point out that <ul>
<li> rows in <code>clin.substance_use</code> which reference a brand key to it indirectly, via fk ref.lnk_substance2brand
</li> <li> rows in <code>clin.substance_use</code> need not reference a brand &#8211; they can alternately reference a single substance (irrespective of brand) from column fk_substance; this provides a basis to unlink a clinical record association from a brand where that might be appropriate. One example might be where a pharmacy had post-hoc substituted an unknown brand for whatever had been recorded in GNUmed. Post hoc auditing of the GNUmed record to reflect the limit of the known information could be reasonable but would be available only in the case of brands which had a single active-substance.
</li> <li> rows in <code>clin.vaccination</code> will have referenced clin.vaccine.pk, which itself references ref.branded_drug.pk (moreover, clin.vaccine.pk will exist represented in clin.lnk_vaccine2inds and clin.vaccine_batches) and so all of these referential integrity contraints would need to be met to remove brands which had been linked as vaccines
</li></ul> 
<p />
When adding brands, the job is not completed until: <ul>
<li> those brands which are vaccines are inserted into clin.vaccine, whose columns 'id_route' and 'is_live' (and 'fk_brand') cannot be NULL, and
</li> <li> non-vaccine brands (and optionally vaccines) are indirectly linked to their component substance(s) &#8211; which may or may not already exist &#8211; via one or more links in ref.lnk_substance2brand 
</li></ul> 
<p />
<h3><a name="altering_Medication_names"></a> altering Medication names </h3>
<p />
This can be done in-place, for example:
<p /> <ul>
<li> fixing spelling mistakes
</li> <li> when a drug has been withdrawn from the market as unsafe (rename it to: "DO NOT PRESCRIBE: orginal-drug-name")
</li> <li> applying the <a href="http://www.ismp.org/Tools/tallmanletters.pdf" rel="nofollow" target="_top">TALLman</a> approach to improving drug safety, which uses letter case to make similar drug names more distinct (and for which GNUmed may by this time have a data pack available)
</li></ul> 
<hr />
<p />
<hr />