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 Next revision Both sides next revision | ||
|
documentation:next_cdm:visits_microvisits [2017/05/07 16:19] gowtham_rao |
documentation:next_cdm:visits_microvisits [2017/05/07 16:32] gowtham_rao |
||
|---|---|---|---|
| Line 24: | Line 24: | ||
| - | **Proposed VISIT_DETAIL table:** Will have the same structure as current VISIT_OCCURRENCE table, except for two new fields (FK to visit_occurrence and a parent_visit_detail_id), and removal of just _date fields | + | **Proposed VISIT_DETAIL 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^ | ^ Field ^ Required ^ Type ^ Description^ | ||
| Line 30: | Line 32: | ||
| | 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_concept_id| Yes | integer | A foreign key that refers to a visit Concept identifier in the Standardized Vocabularies. | | ||
| - | <del>| visit_start_date| Yes | date | 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.| | ||
| - | <del>| visit_end_date| Yes | date | The end date of the visit. |</del> | + | | <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_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. | | | 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. | | ||
| Line 39: | Line 41: | ||
| | 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. | | | 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.| | | 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_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. | | | 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.| | | 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. | | + | | visit_detail_parent_id | No | integer | A foreign key to the visit_detail table record to represent the immediate parent visit-detail record. | |
| - | | parent_visit_occurrence_id | No | integer | A foreign key to the visit_occurrence table record to represent the parent visit. | + | | visit_occurrence_id | Yes | integer | A foreign key that refers to the record in the visit_occurrence table | |
| - | | primary_visit_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 | | + | |
| -------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- | ||
| - | **Relationship between VISIT_OCCURRENCE and clinical events tables** like CONDITION_OCCURRENCE, PROCEDURE_OCCURRENCE, SPECIMEN etc. | + | **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. | ||
| - | Use existing visit_occurrence_id (Fk) in these (event)_OCCURRENCE tables. | + | |
| + | ^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.| | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | ^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.| | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | ^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.| | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | ^ 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) | | ||
| -------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- | ||
documentation/next_cdm/visits_microvisits.txt · Last modified: 2017/07/06 16:30 by clairblacketer
Page Tools
