Test Results handling/hacking

See also Sebastian's LabModule

The fetcher/importer maps the source of the results to the correct test_org.pk, probably it would always already exist unless the labs were received through a service bureau to which a new lab had subscribed. If there were difficulty verifying which test_org.pk a log alert would need to be generated for a human user.

Having established the test_org.pk the importer compares an incoming data file to the test_type table. If GNUMed cannot find within the table a match on (fk_test_org, code, [and, if not NULL, the coding_system]) GNUMed could create the extra rows in test_type to hold the new values, with the default NULL for coding_system replaced by something to designate that org's arbitrary system, and could be allowed to populate conversion_unit with whatever has been provided by the lab as the unit accompanying that result.

In the event that a lab re-uses a code in its new arbitrary coding_system, or the chance that a numeric code, which was used under an old coding system for one test, happens to also exist in a new coding system for a different test, we now have the constraint unique(fk_test_org, code, coding_system) in place of unique(fk_test_org, code).

Conversion_unit defines a base line against which to calculate"exchange rates" for results of the same test coming in with different units (which are stored per result). It represents a reference by which to calculate conversions, operated on with tools of which Karsten is aware which know how to deal with dimensioned values. It does not care or alter which unit the lab attaches to the actual results flowing in, which are stored with the value in test_result. This helps to avoid the problem described when a lab changes values e.g. at http://emruser.typepad.com/canadianemr/2004/07/changes_in_lab_.html

A resource for conversion factors: http://jama.ama-assn.org/content/vol292/issue1/images/data/112/DC6/JAMA_auinst_si.dtl

It is not felt that (code, coding system, conversion_unit) should be unique across multiple test_orgs that use the same (code, coding system).

Conversion_unit is also useful to (potentially) be copied (or offer to be copied) into test_results for measurements (created in test_type) that are performed within (or manually input within) the office. As it is possible that weight could be measured (or stated by the patient to be) in either kg or pounds, and height can be in inches or cm or m, conversion_unit should be offered as the default where appropriate but require user confirmation.

Test type local:

the constraints are

unique(local_code, local_name) unique(fk_test_type, local_code)

eg: a) each local_code can only ever be associated with one local_name (eg. long-short name combinations are unique) b) each test_type can only once be associated with a given local_code (and thus local_name)

Over in the table test_type I may have the random and fasting glucose level types from 2 different labs (suppose these are test_org_pk 6 and 7)


The above meets the schema requirement unique (fk_test_org, code).

Since some other coding_system could happen to use the same code for a different purpose, we do not require "code" by itself to be unique but unique(fk_test_org, code, coding_system). Two organizations might choose and be permitted to name a combination of CODE with CODING SYSTEM differently.

So now, moving to the table test_type_local, I can have

1           / GLUC [F]   /  Glucose, fasting
2           / GLUC [F]   /  Glucose, fasting
1           / GLUC [S/B/P]   /  Glucose, serum/blood/plasma (any)
2           / GLUC [S/B/P]   /  Glucose, serum/blood/plasma (any)
3           / GLUC [S/B/P]   /  Glucose, serum/blood/plasma (any)
4           / GLUC [S/B/P]   /  Glucose, serum/blood/plasma (any)

This would permit me to select for display or analysis (view: v_results4lab_req, constrained to lab results) - any patient's fasting glucoses (irrespective whether done at lab 6 or 7) - any patients' blood or serum or plasma glucoses (but not urine or CSF glucoses)

If there are 100 tests that I commonly use, serviced by 5 local test_orgs then I would have to have at least 100 x 5 = 500 codes that I have to create myself in test_type_local. Thank brach labs are coverd under their org! However, you don't have to create anything in test_type_local. If there isn't anything in there for a test_type, the views will happily create virtual rows where the unified name is the same as the original name...

If all test_orgs in one's district were to use the same CODING_SYSTEM the (coding-system, code) e.g. LOINC 14769-4 could unify results of interest.

A special advantage of going to all that extra trouble with test_type_local would be when one or more test_orgs use a DIFFERENT CODING_SYSTEM

It is not to aggregate different tests for viewing purposes for example COAGS = INR, PTT and PLATELETS LIVER = AST ALT GGT ALK BILI INR HEPA HEPB HEPC CARDIO = CK TROP ANP GLUC CHOL

This is explicitly NOT the purpose of test_type_local. This area is what Liz is eventually working on and which will show up under "profiles" (no tables there yet). > "Profiles" are meant to organize the aggregation of combinations of test results for viewing, but could also serve test ordering. - think clinical, eg. Hep Screening, kidney profile, inflammatory bowel disease monitoring, etc.
Topic revision: 30 Sep 2004, JamesBusser
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback
Powered by Olark