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
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.
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.