Note Table update for integrating pathology and radiology report
chandryou opened this issue · 17 comments
Proposal
Adding two columns to note table to capture reports from other records in CDM eg, pathology or radiology report (just like cost table) as discussed here1, here2.
Note_event_id (NOT NULL, integer) : A foreign key identifier to the event (e.g. Specimen, Procedure, Measurement, Visit etc) record for which cost data are recorded.
note_domain_id (NOT NULL, varchar(20): The concept representing the domain of the note event, from which the corresponding table can be inferred that contains the entity for which note information is recorded. For discharge note or admission note, 'visit' event can be used.
I am in support of this proposal. I think it is a good idea. I know there is general perception that the presence of visit_occurrence_id in the NOTE table makes this unnecessary but my own experience is one encounter or visit occurrence often spans multiple procedure occurrences. So it is ambiguous which PROCEDURE_OCCURRENCE a NOTE entry belongs to if you simply rely on tying back to a VISIT_OCCURRENCE.
Hate to do that, but can you put the breakdown of the PROCEDURE_OCCURRENCE into a new Github issue and proposal? This should be about @chandryou's addition to the NOTE table.
I think it is a best practice to have a visit table as the central table that every other table joins to, i.e. not have the cost, note table link directly to procedure_occurrence. Visit_detail was designed to capture details of a record in visit_occurrence, and can capture granularity.
@chandryou I don't think we should do note_domain_id. We have thought about a new way of unambiguously referencing a table in omop, like cost_event_table_concept_id our note_event_table_concept_id, which is based on assigning a concept_id (integer) for every table in the CDM. The *_domain_id is a varchar, and is ambiguous. This leads to poor performance and e.g. in visit - are we talking about visit_occurrence or visit_detail? Both have the same domain_id
@cgreich Digression removed. I will put the PROCEDURE_OCCURRENCE breakdown into a proposal at a later date.
@gowthamrao I believe the motivation of the proposal is to support explicitly linking a NOTE to the entity the NOTE is "about". To address the ambiguity I raised of an encounter/visit occurrence spawning multiple PROCEDURE_OCCURRENCE entries and multiple NOTE entries, I would need to torture my own source data to create faux VISIT_DEATAIL entries not clearly present. If my source data has direct connections between NOTE entries and PROCEDURE_OCCURRENCE entries, I would like my common data model to be able to reflect that reality.
Agree with @mgurley. A Visit Detail does not have to contain just one Procedure. It's not a awkward FACT_RELATIONSHIP replacement. It's a logical structure to collect all healthcare events from a single "service", potentially combining many Procedures, Measurements, Diagnoses and Drugs.
@gowthamrao The reason of this proposal is simple. I want to capture the 'coronary angiography' , 'the echocardiography report' , 'pathology report' and 'radiology report' into CDM. You know the patient can take bunch of x-rays in single visit. We cannot divide all of these events into visit_details.
In the CONCEPT table, the Domain_ID is varchar (eg. 'Metadata, 'Drug', 'Condition',..), but this table is well organized. I understand your concern. But this is the best way I can come up with to store those data above.
A single timestamped record in procedure_occurrence (coronary angiography, echocardiolography) is one occurrence of an event. Events happen during visit. A visit may have more than one events, but every event should have a visit. Visits have a start datetime and end datetime. Procedure events don't have an end-date time.
This is one reason why i advocate that visit be the central anchor, and there be no records in procedure_occurrence without a corresponding visit-record.
@chandryou yes - domain_id (text, not a concept_id) is still the best we have. Just that it is not unambiguous. e.g. when we say 'Visit' domain_id, is that Visit_occurrence or visit_detail?
The proposed solution is pretty simple -- create concept_id for every omop cdm table. e.g. visit_occurrence has a concept_id, that is different from visit_detail. So, by using a column that is a concept_id, instead of domain_id in the note table - it avoids ambiguity.
@gowthamrao ,
A patient can undergo coronary angiography twice at a same day. Or a patient can take chest x-ray three times at a same day, too. These things quite often happen.
I agree that we need to avoid ambiguity.
In the cost table,
The cost information is linked through the cost_event_id field to its entity, which denotes a record in a table referenced by the cost_domain_id field:
| cost_domain_id | corresponding CDM table |
|---|---|
| Drug | DRUG_EXPOSURE |
| Visit | VISIT_OCCURRENCE |
| Procedure | PROCEDURE_OCCURRENCE |
| Device | DEVICE_EXPOSURE |
| Measurement | MEASUREMENT |
| Observation | OBSERVATION |
| Specimen | SPECIMEN |
Or another solution avoiding ambiguity is creating concept_id for tables. I think it's another good solution. In this case, we need to revise cost table, too.
Yes, you are correct - this is a major change that needs to be done
| cost_event_table_concept_id | Yes | integer | A foreign key identifier to a concept in the CONCEPT table representing the identity of the table whose primary key is equal to cost_event_id |
|---|
When the cost table was changed, this was not addressed because it was a major change that had broader implications. So, we created issue #164 to address it in the near future.
@cgreich thoughts on bringing this cost_event_table_concept_id up soon?
Soon? I think we wanted to do that in V6. Because it is a big change. Question is how quickly will be bring on V6. Let me toss out on the Forum a debate on that.
Can I propose this again so that revision of note table can be applied in the newer version of CDM, @clairblacketer ?
You could link the radiology report/NOTE to the actual radiology record in the MEASUREMENT table via the "ugly" FACT_RELATIONSHIP table.
Thank you @MelaniePhilofsky Then, it's possible though it's a little bit ugly...
Based on the discussion from today and issue #181, NOTE_DOMAIN_ID will be changed to NOTE_EVENT_FIELD_CONCEPT_ID. This will house the concept_id that corresponds to the field where the id in NOTE_EVENT_ID can be found.
added in v6.0