As @Christian_Reich pointed out in an earlier discussion, some HCPCS codes map to multiple RxNorm codes. Specifically, when there’s no way to infer the SCD RxNorm code, we get a one-to-many map to every ingredient. This only seems to be the case for a handful of HCPCS codes:
select c.concept_id, c.concept_code, c.concept_name, count(c2.concept_id)
from concept c
join concept_relationship cr on c.concept_id = cr.concept_id_1 and c.vocabulary_id = 'HCPCS'
join concept c2 on c2.concept_id = cr.concept_id_2 and c2.vocabulary_id = 'RxNorm'
group by c.concept_id, c.concept_code, c.concept_name
having count(c2.concept_id) > 1
but nonetheless, I’m curious how folks are handling it. We’re currently inserting multiple rows into drug_exposure, each with a different drug_exposure_id. Maybe later on down the line, we can put something into fact_relationship indicating that all four drug_exposure_ids come from the same product. Are others doing the same thing? Does this approach seem rational?