In Bahmni, we provide the doctors with an ability to mark an observation as abnormal. It can be marked automatically if it falls out of the normal range and the doctor can choose to override it.
Currently we store it as an obs group. As of now, while creating forms for an implementation, the implementer has to create a concept set with Abnormal being a concept as part of that set. The concept set also has to be of class Concept Details.
With form builder, we are looking to simplify this complex model of storing the data. We would ideally like to have “Abnormal” to be a property of the concept which can be set true/false while designing the form in the form builder.
I have been unhappy that we model our data structure this way unnecessarily for capturing something like ‘abnormal’ flag (or anywhere we need to capture additional details of observation captured). This not just complicates interpretation of the data (always becomes app specific and you need to know the nested structure), but also causes problem for reporting.
Example: ‘Find cases when a persons Hb level is abnormal’ - this is queried by ‘find observations for concept = ‘Hb’, look for a child obs with ‘abnormal’ concept value of which is true’! Imagine correlating that with diagnosis of ‘Anemia’.
While the OpenMRS Obs model gives tremendous flexibility (for good or worse), I think for such things we should have a mechanism of simplifying the data structure.
In this specific case, I can think of extending the obs model to have a ‘abnormal’. IMHO that obs.abnormal is generic enough to be introduced into the model. There are cases like ‘Diagnosis’ or other information model (example a custom obs structure to capture “family history”) where this might not make sense, but that can be defaulted or not have any value.
For the ‘unknown’ or similar attributes: I am not sure. I can only think of extending the obs model as
using another EAV table to capture these info. (may not be such a good idea considering that Obs table is already nested and deep enough and may only hinder performance of querying)
using a JSON data type (like we do in medication order and use a custom serialiser and/or move to MySQL 5.7)
In all, I guess what I am saying is that - simplifying and flattening the obs model for such additional meta information.
@angshuonline the challenge i find with adding abnormal to the obs field is that if we had always followed that pattern, the obs table would now be the most column bloated table, moreover with columns that mostly get default values that do not make sense for most rows of data. I have ever worked on a project that decided to go that route and as a result, many developers started complaining about complexity brought by tables with so many columns that do not always make sense to some data rows.
Generally speaking, providing a data model that combines simplicity, flexibility, ease and performance of reporting, is a hard nut to crack! For now, i have come to realise that reporting may need a transformed structure different from that of flexible storage.
Maybe, it is a high time we again explored the possibilities of using NoSQL for obs.
Agree. Thats why I also mentioned about an additional table as EAV model for additional info on obs.
I was talking from our experience where we have seen ‘abnormal’ flag as a very common requirement in forms captured forms.
Regarding complexity, I think the current “pattern” is worse (not advocating for more and more columns). We setup and capture data in “obs group”, which is extremely difficult to interpret and maintain and migrate to another structure when situation warrants. This also breaks our reporting!
One way, you could say is use a Complex Obs handler and store as JSON, but then its not queryable. Hence my advocacy for mysql 5.7 JSON type. Imagine an additional table mapped with the obs, which has a single column which has a JSON type.
Not if you create multiple tables (1:0 relationships) with specific attributes for each kind of concepts. It’s basic database design. I agree with Anshuman that the current model is so generic that makes easy queries and reports very complex (and hard to understand for the profane). At the very least I’d use views (with hardcoded concept ids), either normal or materialized.
Would love to schedule some design time. Here are the next available open slots. Would one of these work for you @dkayiwa@angshuonline? Is there anyone else required for this call?
Wednesday, November 30 from 7-8pm UTC
Monday, December 5 from 4-5pm UTC (day before OMRS16 so attendance will be light)
I agree with trying to stay close to FHIR in this case, as @burke suggests. As I was reading this thread I considered the most common flags that appear next to results:
For most numeric results, High, Low, Very/Critically High (typically shown as “HH” as FHIR uses), Very/Critically Low
For culture sensitivity MICs (a numeric way of expressing how good the antibiotic is): Sensitive, Resistant, Intermediate
For immunological tests (mainly for vaccinations and infection detection): Reactive, Non-Reactive
For some tests that don’t have numbers (such as rapid strep or influenza tests): Positive, Negative
FHIR appears to have all of these typical annotations covered.
Reporting and clinical decision support tools that wished to find the abnormals would look for H/L/HH/LL.
Reporting that wished to find other important or hazardous info, such as positive Zika tests, would look for Positive or Reactive.
Can this be taken as the “normally abnormal” e.g. transient rises in systolic BP especially in the elderly? Also is there a problem if the observation in this format is misaligned with other observations that when integrated make it a ‘true abnormal’ or ‘true normal’?
HL7 has always included an abnormal flag with observations. In FHIR, it’s the Observation.interpretation. One of the reasons for doing this is because reference ranges can vary by so many factors (ethnicity, gender, age, timing of specimen, lab equipment used, etc.). The notion of “abnormal” is not so much a universal truth; rather, it’s a representation of whether the system reporting the observation believers it should be reported as abnormal.
We didn’t include abnormal flags in the early days of OpenMRS, but, if I had a time machine, I would have included it. The same goes for Observation.status to allow us to properly handle pending results, observations with exceptions (inadequate specimen), etc. If someone could come along and add status and abnormal attributes for obs, I think we would be very happy to add them.
The challenge is the central & often large obs table. Making changes to the obs table isn’t hardly possible for some larger implementations. This is where we need the third leg to this stool: a viable upgrade path for changes to large tables. For example, imagine an upgrade helper module that creates a second obs table with the new attributes and, in a non-disruptive (bandwidth-limited) way, begins copying obs to the new table and keeping any changes in sync (maybe with stored procedures?). A big implementation installs this module and a week later all their data has been copied into the obs2 table, so only a brief downtime is needed for the helper module to replace the original obs with the new populated obs table.
If you can give me (1) an Obs.abnormal, (2) an Obs.status (so status doesn’t get left behind), and (3) a viable upgrade path that doesn’t exclude larger implementations, I’m sold.
I understand why this is important for implementations with huge obs tables, but it’s a tough ask for someone else who does not have such an implementation to do the solutioning for this. Especially to do it in a generic way that’s guaranteed to work across all implementations, regardless of DB and OS versions.
In relational databases with huge tables the solution usually is to use partitions (physically splitting the table in multiple ones, by some column normally dates). Obviously this is a DBA task, can’t be done with liquibase scripts. But I don’t know if MySQL supports partitions.
Another option would be using PostgreSQL if it’s better than MySQL.
As far as i can tell, our obs table is some day going to require something NoSQL like.
Uber had a similar problem for their tripstore table which was growing the fastest, just as we have our obs table.
They modified MySQL to give it some NoSQL capabilities to store data for this biggest table. The rest of the not so big data remained in their original PostgreSQL database.
You can read details at: https://eng.uber.com/mezzanine-migration/
Of course we do not have the capacity to modify a RDBMS. So we may choose an existing NoSQL for obs while leaving the rest intact, as we face the reality of Polyglot Persistence - using different data storage technologies to handle varying data storage needs!
