Documentation
Common Data Model (CDM)
Convert Database to CDM (ETL)
Tool Specific Documentation
Sidebar
documentation:next_cdm:visits_microvisits
Table of Contents
Proposal for Visit_detail: Represent granular encounters or microvisits
Proposals are now housed as github issues link to github issue
Proposal Owner: Gowtham Rao
Use cases: http://forums.ohdsi.org/t/new-table-for-encounters-encounters-care-transitions/2297 http://forums.ohdsi.org/t/how-to-represent-encounter-payer-information-in-the-model/369 http://forums.ohdsi.org/t/capturing-more-than-one-care-site-id-or-provider-id-per-encounter-occurrence/1901 Also addresses using new Definition table the concern of atomicity and lineage of data to source http://forums.ohdsi.org/t/purpose-of-the-omop-cdm-analytics-ready-or-close-to-source/2358
See discussions: http://forums.ohdsi.org/t/definition-of-encounter-transfers-and-referrals-and-distinction-of-er-from-hospital-encounters/1311/4?u=gowtham_rao http://forums.ohdsi.org/t/new-table-for-encounters-encounters-care-transitions/2297
Proposal intent and problem statement:
- Current VISIT_OCCURRENCE table does not meet use-cases that need the granular information, such as movement of person while in a hospital.
- 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.
- 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.
- 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
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)
- Removal of _date fields.
| Field | Required | Type | Description |
|---|---|---|---|
| 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. |
| visit_detail_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-detail 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 |
| 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. |
| preceding_visit_detail_id | No | integer | A foreign key to the visit_occurrence table record of the visit immediately preceding this visit. |
| visit_source_value | No | string | The source code for the visit as it appears in the source data. |
| visit_source_concept_id | No | Integer | A foreign key to a Concept that refers to the code used in the source. |
| 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. |
| visit_detail_parent_id | No | integer | A foreign key to the visit_detail table record to represent the immediate parent visit-detail record. |
| visit_occurrence_id | Yes | integer | A foreign key that refers to the record in the visit_occurrence table |
Relationship between VISIT_DETAIL and clinical events tables We will add a new optional foreign key pointing from a clinical-event table to visit_detail table.
PROCEDURE_OCCURRENCE
| Field | Required | Type | Description |
|---|---|---|---|
| procedure_occurrence_id | Yes | integer | A system-generated unique identifier for each Procedure Occurrence. |
| person_id | Yes | integer | A foreign key identifier to the Person who is subjected to the Procedure. The demographic details of that Person are stored in the PERSON table. |
| procedure_concept_id | Yes | integer | A foreign key that refers to a standard procedure Concept identifier in the Standardized Vocabularies. |
| procedure_date | Yes | date | The date on which the Procedure was performed. |
| procedure_datetime | No | datetime | The date and time on which the Procedure was performed. |
| procedure_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 procedure record is derived. |
| modifier_concept_id | No | integer | A foreign key to a Standard Concept identifier for a modifier to the Procedure (e.g. bilateral) |
| quantity | No | integer | The quantity of procedures ordered or administered. |
| 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_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_concept_id | No | integer | A foreign key to a Procedure Concept that refers to the code used in the source. |
| qualifier_source_value | No | varchar(50) | The source code for the qualifier as it appears in the source data. |
DRUG_EXPOSURE
| Field | Required | Type | Description |
|---|---|---|---|
| drug_exposure_id | Yes | integer | A system-generated unique identifier for each Drug utilization event. |
| person_id | Yes | integer | A foreign key identifier to the person who is subjected to the Drug. The demographic details of that person are stored in the person table. |
| drug_concept_id | Yes | integer | A foreign key that refers to a Standard Concept identifier in the Standardized Vocabularies for the Drug concept. |
| drug_exposure_start_date | Yes | date | The start date for the current instance of Drug utilization. Valid entries include a start date of a prescription, the date a prescription was filled, or the date on which a Drug administration procedure was recorded. |
| drug_exposure_start_datetime | No | datetime | The start date and time for the current instance of Drug utilization. Valid entries include a start date of a prescription, the date a prescription was filled, or the date on which a Drug administration procedure was recorded. |
| drug_exposure_end_date | No | date | The end date for the current instance of Drug utilization. It is not available from all sources. |
| drug_exposure_end_datetime | No | datetime | The end date and time for the current instance of Drug utilization. It is not available from all sources. |
| drug_type_concept_id | Yes | integer | A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the type of Drug Exposure recorded. It indicates how the Drug Exposure was represented in the source data. |
| stop_reason | No | varchar(20) | The reason the Drug was stopped. Reasons include regimen completed, changed, removed, etc. |
| refills | No | integer | The number of refills after the initial prescription. The initial prescription is not counted, values start with 0. |
| quantity | No | float | The quantity of drug as recorded in the original prescription or dispensing record. |
| days_supply | No | integer | The number of days of supply of the medication as recorded in the original prescription or dispensing record. |
| sig | No | clob | The directions (“signetur”) on the Drug prescription as recorded in the original prescription (and printed on the container) or dispensing record. |
| route_concept_id | No | integer | A foreign key to a predefined concept in the Standardized Vocabularies reflecting the route of administration. |
| effective_drug_dose | No | float | Numerical value of Drug dose for this Drug Exposure record. |
| dose_unit_concept_ id | No | integer | A foreign key to a predefined concept in the Standardized Vocabularies reflecting the unit the effective_drug_dose value is expressed. |
| lot_number | No | varchar(50) | An identifier assigned to a particular quantity or lot of Drug product from the manufacturer. |
| 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_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_concept_id | No | integer | A foreign key to a Drug Concept that refers to the code used in the source. |
| route_source_value | No | varchar(50) | The information about the route of administration as detailed in the source. |
| dose_unit_source_value | No | varchar(50) | The information about the dose unit as detailed in the source. |
DEVICE_EXPOSURE
| Field | Required | Type | Description |
|---|---|---|---|
| device_exposure_id | Yes | integer | A system-generated unique identifier for each Device Exposure. |
| person_id | Yes | integer | A foreign key identifier to the Person who is subjected to the Device. The demographic details of that person are stored in the Person table. |
| device_concept_id | Yes | integer | A foreign key that refers to a Standard Concept identifier in the Standardized Vocabularies for the Device concept. |
| device_exposure_start_date | Yes | date | The date the Device or supply was applied or used. |
| device_exposure_start_datetime | No | datetime | The date and time the Device or supply was applied or used. |
| device_exposure_end_date | No | date | The date the Device or supply was removed from use. |
| device_exposure_end_datetime | No | datetime | The date and time the Device or supply was removed from use. |
| device_type_concept_id | Yes | integer | A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the type of Device Exposure recorded. It indicates how the Device Exposure was represented in the source data. |
| unique_device_id | No | varchar(50) | A UDI or equivalent identifying the instance of the Device used in the Person. |
| quantity | No | integer | The number of individual Devices used for the exposure. |
| 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_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_ concept_id | No | integer | A foreign key to a Device Concept that refers to the code used in the source. |
CONDITION_OCCURRENCE
| Field | Required | Type | Description |
|---|---|---|---|
| condition_occurrence_id | Yes | integer | A unique identifier for each Condition Occurrence event. |
| person_id | Yes | integer | A foreign key identifier to the Person who is experiencing the condition. The demographic details of that Person are stored in the PERSON table. |
| condition_concept_id | Yes | integer | A foreign key that refers to a Standard Condition Concept identifier in the Standardized Vocabularies. |
| condition_start_date | Yes | date | The date when the instance of the Condition is recorded. |
| condition_start_datetime | No | datetime | The date and time when the instance of the Condition is recorded. |
| condition_end_date | No | date | The date when the instance of the Condition is considered to have ended. |
| condition_end_datetime | No | date | The date when the instance of the Condition is considered to have ended. |
| condition_type_concept_id | Yes | integer | A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the source data from which the condition was recorded, the level of standardization, and the type of occurrence. |
| stop_reason | No | varchar(20) | The reason that the condition was no longer present, as indicated in the source data. |
| 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_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_source_concept_id | No | integer | A foreign key to a Condition Concept that refers to the code used in the source. |
| condition_source_value | No | varchar(50) | The source code for the condition as it appears in the source data. This code is mapped to a standard condition concept in the Standardized Vocabularies and the original code is stored here for reference. |
| condition_status_source_value | No | varchar(50) |
MEASUREMENT
| Field | Required | Type | Description |
|---|---|---|---|
| measurement_id | Yes | integer | A unique identifier for each Measurement. |
| 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. |
| measurement_concept_id | Yes | integer | A foreign key to the standard measurement concept identifier in the Standardized Vocabularies. |
| measurement_date | Yes | date | The date of the Measurement. |
| 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)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. |
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 |
Representation of US claim data
US claims data generally has two-levels
- Header/summary data that summarizes the entire claim
- 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
