Observational Health Data Sciences and Informatics

User Tools

Site Tools

Trace: • meeting_notes_2017_may_16 • meeting_notes_2017_oct-19 • single_cost • visits_microvisits
documentation:next_cdm:visits_microvisits

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
documentation:next_cdm:visits_microvisits [2017/05/07 16:37] – gowtham_raodocumentation:next_cdm:visits_microvisits [2017/07/06 16:30] (current) – clairblacketer
Line 1: Line 1:
 ====== Proposal for Visit_detail: Represent granular encounters or microvisits ====== ====== Proposal for Visit_detail: Represent granular encounters or microvisits ======
 +** Proposals are now housed as github issues ** [[https://github.com/OHDSI/CommonDataModel/issues/70|link to github issue]] 
  
  
Line 19: Line 20:
   * The intent of this proposal is to capture detail information about a record in visit_occurrence. Examples of detail information may be encounters, micro-visits etc., and will be collected **as is** from the source data.   * The intent of this proposal is to capture detail information about a record in visit_occurrence. Examples of detail information may be encounters, micro-visits etc., and will be collected **as is** from the source data.
   * We propose a new VISIT_DETAIL table with a structure that is similar to current VISIT_OCCURRENCE table. For every record in visit_occurrence there maybe 0 or more records in visit_detail.   * We propose a new VISIT_DETAIL table with a structure that is similar to current VISIT_OCCURRENCE table. For every record in visit_occurrence there maybe 0 or more records in visit_detail.
-  * Records in visit_detail will be related to each other sequentially or hierarchially, AND will be related to visit_occurrence table (using chaining/sequential method or parent-child/part-of).+  * Records in visit_detail will be related to each other sequentially or hierarchically, AND will be related to visit_occurrence table (using chaining/sequential method or parent-child/part-of).
   * All information will belong to the domain visit.   * All information will belong to the domain visit.
-  * Example: an entire inpatient stay maybe on record in visit_occurrence table. This may have one or more detail information such as ER, ICU, medical floor, rehabilitation floor etc. Each of these visit_details may have different start/end date-times, different concept_id's and fact_id's - that would be separate record in visit_detail with a FK link to visit_occurrence. Each record within visit_detail maybe related to each other, sequentially --> ER leading to ICU leading to medical floor, leading to rehabilitation, or in hierarchical parent-child visit --> a visit for dialysis while in ICU.+  * Example: an entire inpatient stay maybe one record in visit_occurrence table. This may have one or more detail information such as ER, ICU, medical floor, rehabilitation floor etc. Each of these visit_details may have different start/end date-times, different concept_id's and fact_id's - that would be separate record in visit_detail with a FK link to visit_occurrence. Each record within visit_detail maybe related to each other, sequentially --> ER leading to ICU leading to medical floor, leading to rehabilitation, or in hierarchical parent-child visit --> a visit for dialysis while in ICU.
  
- +====== Proposed VISIT_DETAIL table ====== 
-**Proposed VISIT_DETAIL table:** Will have the same structure as current VISIT_OCCURRENCE table, except for two changes:+This table will have the same structure as current VISIT_OCCURRENCE table, except for two changes:
   * Two new foreign keys pointing to itself (visit_detail_parent_id) and to visit_occurrence table (visit_occurrence_id)   * Two new foreign keys pointing to itself (visit_detail_parent_id) and to visit_occurrence table (visit_occurrence_id)
   * Removal of _date fields.   * Removal of _date fields.
Line 31: Line 32:
 | visit_detail_id| Yes| integer| A unique identifier for each Person's visit-detail at a healthcare provider.| | visit_detail_id| Yes| integer| A unique identifier for each Person's visit-detail at a healthcare provider.|
 | person_id | Yes | integer | A foreign key identifier to the Person for whom the visit is recorded. The demographic details of that Person are stored in the PERSON table.| | person_id | Yes | integer | A foreign key identifier to the Person for whom the visit is recorded. The demographic details of that Person are stored in the PERSON table.|
-| visit_concept_id| Yes | integer | A foreign key that refers to a visit Concept identifier in the Standardized Vocabularies. |+| visit_detail_concept_id| Yes | integer | A foreign key that refers to a visit Concept identifier in the Standardized Vocabularies. |
 | <del>visit_start_date</del>| <del>Yes</del> | <del>date </del> | <del>The start date of the visit.</del>| | <del>visit_start_date</del>| <del>Yes</del> | <del>date </del> | <del>The start date of the visit.</del>|
 | visit_start_datetime | Yes | datetime | The date and time of the visit-detail started.| | visit_start_datetime | Yes | datetime | The date and time of the visit-detail started.|
Line 54: Line 55:
  
  
-====== PROCEDURE_OCCURRENCE ======+====== PROCEDURE_OCCURRENCE======
  
 ^Field^Required^Type^Description^ ^Field^Required^Type^Description^
Line 67: Line 68:
 |provider_id|No|integer|A foreign key to the provider in the provider table who was responsible for carrying out the procedure.| |provider_id|No|integer|A foreign key to the provider in the provider table who was responsible for carrying out the procedure.|
 |visit_occurrence_id|No|integer|A foreign key to the visit in the visit table during which the Procedure was carried out.| |visit_occurrence_id|No|integer|A foreign key to the visit in the visit table during which the Procedure was carried out.|
-|**visit_detail_id**|No|integer|A foreign key to the visit in the visit-detail table during which the Procedure was carried out.|+|**visit_detail_id**|**No**|**integer**|**A foreign key to the visit in the visit-detail table during which the Procedure was carried out.**|
 |procedure_source_value|No|varchar(50)|The source code for the Procedure as it appears in the source data. This code is mapped to a standard procedure Concept in the Standardized Vocabularies and the original code is, stored here for reference. Procedure source codes are typically ICD-9-Proc, CPT-4, HCPCS or OPCS-4 codes.| |procedure_source_value|No|varchar(50)|The source code for the Procedure as it appears in the source data. This code is mapped to a standard procedure Concept in the Standardized Vocabularies and the original code is, stored here for reference. Procedure source codes are typically ICD-9-Proc, CPT-4, HCPCS or OPCS-4 codes.|
 |procedure_source_concept_id|No|integer|A foreign key to a Procedure Concept that refers to the code used in the source.| |procedure_source_concept_id|No|integer|A foreign key to a Procedure Concept that refers to the code used in the source.|
Line 96: Line 97:
 |provider_id|No|integer|A foreign key to the provider in the provider table who initiated (prescribed or administered) the Drug Exposure.| |provider_id|No|integer|A foreign key to the provider in the provider table who initiated (prescribed or administered) the Drug Exposure.|
 |visit_occurrence_id|No|integer|A foreign key to the visit in the visit table during which the Drug Exposure was initiated.| |visit_occurrence_id|No|integer|A foreign key to the visit in the visit table during which the Drug Exposure was initiated.|
-|**visit_detail_id**|No|integer|A foreign key to the visit in the visit-detail table during which the Drug Exposure was initiated.|+|**visit_detail_id**|**No**|**integer**|**A foreign key to the visit in the visit-detail table during which the Drug Exposure was initiated.**|
 |drug_source_value|No|varchar(50)|The source code for the Drug as it appears in the source data. This code is mapped to a Standard Drug concept in the Standardized Vocabularies and the original code is, stored here for reference.| |drug_source_value|No|varchar(50)|The source code for the Drug as it appears in the source data. This code is mapped to a Standard Drug concept in the Standardized Vocabularies and the original code is, stored here for reference.|
 |drug_source_concept_id|No|integer|A foreign key to a Drug Concept that refers to the code used in the source.| |drug_source_concept_id|No|integer|A foreign key to a Drug Concept that refers to the code used in the source.|
Line 119: Line 120:
 |provider_id|No|integer|A foreign key to the provider in the PROVIDER table who initiated of administered the Device.| |provider_id|No|integer|A foreign key to the provider in the PROVIDER table who initiated of administered the Device.|
 |visit_occurrence_id|No|integer|A foreign key to the visit in the VISIT table during which the device was used.| |visit_occurrence_id|No|integer|A foreign key to the visit in the VISIT table during which the device was used.|
-|**visit_detail_id**|No|integer|A foreign key to the visit in the VISIT_DETAIL table during which the device was used.|+|**visit_detail_id**|**No**|**integer**|**A foreign key to the visit in the VISIT_DETAIL table during which the device was used.**|
 |device_source_value|No|varchar(50)|The source code for the Device as it appears in the source data. This code is mapped to a standard Device Concept in the Standardized Vocabularies and the original code is stored here for reference.| |device_source_value|No|varchar(50)|The source code for the Device as it appears in the source data. This code is mapped to a standard Device Concept in the Standardized Vocabularies and the original code is stored here for reference.|
 |device_source_ concept_id|No|integer|A foreign key to a Device Concept that refers to the code used in the source.| |device_source_ concept_id|No|integer|A foreign key to a Device Concept that refers to the code used in the source.|
Line 139: Line 140:
 | provider_id                    | No        | integer      | A foreign key to the Provider in the PROVIDER table who was responsible for capturing (diagnosing) the Condition.                                                                                                | | provider_id                    | No        | integer      | A foreign key to the Provider in the PROVIDER table who was responsible for capturing (diagnosing) the Condition.                                                                                                |
 | visit_occurrence_id            | No        | integer      | A foreign key to the visit in the VISIT table during which the Condition was determined (diagnosed).                                                                                                             | | visit_occurrence_id            | No        | integer      | A foreign key to the visit in the VISIT table during which the Condition was determined (diagnosed).                                                                                                             |
-| **visit_detail_id           ** | No        | integer      | A foreign key to the visit in the VISIT_DETAIL table during which the Condition was determined (diagnosed).                                                                                                             |+| **visit_detail_id           ** | **No       ** | **integer     ** | **A foreign key to the visit in the VISIT_DETAIL table during which the Condition was determined (diagnosed).**                                                                                                             |
 | condition_status_concept_id    | No        | integer      | A foreign key to the predefined concept in the standard vocabulary reflecting the condition status.                                                                                                              | | condition_status_concept_id    | No        | integer      | A foreign key to the predefined concept in the standard vocabulary reflecting the condition status.                                                                                                              |
 | condition_source_concept_id    | No        | integer      | A foreign key to a Condition Concept that refers to the code used in the source.                                                                                                                                 | | condition_source_concept_id    | No        | integer      | A foreign key to a Condition Concept that refers to the code used in the source.                                                                                                                                 |
Line 162: Line 163:
 |provider_id|No|integer|A foreign key to the provider in the PROVIDER table who was responsible for initiating or obtaining the measurement.| |provider_id|No|integer|A foreign key to the provider in the PROVIDER table who was responsible for initiating or obtaining the measurement.|
 |visit_occurrence_id|No|integer|A foreign key to the Visit in the VISIT_OCCURRENCE table during which the Measurement was recorded.| |visit_occurrence_id|No|integer|A foreign key to the Visit in the VISIT_OCCURRENCE table during which the Measurement was recorded.|
-|**visit_detail_id**|No|integer|A foreign key to the Visit in the VISIT_DETAIL table during which the Measurement was recorded.|+|**visit_detail_id**|**No**|**integer**|**A foreign key to the Visit in the VISIT_DETAIL table during which the Measurement was recorded.**|
 |measurement_source_value|No|varchar(50)|The Measurement name as it appears in the source data. This code is mapped to a Standard Concept in the Standardized Vocabularies and the original code is stored here for reference.| |measurement_source_value|No|varchar(50)|The Measurement name as it appears in the source data. This code is mapped to a Standard Concept in the Standardized Vocabularies and the original code is stored here for reference.|
 |measurement_source_concept_id|No|integer|A foreign key to a Concept in the Standard Vocabularies that refers to the code used in the source.| |measurement_source_concept_id|No|integer|A foreign key to a Concept in the Standard Vocabularies that refers to the code used in the source.|
Line 187: Line 188:
 |provider_id|No|integer|A foreign key to the Provider in the PROVIDER table who took the Note.| |provider_id|No|integer|A foreign key to the Provider in the PROVIDER table who took the Note.|
 |visit_occurrence_id|No|integer|Foreign key to the Visit in the VISIT_OCCURRENCE table when the Note was taken.| |visit_occurrence_id|No|integer|Foreign key to the Visit in the VISIT_OCCURRENCE table when the Note was taken.|
-|**visit_detail_id**|No|integer|Foreign key to the Visit in the VISIT_DETAIL table when the Note was taken.|+|**visit_detail_id**|**No**|**integer**|**Foreign key to the Visit in the VISIT_DETAIL table when the Note was taken.**|
  
  
Line 206: Line 207:
 |provider_id|No|integer|A foreign key to the provider in the PROVIDER table who was responsible for making the observation.| |provider_id|No|integer|A foreign key to the provider in the PROVIDER table who was responsible for making the observation.|
 |visit_occurrence_id|No|integer|A foreign key to the visit in the VISIT_OCCURRENCE table during which the observation was recorded.| |visit_occurrence_id|No|integer|A foreign key to the visit in the VISIT_OCCURRENCE table during which the observation was recorded.|
-|**visit_detail_id**|No|integer|A foreign key to the visit in the VISIT_DETAIL table during which the observation was recorded.|+|**visit_detail_id**|**No**|**integer**|**A foreign key to the visit in the VISIT_DETAIL table during which the observation was recorded.**|
 |observation_source_value|No|varchar(50)|The observation code as it appears in the source data. This code is mapped to a Standard Concept in the Standardized Vocabularies and the original code is, stored here for reference.| |observation_source_value|No|varchar(50)|The observation code as it appears in the source data. This code is mapped to a Standard Concept in the Standardized Vocabularies and the original code is, stored here for reference.|
 |observation_source_concept_id|No|integer|A foreign key to a Concept that refers to the code used in the source.| |observation_source_concept_id|No|integer|A foreign key to a Concept that refers to the code used in the source.|
Line 212: Line 213:
 |qualifier_source_value|No|varchar(50)|The source value associated with a qualifier to characterize the observation| |qualifier_source_value|No|varchar(50)|The source value associated with a qualifier to characterize the observation|
  
--------------------------------------------------------------------------------------------------------------- 
-Representation of US claim data 
  
-US claims data generally has header/summary data and line/detail level data. Detail is a child of the summary. If parent_visit_occurrence_id = visit_occurrence_id then it is header/summary data. If parent_visit_occurrence_id is not equal to visit_occurrence_id then it is a detail. We create new visit_type_concept_id for the following 
- - Facility claim (Summary) 
- - Facility claim (Detail) 
- - Professional claim (Summary) 
- - Professional claim (Detail) 
- - Professional claim (Any) 
- - Facility claim (Any) 
  
 -------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------
 +Representation of US claim data
  
-**Proposed new VISIT_OCCURRENCE_ERA table:** Will have the same structure as current VISIT_OCCURRENCE table. +US claims data generally has two-levels  
 +  - Header/summary data that summarizes the entire claim 
 +  - Line/detail that details a claim. 
  
-^ Field ^ Required ^ Type ^ Description^ +Detail is thus a child of the summary, and for every record in summary there is one or more records in detail. i.e. there will be atleast one FK link from visit_detail to visit_occurrence. 
-| visit_occurrence_era_id| Yes| integer| A unique identifier for each Person's visit at a healthcare provider.| +
-| person_id | Yes | integer | A foreign key identifier to the Person for whom the visit is recorded. The demographic details of that Person are stored in the PERSON table.| +
-| visit_concept_id| Yes | integer | A foreign key that refers to a visit Concept identifier in the Standardized Vocabularies. | +
-| visit_start_datetime | Yes | datetime | The date and time of the visit started.| +
-| visit_end_datetime | No | datetime | The date and time of the visit end.| +
-| visit_type_concept_id | Yes | integer | A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the type of source data from which the visit record is derived. | +
-| provider_id | No | integer | A foreign key to the provider in the provider table who was associated with the visit. | +
-| care_site_id | No | integer |A foreign key to the care site in the care site table where visit occurred | +
-| location_id | No | integer |A foreign key to the location record in the location table where visit occurred| +
-| place_of_service_concept_id | No | integer |The standard concept id for place_of_service where visit occurred| +
-| admitting_source_concept_id | No | integer | A foreign key to the predefined concept in the Place of Service Vocabulary reflecting the admitting source for a visit. | +
-| discharge_to_concept_id | No | integer | A foreign key to the predefined concept in the Place of Service Vocabulary reflecting the discharge disposition (destination) for a visit.| +
-| visit_source_value | No | string | The source code for the visit as it appears in the source data. | +
-| admitting_source_value | No | string | The source code for the admitting source as it appears in the source data. | +
-| discharge_to_source_value | No | string | The source code for the discharge disposition as it appears in the source data.| +
-| preceding_visit_occurrence_id | No | integer | A foreign key to the visit_occurrence table record of the visit immediately preceding this visit. | +
-|+
  
-=== Relationship between VISIT_OCCURRENCE and VISIT_OCCURRENCE_ERA === 
-Linking VISIT_OCCURRENCE table to VISIT_OCCURRENCE_ERA will allow for provenance/lineage.  
- 
-There may be n:m relationship between VISIT_OCCURRENCE and VISIT_OCCURRENCE_ERA, however one of them may be primary. This may be represented using primary_visit_ocurrence_era_id on VISIT_OCCURRENCE table. 
- 
-| primary_vist_occurrence_era_id  | Yes  | integer  | A foreign key that refers to the record in the VISIT_OCCURRENCE_ERA that was primary derived _ERA record from visit_occurrence_id  | 
- 
-All other non-primary relationship may be represented using FACT_RELATIONSHIP tables. 
- 
- 
--------------------------------------------------------------------------------------------------------------- 
-**Relationship between visit_occurrence and clinical events tables** like CONDITION_OCCURRENCE, PROCEDURE_OCCURRENCE, SPECIMEN etc.  
- 
-By joining through VISIT_OCCURRENCE table 
-  * Join VISIT_OCCURRENCE_ERA to VISIT_OCCURRENCE. 
-  * Join VISIT_OCCURRENCE to (event)_occurrence tables. 
- 
--------------------------------------------------------------------------------------------------------------- 
  
documentation/next_cdm/visits_microvisits.1494175068.txt.gz · Last modified: by gowtham_rao
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki