Here is how I am going to handle mapping microbiology susceptibility in CDMv4.
As discussed there is the isolate (tissue sample) and one or more antibiotics with a result for each antibiotic. For every set of tests, I am creating a single procedure record. The procedure type will be ‘Culture Sensitivity’ concept id 4170475. The procedure concept id will refer to the sample being tested, for example ‘Staphylococcus aureus’ (4149419). Then in the observation table create a record for each antibiotic where the Observation Type is the sample being tested, the Observation is the antibiotic and the result is either Resistant or Sensitive.
Then to find the observations for sensitivity of any isolate to antibiotics for the patient in visit 123
SELECT *
FROM procedure_occurrence proc
JOIN observation obs
ON obs.observation_type_concept_id = proc.procedure_concept_id
AND obs.visit_occurrence_id = proc.visit_occurrence_id
WHERE procedure_type_concept_id = 4170475 – Culture Sensitivity’
AND procedure.visit_occurrence_id = 123