documentation:next_cdm:visits_microvisits
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
|
documentation:next_cdm:visits_microvisits [2017/05/07 16:32] gowtham_rao |
documentation:next_cdm:visits_microvisits [2017/06/06 18:05] gowtham_rao |
||
|---|---|---|---|
| Line 19: | Line 19: | ||
| * 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 31: | ||
| | 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 53: | Line 53: | ||
| We will add a new optional foreign key pointing from a clinical-event table to visit_detail table. | We will add a new optional foreign key pointing from a clinical-event table to visit_detail table. | ||
| + | |||
| + | ====== PROCEDURE_OCCURRENCE====== | ||
| ^Field^Required^Type^Description^ | ^Field^Required^Type^Description^ | ||
| Line 65: | Line 67: | ||
| |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 73: | Line 75: | ||
| + | ====== DRUG_EXPOSURE ====== | ||
| ^Field^Required^Type^Description^ | ^Field^Required^Type^Description^ | ||
| |drug_exposure_id|Yes|integer|A system-generated unique identifier for each Drug utilization event.| | |drug_exposure_id|Yes|integer|A system-generated unique identifier for each Drug utilization event.| | ||
| Line 94: | Line 96: | ||
| |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 103: | Line 105: | ||
| + | ====== DEVICE_EXPOSURE ====== | ||
| ^Field^Required^Type^Description^ | ^Field^Required^Type^Description^ | ||
| |device_exposure_id|Yes|integer|A system-generated unique identifier for each Device Exposure.| | |device_exposure_id|Yes|integer|A system-generated unique identifier for each Device Exposure.| | ||
| Line 116: | Line 119: | ||
| |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 123: | Line 126: | ||
| + | ====== CONDITION_OCCURRENCE ====== | ||
| ^ Field ^ Required ^ Type ^ Description ^ | ^ Field ^ Required ^ Type ^ Description ^ | ||
| | condition_occurrence_id | Yes | integer | A unique identifier for each Condition Occurrence event. | | | condition_occurrence_id | Yes | integer | A unique identifier for each Condition Occurrence event. | | ||
| Line 136: | Line 139: | ||
| | 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 142: | Line 145: | ||
| | condition_status_source_value | No | varchar(50) | | | condition_status_source_value | No | varchar(50) | | ||
| - | -------------------------------------------------------------------------------------------------------------- | ||
| - | 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 | + | ====== MEASUREMENT ====== |
| - | - Facility claim (Summary) | + | ^Field^Required^Type^Description^ |
| - | - Facility claim (Detail) | + | |measurement_id|Yes|integer|A unique identifier for each Measurement.| |
| - | - Professional claim (Summary) | + | |person_id|Yes|integer|A foreign key identifier to the Person about whom the measurement was recorded. The demographic details of that Person are stored in the PERSON table.| |
| - | - Professional claim (Detail) | + | |measurement_concept_id|Yes|integer|A foreign key to the standard measurement concept identifier in the Standardized Vocabularies.| |
| - | - Professional claim (Any) | + | |measurement_date|Yes|date|The date of the Measurement.| |
| - | - Facility claim (Any) | + | |measurement_datetime|No|datetime|The date and time of the Measurement. (Some database systems don't have a datatype of time. To accomodate all temporal analyses, datatype datetime can be used (combining measurement_date and measurement_time)[[http://forums.ohdsi.org/t/date-time-and-datetime-problem-and-the-world-of-hours-and-1day/314|Relevant Forum Discussion]]| |
| + | |measurement_type_concept_id|Yes|integer|A foreign key to the predefined Concept in the Standardized Vocabularies reflecting the provenance from where the Measurement record was recorded.| | ||
| + | |operator_concept_id|No|integer|A foreign key identifier to the predefined Concept in the Standardized Vocabularies reflecting the mathematical operator that is applied to the value_as_number. Operators are <, ≤, =, ≥, >.| | ||
| + | |value_as_number|No|float|A Measurement result where the result is expressed as a numeric value.| | ||
| + | |value_as_concept_id|No|integer|A foreign key to a Measurement result represented as a Concept from the Standardized Vocabularies (e.g., positive/negative, present/absent, low/high, etc.).| | ||
| + | |unit_concept_id|No|integer|A foreign key to a Standard Concept ID of Measurement Units in the Standardized Vocabularies.| | ||
| + | |range_low|No|float|The lower limit of the normal range of the Measurement result. The lower range is assumed to be of the same unit of measure as the Measurement value.| | ||
| + | |range_high|No|float|The upper limit of the normal range of the Measurement. The upper range is assumed to be of the same unit of measure as the Measurement value.| | ||
| + | |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_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_concept_id|No|integer|A foreign key to a Concept in the Standard Vocabularies that refers to the code used in the source.| | ||
| + | |unit_source_value|No|varchar(50)|The source code for the unit as it appears in the source data. This code is mapped to a standard unit concept in the Standardized Vocabularies and the original code is stored here for reference.| | ||
| + | |value_source_value|No|varchar(50)|The source value associated with the content of the value_as_number or value_as_concept_id as stored in the source data.| | ||
| - | -------------------------------------------------------------------------------------------------------------- | ||
| - | **Proposed new VISIT_OCCURRENCE_ERA table:** Will have the same structure as current VISIT_OCCURRENCE table. | ||
| - | ^ Field ^ Required ^ Type ^ Description^ | ||
| - | | 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 | | + | ====== NOTE ====== |
| + | ^Field^Required^Type^Description^ | ||
| + | |note_id|Yes|integer|A unique identifier for each note.| | ||
| + | |person_id|Yes|integer|A foreign key identifier to the Person about whom the Note was recorded. The demographic details of that Person are stored in the PERSON table.| | ||
| + | |note_date |Yes|date|The date the note was recorded.| | ||
| + | |note_datetime|No|datetime|The date and time the note was recorded.| | ||
| + | |note_type_concept_id|Yes|integer|A foreign key to the predefined Concept in the Standardized Vocabularies reflecting the type, origin or provenance of the Note.| | ||
| + | |note_class_concept_id|Yes|integer|A foreign key to the predefined Concept in the Standardized Vocabularies reflecting the HL7 LOINC Document Type Vocabulary classification of the note.| | ||
| + | |note_title|No|string(250)|The title of the Note as it appears in the source.| | ||
| + | |note_text|Yes|RBDMS dependent text|The content of the Note.| | ||
| + | |encoding_concept_id|Yes|integer|A foreign key to the predefined Concept in the Standardized Vocabularies reflecting the note character encoding type.| | ||
| + | |language_concept_id|Yes|integer|A foreign key to the predefined Concept in the Standardized Vocabularies reflecting the language of 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_detail_id**|**No**|**integer**|**Foreign key to the Visit in the VISIT_DETAIL table when the Note was taken.**| | ||
| + | |||
| + | |||
| + | |||
| + | ====== OBSERVATION ====== | ||
| + | ^Field^Required^Type^Description^ | ||
| + | |observation_id|Yes|integer|A unique identifier for each observation.| | ||
| + | |person_id|Yes|integer|A foreign key identifier to the Person about whom the observation was recorded. The demographic details of that Person are stored in the PERSON table.| | ||
| + | |observation_concept_id|Yes|integer|A foreign key to the standard observation concept identifier in the Standardized Vocabularies.| | ||
| + | |observation_date|Yes|date|The date of the observation.| | ||
| + | |observation_datetime|No|datetime|The date and time of the observation.| | ||
| + | |observation_type_concept_id|Yes|integer|A foreign key to the predefined concept identifier in the Standardized Vocabularies reflecting the type of the observation.| | ||
| + | |value_as_number|No|float|The observation result stored as a number. This is applicable to observations where the result is expressed as a numeric value.| | ||
| + | |value_as_string|No|varchar(60)|The observation result stored as a string. This is applicable to observations where the result is expressed as verbatim text.| | ||
| + | |value_as_concept_id|No|Integer|A foreign key to an observation result stored as a Concept ID. This is applicable to observations where the result can be expressed as a Standard Concept from the Standardized Vocabularies (e.g., positive/negative, present/absent, low/high, etc.).| | ||
| + | |qualifier_concept_id|No|integer|A foreign key to a Standard Concept ID for a qualifier (e.g., severity of drug-drug interaction alert)| | ||
| + | |unit_concept_id|No|integer|A foreign key to a Standard Concept ID of measurement units in the Standardized Vocabularies.| | ||
| + | |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_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_concept_id|No|integer|A foreign key to a Concept that refers to the code used in the source.| | ||
| + | |unit_source_value|No|varchar(50)|The source code for the unit as it appears in the source data. This code is mapped to a standard unit concept in the Standardized Vocabularies and the original code is, stored here for reference.| | ||
| + | |qualifier_source_value|No|varchar(50)|The source value associated with a qualifier to characterize the observation| | ||
| - | 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. | + | Representation of US claim data |
| - | By joining through VISIT_OCCURRENCE table | + | US claims data generally has two-levels |
| - | * Join VISIT_OCCURRENCE_ERA to VISIT_OCCURRENCE. | + | - Header/summary data that summarizes the entire claim |
| - | * Join VISIT_OCCURRENCE to (event)_occurrence tables. | + | - Line/detail that details a claim. |
| + | |||
| + | 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. | ||
| - | -------------------------------------------------------------------------------------------------------------- | ||
documentation/next_cdm/visits_microvisits.txt · Last modified: 2017/07/06 16:30 by clairblacketer
Page Tools
