New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Note Table update for integrating pathology and radiology report #172

Open
chandryou opened this Issue Mar 22, 2018 · 16 comments

Comments

Projects
6 participants
@chandryou

chandryou commented Mar 22, 2018

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.

@mgurley

This comment has been minimized.

Show comment
Hide comment
@mgurley

mgurley Mar 26, 2018

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.

mgurley commented Mar 26, 2018

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.

@cgreich

This comment has been minimized.

Show comment
Hide comment
@cgreich

cgreich Mar 27, 2018

Contributor

@mgurley:

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.

Contributor

cgreich commented Mar 27, 2018

@mgurley:

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.

@gowthamrao

This comment has been minimized.

Show comment
Hide comment
@gowthamrao

gowthamrao Mar 27, 2018

Contributor

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

Contributor

gowthamrao commented Mar 27, 2018

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

@mgurley

This comment has been minimized.

Show comment
Hide comment
@mgurley

mgurley Mar 27, 2018

@cgreich Digression removed. I will put the PROCEDURE_OCCURRENCE breakdown into a proposal at a later date.

mgurley commented Mar 27, 2018

@cgreich Digression removed. I will put the PROCEDURE_OCCURRENCE breakdown into a proposal at a later date.

@mgurley

This comment has been minimized.

Show comment
Hide comment
@mgurley

mgurley Mar 27, 2018

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

mgurley commented Mar 27, 2018

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

@cgreich

This comment has been minimized.

Show comment
Hide comment
@cgreich

cgreich Mar 27, 2018

Contributor

@gowthamrao:

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.

Contributor

cgreich commented Mar 27, 2018

@gowthamrao:

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.

@chandryou

This comment has been minimized.

Show comment
Hide comment
@chandryou

chandryou Apr 4, 2018

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

chandryou commented Apr 4, 2018

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

@gowthamrao

This comment has been minimized.

Show comment
Hide comment
@gowthamrao

gowthamrao Apr 4, 2018

Contributor

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.

Contributor

gowthamrao commented Apr 4, 2018

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.

@gowthamrao

This comment has been minimized.

Show comment
Hide comment
@gowthamrao

gowthamrao Apr 4, 2018

Contributor

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

Contributor

gowthamrao commented Apr 4, 2018

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

@chandryou

This comment has been minimized.

Show comment
Hide comment
@chandryou

chandryou Apr 4, 2018

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

chandryou commented Apr 4, 2018

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

@gowthamrao

This comment has been minimized.

Show comment
Hide comment
@gowthamrao

gowthamrao Apr 4, 2018

Contributor

Yes, you are correct - this is a major change that needs to be done

#164

#81 (comment)

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?

Contributor

gowthamrao commented Apr 4, 2018

Yes, you are correct - this is a major change that needs to be done

#164

#81 (comment)

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?

@cgreich

This comment has been minimized.

Show comment
Hide comment
@cgreich

cgreich Apr 6, 2018

Contributor

@gowthamrao:

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.

Contributor

cgreich commented Apr 6, 2018

@gowthamrao:

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.

@chandryou

This comment has been minimized.

Show comment
Hide comment
@chandryou

chandryou Jun 22, 2018

Can I propose this again so that revision of note table can be applied in the newer version of CDM, @clairblacketer ?

chandryou commented Jun 22, 2018

Can I propose this again so that revision of note table can be applied in the newer version of CDM, @clairblacketer ?

@MelaniePhilofsky

This comment has been minimized.

Show comment
Hide comment
@MelaniePhilofsky

MelaniePhilofsky Jul 27, 2018

You could link the radiology report/NOTE to the actual radiology record in the MEASUREMENT table via the "ugly" FACT_RELATIONSHIP table.

MelaniePhilofsky commented Jul 27, 2018

You could link the radiology report/NOTE to the actual radiology record in the MEASUREMENT table via the "ugly" FACT_RELATIONSHIP table.

@chandryou

This comment has been minimized.

Show comment
Hide comment
@chandryou

chandryou Jul 29, 2018

Thank you @MelaniePhilofsky Then, it's possible though it's a little bit ugly...

chandryou commented Jul 29, 2018

Thank you @MelaniePhilofsky Then, it's possible though it's a little bit ugly...

@clairblacketer clairblacketer added this to To do in CDM v6.0 via automation Aug 7, 2018

@clairblacketer clairblacketer moved this from To do to In progress in CDM v6.0 Aug 27, 2018

@clairblacketer clairblacketer moved this from In progress to Done in CDM v6.0 Aug 28, 2018

@clairblacketer

This comment has been minimized.

Show comment
Hide comment
@clairblacketer

clairblacketer Sep 4, 2018

Contributor

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.

Contributor

clairblacketer commented Sep 4, 2018

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.

This was referenced Oct 11, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment