OHDSI Home | Forums | Wiki | Github

Buildong relationships between drugs and observation (

(Anton Agranov) #1


Have found an additional information concerning drugs in source data, if they were brought in by patient himself. This sort of information is presented by flag in source records. During the conversion source records will be used to populate the cdm.drug_exposure, but is seems right to keep an additional information about these drugs in cdm.observation. The idea is to build a fact relationship table to keep a distinct connection between cdm.drug_exposure and cdm.observation records.

The problem is what concept can be used as a relationship_concept_id, which is a required field.
Worth mentioning that the reverse concept is also needed.

Maybe someone has already made something like this and can give me some tips.
Any ideas also will be appreciated!

Thanks in advance!

(Clair Blacketer) #2

Hi @anagran it might be worth looking at the v6.0 specification for the OBSERVATION table. It contains the fields OBSERVATION_EVENT_ID and OBS_EVENT_FIELD_CONCEPT_ID. To link another record to an Observation, the primary key goes in OBSERVATION_EVENT_ID (CONDITION_OCCURRENCE_ID, DRUG_EXPOSURE_ID, etc.) and the Concept representing the field where the OBSERVATION_EVENT_ID was taken from go in the OBS_EVENT_FIELD_CONCEPT_ID. For example, a CONDITION_OCCURRENCE of Asthma might be linked to an Observation of a family history of Asthma. In this case the CONDITION_OCCURRENCE_ID of the Asthma record would go in OBSERVATION_EVENT_ID of the family history record and the CONCEPT_ID 1147127 would go in OBS_EVENT_FIELD_CONCEPT_ID to denote that the OBSERVATION_EVENT_ID represents a CONDITION_OCCURRENCE_ID.

It would perfectly acceptable to use these fields even if the rest of your database is on CDM v5.3. To me, this is an easier option than creating relationships and makes queries easier.

(Anton Agranov) #3

I am sorry, I don’t mentioned that the CDM v5.3. will be used.
Anyway, it seems like a better approach than I assumed to use.

Thank you!

(Clair Blacketer) #4

No problem! I would still conform to the CDM v5.3 specifications if you plan to use any OHDSI tools and methods but you can sample this feature from CDM v6 :slight_smile: