Observational Health Data Sciences and Informatics

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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]] 


**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. |
| <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.|
| <del>visit_end_date</del>| <del>Yes</del> |	<del>date </del> | <del>The end date of the visit.</del>|
| 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)[[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.|






====== 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.
Observational Health Data Sciences and Informatics

User Tools

Site Tools

Page Tools
