OHDSI Home | Forums | Wiki | Github

Help with Observation table

Implementers
#1

we have a table that has two discreet elements, A and B
B is directly related to A, and both are considered observations.
each patient could have several of each of these data elements.
so it is very important to make sure element B is associated with the correct element A

is the accepted convention to just make B point to A using observation_event_id and obs_event_field_concept_id

where
observation_event_id is Element A’s observation_id
and
obs_event_field_concept_id is the observation.observation_id

#2

According to the CDM documentation that is not how those fields should be used:

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table
observation_event_id If the Observation record is related to another record in the database, this field is the primary key of the linked record. Put the primary key of the linked record, if applicable, here. See the ETL Conventions for the OBSERVATION table for more details. integer No No No
obs_event_field_concept_id If the Observation record is related to another record in the database, this field is the CONCEPT_ID that identifies which table the primary key of the linked record came from. Put the CONCEPT_ID that identifies which table and field the OBSERVATION_EVENT_ID came from. integer No No Yes CONCEPT

Note that the obs_event_field_concept_id should be a concept, not a record ID (scroll right to see the last column).

My understanding is that obs_event_field_concept_id should be one of the following:

CONCEPT_ID CONCEPT_NAME DOMAIN_ID VOCABULARY_ID CONCEPT_CLASS_ID STANDARD_CONCEPT CONCEPT_CODE
8 Visit Metadata Domain Domain OMOP generated
10 Procedure Metadata Domain Domain OMOP generated
13 Drug Metadata Domain Domain OMOP generated
17 Device Metadata Domain Domain OMOP generated
19 Condition Metadata Domain Domain OMOP generated
21 Measurement Metadata Domain Domain OMOP generated
27 Observation Metadata Domain Domain OMOP generated

The purpose of these two fields is to say "this record is associated with record observation_event_id in table obs_event_field_concept_id"

It doesn’t say whether this should be used to link two records in the same event table like you are proposing, but if you want to do that your example would look like this in the OBSERVATION table:

observation_id … observation_concept_id … observation_event_id obs_event_field_concept_id
111 Concept for A
222 Concept for B 111 27

We should make the documentation of those two fields clearer since all of the event tables have versions of them (and the documentation is equally ambiguous for all of them). It should be more specific about the concepts expected to be used for obs_event_field_concept_id.

I’m also not clear why the Domain concepts are not Standard.

#3

I think the suggestion for the obs_event_field_concept_id might not be correct. The ETL convention for obs_event_field_concept_id says, “Put the CONCEPT_ID that identifies which table and field the OBSERVATION_EVENT_ID came from.”, not just the DOMAIN as suggested. Rather than obs_event_field_concept_id equal to 27, it should be 1147165, which identifies the table/column, observation.observation_id, for OMOP CDM version 5.

t