How is bacteriology data for each specimen grouped together?

Software Bahmni
1

We are trying to write some sql for patient queues that summarise whether for a specimen collected for bacteriology a culture result has been received. These should be grouped by the specimen. However, we are having difficulty working out in the data model how obs are associated with each specific instance of the bacteriology form being filled out.

Let’s say we enter the specimen collection date for a sample in Bahmni with a value of 30/06/2017. Then we also add a culture result. In the obs table we will have a row for the specimen collection date and a row for the result. What we are unable to workout is how these are then linked together (and displayed in the same instance of the bacteriology form in Bahmni).

The encounter_id doesn’t seem specific enough since you could have more than one specimen collection during a single encounter (ie by filling in two instances of the bacteriology form). The obs_group_id seems to be about organising obs that are contained within a set inside an obs template.

So is there an additional table for bacteriology that stores all of the obs_id values for observations that are associated with each instance of the bacteriology form being populated. And if so what is the common value/id that it uses to group them together?

More broadly than the bacteriology module, is there a similar approach for grouping together the obs for each instance of an obs template that is filled? For example, if entering data retrospectively for lab results, we might have two instances of the lab results with different specimen collection dates but the same encounter id because data entry was done at the same time. How does the data model (and Bahmni) recognise these as two distinct instances of the obs template and associate the relevant obs data with each instance?

Apologies too if I’ve posted this in the wrong part of the forum - I wasn’t sure whether this should be Bahmni specific or belongs in a different area because it also relates to the OpenMRS data model.

2

An update. We managed to work through this. I thought I’d post in the event that what we found might be useful for others.

The answer we found is that it is in fact the obs_group_id. When an instance of the obs template is filled in, a row is added to the obs table with the concept_id for the obs template (concept set).

Observations for the concepts within that obs template are then added to the obs table. In the obs_group_id for these observations, the obs_id for the observation with the obs template is stored in obs_group_id. If any of the members of the obs templates are themselves a set, an observation will be added with the concept_id of the set and then the members of that set will be added with the obs_id for the set stored in obs_group_id.

So in short, all of the data is stored in the obs table and the obs_group_id is used to associate observations with the obs_id of a specific instance of their parent concept set.

I’m guessing this might be pretty obvious to those who know the OpenMRS data model very well but it wasn’t initially that intuitive since we’ve not been working with it for that long yet.