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:

Proposed VISIT_DETAIL table

This table will have the same structure as current VISIT_OCCURRENCE table, except for two changes:

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_date Yes date The start date of the visit.
visit_start_datetime Yes datetime The date and time of the visit-detail started.
visit_end_date Yes date The end date of the visit.
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

FieldRequiredTypeDescription
procedure_occurrence_idYesintegerA system-generated unique identifier for each Procedure Occurrence.
person_idYesintegerA 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_idYesintegerA foreign key that refers to a standard procedure Concept identifier in the Standardized Vocabularies.
procedure_dateYesdateThe date on which the Procedure was performed.
procedure_datetimeNodatetimeThe date and time on which the Procedure was performed.
procedure_type_concept_idYesintegerA 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_idNointegerA foreign key to a Standard Concept identifier for a modifier to the Procedure (e.g. bilateral)
quantityNointegerThe quantity of procedures ordered or administered.
provider_idNointegerA foreign key to the provider in the provider table who was responsible for carrying out the procedure.
visit_occurrence_idNointegerA foreign key to the visit in the visit table during which the Procedure was carried out.
visit_detail_idNointegerA foreign key to the visit in the visit-detail table during which the Procedure was carried out.
procedure_source_valueNovarchar(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_idNointegerA foreign key to a Procedure Concept that refers to the code used in the source.
qualifier_source_valueNovarchar(50)The source code for the qualifier as it appears in the source data.

DRUG_EXPOSURE

FieldRequiredTypeDescription
drug_exposure_idYesintegerA system-generated unique identifier for each Drug utilization event.
person_idYesintegerA 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_idYesintegerA foreign key that refers to a Standard Concept identifier in the Standardized Vocabularies for the Drug concept.
drug_exposure_start_dateYesdateThe 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_datetimeNodatetimeThe 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_dateNodateThe end date for the current instance of Drug utilization. It is not available from all sources.
drug_exposure_end_datetimeNodatetimeThe end date and time for the current instance of Drug utilization. It is not available from all sources.
drug_type_concept_idYesinteger 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_reasonNovarchar(20)The reason the Drug was stopped. Reasons include regimen completed, changed, removed, etc.
refillsNointegerThe number of refills after the initial prescription. The initial prescription is not counted, values start with 0.
quantity NofloatThe quantity of drug as recorded in the original prescription or dispensing record.
days_supplyNointegerThe number of days of supply of the medication as recorded in the original prescription or dispensing record.
sigNoclobThe directions (“signetur”) on the Drug prescription as recorded in the original prescription (and printed on the container) or dispensing record.
route_concept_idNointegerA foreign key to a predefined concept in the Standardized Vocabularies reflecting the route of administration.
effective_drug_doseNofloatNumerical value of Drug dose for this Drug Exposure record.
dose_unit_concept_ idNointegerA foreign key to a predefined concept in the Standardized Vocabularies reflecting the unit the effective_drug_dose value is expressed.
lot_numberNovarchar(50)An identifier assigned to a particular quantity or lot of Drug product from the manufacturer.
provider_idNointegerA foreign key to the provider in the provider table who initiated (prescribed or administered) the Drug Exposure.
visit_occurrence_idNointegerA foreign key to the visit in the visit table during which the Drug Exposure was initiated.
visit_detail_idNointegerA foreign key to the visit in the visit-detail table during which the Drug Exposure was initiated.
drug_source_valueNovarchar(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_idNointegerA foreign key to a Drug Concept that refers to the code used in the source.
route_source_valueNovarchar(50)The information about the route of administration as detailed in the source.
dose_unit_source_valueNovarchar(50)The information about the dose unit as detailed in the source.

DEVICE_EXPOSURE

FieldRequiredTypeDescription
device_exposure_idYesintegerA system-generated unique identifier for each Device Exposure.
person_idYesintegerA 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_idYesintegerA foreign key that refers to a Standard Concept identifier in the Standardized Vocabularies for the Device concept.
device_exposure_start_dateYesdateThe date the Device or supply was applied or used.
device_exposure_start_datetimeNodatetimeThe date and time the Device or supply was applied or used.
device_exposure_end_dateNodateThe date the Device or supply was removed from use.
device_exposure_end_datetimeNodatetimeThe date and time the Device or supply was removed from use.
device_type_concept_idYesintegerA 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 Novarchar(50)A UDI or equivalent identifying the instance of the Device used in the Person.
quantityNointegerThe number of individual Devices used for the exposure.
provider_idNointegerA foreign key to the provider in the PROVIDER table who initiated of administered the Device.
visit_occurrence_idNointegerA foreign key to the visit in the VISIT table during which the device was used.
visit_detail_idNointegerA foreign key to the visit in the VISIT_DETAIL table during which the device was used.
device_source_valueNovarchar(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_idNointegerA 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

FieldRequiredTypeDescription
measurement_idYesintegerA unique identifier for each Measurement.
person_idYesintegerA 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_idYesintegerA foreign key to the standard measurement concept identifier in the Standardized Vocabularies.
measurement_dateYesdateThe date of the Measurement.
measurement_datetimeNodatetimeThe 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_idYesintegerA foreign key to the predefined Concept in the Standardized Vocabularies reflecting the provenance from where the Measurement record was recorded.
operator_concept_idNointegerA 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_numberNofloatA Measurement result where the result is expressed as a numeric value.
value_as_concept_idNointegerA 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_idNointegerA foreign key to a Standard Concept ID of Measurement Units in the Standardized Vocabularies.
range_lowNofloatThe 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_highNofloatThe 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_idNointegerA foreign key to the provider in the PROVIDER table who was responsible for initiating or obtaining the measurement.
visit_occurrence_idNointegerA foreign key to the Visit in the VISIT_OCCURRENCE table during which the Measurement was recorded.
visit_detail_idNointegerA foreign key to the Visit in the VISIT_DETAIL table during which the Measurement was recorded.
measurement_source_valueNovarchar(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_idNointegerA foreign key to a Concept in the Standard Vocabularies that refers to the code used in the source.
unit_source_valueNovarchar(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_valueNovarchar(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

FieldRequiredTypeDescription
note_idYesintegerA unique identifier for each note.
person_idYesintegerA 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 YesdateThe date the note was recorded.
note_datetimeNodatetimeThe date and time the note was recorded.
note_type_concept_idYesintegerA foreign key to the predefined Concept in the Standardized Vocabularies reflecting the type, origin or provenance of the Note.
note_class_concept_idYesintegerA foreign key to the predefined Concept in the Standardized Vocabularies reflecting the HL7 LOINC Document Type Vocabulary classification of the note.
note_titleNostring(250)The title of the Note as it appears in the source.
note_textYesRBDMS dependent textThe content of the Note.
encoding_concept_idYesintegerA foreign key to the predefined Concept in the Standardized Vocabularies reflecting the note character encoding type.
language_concept_idYesintegerA foreign key to the predefined Concept in the Standardized Vocabularies reflecting the language of the note.
provider_idNointegerA foreign key to the Provider in the PROVIDER table who took the Note.
visit_occurrence_idNointegerForeign key to the Visit in the VISIT_OCCURRENCE table when the Note was taken.
visit_detail_idNointegerForeign key to the Visit in the VISIT_DETAIL table when the Note was taken.

OBSERVATION

FieldRequiredTypeDescription
observation_idYesintegerA unique identifier for each observation.
person_idYesintegerA 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_idYesintegerA foreign key to the standard observation concept identifier in the Standardized Vocabularies.
observation_dateYesdateThe date of the observation.
observation_datetimeNodatetimeThe date and time of the observation.
observation_type_concept_idYesintegerA foreign key to the predefined concept identifier in the Standardized Vocabularies reflecting the type of the observation.
value_as_numberNofloatThe observation result stored as a number. This is applicable to observations where the result is expressed as a numeric value.
value_as_stringNovarchar(60)The observation result stored as a string. This is applicable to observations where the result is expressed as verbatim text.
value_as_concept_idNoIntegerA 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_idNointegerA foreign key to a Standard Concept ID for a qualifier (e.g., severity of drug-drug interaction alert)
unit_concept_idNointegerA foreign key to a Standard Concept ID of measurement units in the Standardized Vocabularies.
provider_idNointegerA foreign key to the provider in the PROVIDER table who was responsible for making the observation.
visit_occurrence_idNointegerA foreign key to the visit in the VISIT_OCCURRENCE table during which the observation was recorded.
visit_detail_idNointegerA foreign key to the visit in the VISIT_DETAIL table during which the observation was recorded.
observation_source_valueNovarchar(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_idNointegerA foreign key to a Concept that refers to the code used in the source.
unit_source_valueNovarchar(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_valueNovarchar(50)The source value associated with a qualifier to characterize the observation

Representation of US claim data

US claims data generally has two-levels

  1. Header/summary data that summarizes the entire claim
  2. 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.