Obs Table Primer

This documentation is designed to give developers a basic description of how clinical observational data is written to the OpenMRS data repository. This model has been refined significantly since this early version was released. To see the latest version, feel free to view check out the Data Model section of our site.

To start, some basic definitions:

 An observation is any clinical measurement acquired and observable in a clinical 
 setting.  If you use your imagination, just about anything can be an observation.  For 
 example, a hemoglobin test, a patient's weight, an answer to a question, or a physical 
 exam finding.  In short, any piece of data collected in a clinical setting (a patient's 
 demographic information being a notable exception) is considered to be an observation.  
 Note that if a clinician decides to order a hemoglobin, the order itself isn't 
 observational:  this is a clinician action, not something measured or acquired directly 
 from the patient.

 A concept is a means of describing observational information within our system.  
 Inherent within the data model is a collection of definitions for any concept that's 
 collected within the repository:  the concept dictionary.  Each concept is described 
 with a term, which is represented within the term table (and is described in depth elsewhere).

Storing information within the data repository is pretty straightforward, if you understand the underlying database structure. Let's look at this portion of the data model.

Each observation noted during a clinical evaluation is stored as a row within this table. The obs_id is the unique identifier of the table, and should be a simple autonumbered field. Many of the subsequent attributes seen to the right are "meta data" related to the particular visit. patient_id is a foreign key to patient.patient_id, and should include the internal patient identifier number for the patient (not the medical record number). term_id is another foreign key which points to term.term_id and refers to the concept which describes what is being collected by the system. location_id describes where the observation was collected, it will typically be a clinical setting such as a clinic or a laboratory. It has a foreign key which points to location.location_id. More on location_id in a moment. Finally, encounter_id refers to the particular patient visit. As you might assume, many observations are collected during the course of a given clinical visit. Each visit's "meta data" is contained within this encounter table. You might wonder what the point of location_id is, after seeing what is stored within an encounter. In short, observations on a patient can be made outside of an encounter, or outside of the purview of our system. For example, a patient could bring copies of their medical record, or observations could be made during a home visit, etc. Therefore, encounter_id is not a required field.

When building an application, you'll have likely acquired all of this "meta data" before results are collected by your application. How you store the value of the observation, depends of the data type of the concept as defined by it's term definition. You'll notice that there are numerous different fields to store values. You'll only use one of these for each row. Let's walk through each major datatype to demonstrate.

Boolean: On one of the patient encounter forms used in the Eldoret clinics, the following question is asked: "Have you disclosed your HIV status to anyone?" This question has a simple yes or no answer, and is modeled in our dictionary within concept 1048. If you click through the provided link, you'll see that the datatype for this concept is boolean. Because of this, you will store a bit answer within value_boolean.

Coded: Another example, concept 1061. This is another question asked on an encounter form: "How do you think you were exposed to HIV?" There are six possible answers to this question, all of which are ALSO concepts within the system. Click through the link provided for more details. To store the answer for this question, you need to store the term_id for the corresponding concept. Say, in this case, the answer was "through a blood transfusion", which corresponds to concept 1063. You would store "1063" in value_coded. As you might have noticed, value_coded in fact has a foreign key to concept.concept_id to ensure this linkage occurs and maintain integrity.

Datetime: Concept 1113. In this particular example, the OpenMRS encounter form is capturing some historical information related to a previous date in which tuberculosis therapy was started. Note the "date" datatype. The answer to this question is a SQL compliant date, time or datetime. 1/1/2005, 1-1-2005, etc. depending on the database specifics within value_datetime.

Numeric: Let's look at a lab test result, a hematocrit: concept 1015. This test has a numeric answer, like 33 or 45. Store this answer within value_numeric. Most values will be pretty straightforward, like this. What if however, the result is a range, like 2-4, or a value that's "<1" or ">=400"? These more complex numeric answers can be described with the numeric_modifier field. numeric_modifier is an ordinal field, which has a list of all of the needed modifiers, such as ">", "<", "range", etc. To store a simple modifier, write the numeric value within value_numeric, and add the corresponding modifier into numeric_modifier. In the case of ranges, store the mean value of the range within value_numeric, store "range" in numeric_modifier, and the fully described value (for later display purposes) in value_text. This allows the system to use an approximation of the value in more computable ways (such as decision support, etc.), and still store the actual answer for display purposes. Contact us for more details on this.

Here are a few actual examples to provide a finer point.

Patient: Jenny D. Patient MRN: 99TU-2, which corresponds to patient_id: 123 Clinic Vist Date: 1/14/05 @ 2:30pm Location: Mosoriot Medical Center Hemoglobin: 11.5 Weight: 45 kg Date of TB treatment: 1/1/2002 Gastrointestinal Exam: Hepatomegaly...

As you can see, this patient has a number of observations ready for storage. The name, and medical record number aren't important for the obs table, but you can see a patient_id, and a location. You also see a datetime for the visit. The third and fourth bits of information will give you enough information to build an encounter within the encounter table. Mosoriot has a corresponding location_id of "2". Following the demographics are four separate observations to be recorded: hemoglobin, a numeric value; weight, a numeric vital sign; date of TB treatment, a datetime; and gastrointestinal exam finding, a coded answer. If you look at this concept, hepatomegaly is one of the possible answers, and it's the corresponding concept 5008. Knowing all of this, filling in the table is pretty straightforward. Based on the data to the left, you would store 4 rows of data within the obs table with the following information...

obs_id	pat_id	trm_id	lctn_id	encntr_id	sub_id	val_bool	val_cod	val_dt	val_num	num_mod	val_txt	d_e_t	enterer	comment
1	123	21	2	345					11.5			1/15/2005	2
2	123	5089	2	345					45			1/15/2005	2
3	123	1113	2	345				1/1/2002				1/15/2005	2
4	123	1125	2	345			5008					1/15/2005	2

That should be enough info to get folks started. Feel free to send us mail if you'd like this to be explained in more detail!

-Paul