OMOP_5.4_ER_ALL

Model - OMOP_5.4_ER link

Properties
Name Value
Description

This is the specification document for the OMOP Common Data Model, v5.4. This is the latest version of the OMOP CDM. Each table is represented with a high-level description and ETL conventions that should be followed. This is continued with a discussion of each field in each table, any conventions related to the field, and constraints that should be followed (like primary key, foreign key, etc). Should you have questions please feel free to visit the forums or the github issue page

 

STANDARDIZED HEALTH SYSTEMS

location

care_site

provider

 

 

STANDARDIZED CLINICAL TABLES

person

observation_period

death

visit_occurrence

visit_detail

condition_occurrence

procedure_occurrence

drug_exposure

device_exposure

measurement

observation

note

note_nlp

episode

episode_event

specimen

fact_relationship

 

STANDARDIZED VOCABULARY TABLES

concept

concept_ancestor

concept_class

concept_relationship

concept_synonym

domain

vocabulary

relationship

source_to_concept_map

drug_strength

 

STANDARDIZED HEALTH ECONOMICS

cost

payer_plan_period

 

 

STANDARDIZED DERIVED ELEMENTS

condition_era

drug_era

dose_era

 

RESULTS SCHEMA

cohort

cohort_definition

 

 

STANDARDIZED METADATA

cdm_source

metadata

Abstract false
Leaf false
Root true
Stereotypes systemModel

Children
Name Description
 cohort : Entity

RESULT SCHEMA TABLE

 care_site : Entity

 

CARE_SITE

Table Description

The CARE_SITE table contains a list of uniquely identified institutional (physical or organizational) units where healthcare delivery is practiced (offices, wards, hospitals, clinics, etc.).

ETL Conventions

Care site is a unique combination of location_id and place_of_service_source_value. Care site does not take into account the provider (human) information such a specialty. Many source data do not make a distinction between individual and institutional providers. The CARE_SITE table contains the institutional providers. If the source, instead of uniquely identifying individual Care Sites, only provides limited information such as Place of Service, generic or “pooled” Care Site records are listed in the CARE_SITE table. There can be hierarchical and business relationships between Care Sites. For example, wards can belong to clinics or departments, which can in turn belong to hospitals, which in turn can belong to hospital systems, which in turn can belong to HMOs.The relationships between Care Sites are defined in the FACT_RELATIONSHIP table.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
care_site_id Assign an ID to each combination of a location and nature of the site - the latter could be the Place of Service, name or another characteristic in your source data. integer Yes Yes No
care_site_name The name of the care_site as it appears in the source data varchar(255) No No No
place_of_service_concept_id This is a high-level way of characterizing a Care Site. Typically, however, Care Sites can provide care in multiple settings (inpatient, outpatient, etc.) and this granularity should be reflected in the visit. Choose the concept in the visit domain that best represents the setting in which healthcare is provided in the Care Site. If most visits in a Care Site are Inpatient, then the place_of_service_concept_id should represent Inpatient. If information is present about a unique Care Site (e.g. Pharmacy) then a Care Site record should be created. Accepted Concepts. integer No No Yes CONCEPT
location_id The location_id from the LOCATION table representing the physical location of the care_site. integer No No Yes LOCATION
care_site_source_value The identifier of the care_site as it appears in the source data. This could be an identifier separate from the name of the care_site. varchar(50) No No No
place_of_service_source_value Put the place of service of the care_site as it appears in the source data. varchar(50) No No No

 cdm_source : Entity

 

CDM_SOURCE

Table Description

The CDM_SOURCE table contains detail about the source database and the process used to transform the data into the OMOP Common Data Model.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
cdm_source_name The name of the CDM instance. varchar(255) Yes No No
cdm_source_abbreviation The abbreviation of the CDM instance. varchar(25) Yes No No
cdm_holder The holder of the CDM instance. varchar(255) Yes No No
source_description The description of the CDM instance. varchar(MAX) No No No
source_documentation_reference varchar(255) No No No
cdm_etl_reference Put the link to the CDM version used. varchar(255) No No No
source_release_date The release date of the source data. date Yes No No
cdm_release_date The release data of the CDM instance. date Yes No No
cdm_version varchar(10) No No No
cdm_version_concept_id The Concept Id representing the version of the CDM. You can find all concepts that represent the CDM versions using the query: SELECT * FROM CONCEPT WHERE VOCABULARY_ID = ‘CDM’ AND CONCEPT_CLASS = ‘CDM’ integer Yes No Yes CONCEPT
vocabulary_version You can find the version of your Vocabulary using the query: SELECT vocabulary_version from vocabulary where vocabulary_id = ‘None’ varchar(20) Yes No No

 cohort_definition : Entity

RESULT SCHEMA TABLE

 concept : Entity

 

CONCEPT

Table Description

The Standardized Vocabularies contains records, or Concepts, that uniquely identify each fundamental unit of meaning used to express clinical information in all domain tables of the CDM. Concepts are derived from vocabularies, which represent clinical information across a domain (e.g. conditions, drugs, procedures) through the use of codes and associated descriptions. Some Concepts are designated Standard Concepts, meaning these Concepts can be used as normative expressions of a clinical entity within the OMOP Common Data Model and within standardized analytics. Each Standard Concept belongs to one domain, which defines the location where the Concept would be expected to occur within data tables of the CDM.

Concepts can represent broad categories (like ‘Cardiovascular disease’), detailed clinical elements (‘Myocardial infarction of the anterolateral wall’) or modifying characteristics and attributes that define Concepts at various levels of detail (severity of a disease, associated morphology, etc.).

Records in the Standardized Vocabularies tables are derived from national or international vocabularies such as SNOMED-CT, RxNorm, and LOINC, or custom Concepts defined to cover various aspects of observational data analysis.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
concept_id A unique identifier for each Concept across all domains. integer Yes Yes No
concept_name An unambiguous, meaningful and descriptive name for the Concept. varchar(255) Yes No No
domain_id A foreign key to the DOMAIN table the Concept belongs to. varchar(20) Yes No Yes DOMAIN
vocabulary_id A foreign key to the VOCABULARY table indicating from which source the Concept has been adapted. varchar(20) Yes No Yes VOCABULARY
concept_class_id The attribute or concept class of the Concept. Examples are ‘Clinical Drug’, ‘Ingredient’, ‘Clinical Finding’ etc. varchar(20) Yes No Yes CONCEPT_CLASS
standard_concept This flag determines where a Concept is a Standard Concept, i.e. is used in the data, a Classification Concept, or a non-standard Source Concept. The allowable values are ‘S’ (Standard Concept) and ‘C’ (Classification Concept), otherwise the content is NULL. varchar(1) No No No
concept_code The concept code represents the identifier of the Concept in the source vocabulary, such as SNOMED-CT concept IDs, RxNorm RXCUIs etc. Note that concept codes are not unique across vocabularies. varchar(50) Yes No No
valid_start_date The date when the Concept was first recorded. The default value is 1-Jan-1970, meaning, the Concept has no (known) date of inception. date Yes No No
valid_end_date The date when the Concept became invalid because it was deleted or superseded (updated) by a new concept. The default value is 31-Dec-2099, meaning, the Concept is valid until it becomes deprecated. date Yes No No
invalid_reason Reason the Concept was invalidated. Possible values are D (deleted), U (replaced with an update) or NULL when valid_end_date has the default value. varchar(1) No No No

 concept_ancestor : Entity

 

CONCEPT_ANCESTOR

Table Description

The CONCEPT_ANCESTOR table is designed to simplify observational analysis by providing the complete hierarchical relationships between Concepts. Only direct parent-child relationships between Concepts are stored in the CONCEPT_RELATIONSHIP table. To determine higher level ancestry connections, all individual direct relationships would have to be navigated at analysis time. The CONCEPT_ANCESTOR table includes records for all parent-child relationships, as well as grandparent-grandchild relationships and those of any other level of lineage. Using the CONCEPT_ANCESTOR table allows for querying for all descendants of a hierarchical concept. For example, drug ingredients and drug products are all descendants of a drug class ancestor.

This table is entirely derived from the CONCEPT, CONCEPT_RELATIONSHIP and RELATIONSHIP tables.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
ancestor_concept_id The Concept Id for the higher-level concept that forms the ancestor in the relationship. integer Yes No Yes CONCEPT
descendant_concept_id The Concept Id for the lower-level concept that forms the descendant in the relationship. integer Yes No Yes CONCEPT
min_levels_of_separation The minimum separation in number of levels of hierarchy between ancestor and descendant concepts. This is an attribute that is used to simplify hierarchic analysis. integer Yes No No
max_levels_of_separation The maximum separation in number of levels of hierarchy between ancestor and descendant concepts. This is an attribute that is used to simplify hierarchic analysis. integer Yes No No

 concept_class : Entity

 

CONCEPT_CLASS

Table Description

The CONCEPT_CLASS table is a reference table, which includes a list of the classifications used to differentiate Concepts within a given Vocabulary. This reference table is populated with a single record for each Concept Class.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
concept_class_id A unique key for each class. varchar(20) Yes Yes No
concept_class_name The name describing the Concept Class, e.g. Clinical Finding, Ingredient, etc. varchar(255) Yes No No
concept_class_concept_id A Concept that represents the Concept Class. integer Yes No Yes CONCEPT

 concept_relationship : Entity

 

CONCEPT_RELATIONSHIP

Table Description

The CONCEPT_RELATIONSHIP table contains records that define direct relationships between any two Concepts and the nature or type of the relationship. Each type of a relationship is defined in the RELATIONSHIP table.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
concept_id_1 integer Yes No Yes CONCEPT
concept_id_2 integer Yes No Yes CONCEPT
relationship_id The relationship between CONCEPT_ID_1 and CONCEPT_ID_2. Please see the Vocabulary Conventions. for more information. varchar(20) Yes No Yes RELATIONSHIP
valid_start_date The date when the relationship is first recorded. date Yes No No
valid_end_date The date when the relationship is invalidated. date Yes No No
invalid_reason Reason the relationship was invalidated. Possible values are ‘D’ (deleted), ‘U’ (updated) or NULL. varchar(1) No No No

 concept_synonym : Entity

 

CONCEPT_SYNONYM

Table Description

The CONCEPT_SYNONYM table is used to store alternate names and descriptions for Concepts.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
concept_id integer Yes No Yes CONCEPT
concept_synonym_name varchar(1000) Yes No No
language_concept_id integer Yes No Yes CONCEPT

 condition_era : Entity

 

CONDITION_ERA

Table Description

A Condition Era is defined as a span of time when the Person is assumed to have a given condition. Similar to Drug Eras, Condition Eras are chronological periods of Condition Occurrence. Combining individual Condition Occurrences into a single Condition Era serves two purposes:

  • It allows aggregation of chronic conditions that require frequent ongoing care, instead of treating each Condition Occurrence as an independent event.
  • It allows aggregation of multiple, closely timed doctor visits for the same Condition to avoid double-counting the Condition Occurrences. For example, consider a Person who visits her Primary Care Physician (PCP) and who is referred to a specialist. At a later time, the Person visits the specialist, who confirms the PCP’s original diagnosis and provides the appropriate treatment to resolve the condition. These two independent doctor visits should be aggregated into one Condition Era.

ETL Conventions

Each Condition Era corresponds to one or many Condition Occurrence records that form a continuous interval. The condition_concept_id field contains Concepts that are identical to those of the CONDITION_OCCURRENCE table records that make up the Condition Era. In contrast to Drug Eras, Condition Eras are not aggregated to contain Conditions of different hierarchical layers. The SQl Script for generating CONDITION_ERA records can be found here The Condition Era Start Date is the start date of the first Condition Occurrence. The Condition Era End Date is the end date of the last Condition Occurrence. Condition Eras are built with a Persistence Window of 30 days, meaning, if no occurrence of the same condition_concept_id happens within 30 days of any one occurrence, it will be considered the condition_era_end_date.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
condition_era_id integer Yes Yes No
person_id integer Yes No Yes PERSON
condition_concept_id The Concept Id representing the Condition. integer Yes No Yes CONCEPT Condition
condition_era_start_date The start date for the Condition Era constructed from the individual instances of Condition Occurrences. It is the start date of the very first chronologically recorded instance of the condition with at least 31 days since any prior record of the same Condition. date Yes No No
condition_era_end_date The end date for the Condition Era constructed from the individual instances of Condition Occurrences. It is the end date of the final continuously recorded instance of the Condition. date Yes No No
condition_occurrence_count The number of individual Condition Occurrences used to construct the condition era. integer No No No

 

 condition_occurrence : Entity

 

CONDITION_OCCURRENCE

Table Description

This table contains records of Events of a Person suggesting the presence of a disease or medical condition stated as a diagnosis, a sign, or a symptom, which is either observed by a Provider or reported by the patient.

User Guide

Conditions are defined by Concepts from the Condition domain, which form a complex hierarchy. As a result, the same Person with the same disease may have multiple Condition records, which belong to the same hierarchical family. Most Condition records are mapped from diagnostic codes, but recorded signs, symptoms and summary descriptions also contribute to this table. Rule out diagnoses should not be recorded in this table, but in reality their negating nature is not always captured in the source data, and other precautions must be taken when when identifying Persons who should suffer from the recorded Condition. Record all conditions as they exist in the source data. Any decisions about diagnosis/phenotype definitions would be done through cohort specifications. These cohorts can be housed in the COHORT table. Conditions span a time interval from start to end, but are typically recorded as single snapshot records with no end date. The reason is twofold: (i) At the time of the recording the duration is not known and later not recorded, and (ii) the Persons typically cease interacting with the healthcare system when they feel better, which leads to incomplete capture of resolved Conditions. The CONDITION_ERA table addresses this issue. Family history and past diagnoses (‘history of’) are not recorded in this table. Instead, they are listed in the OBSERVATION table. Codes written in the process of establishing the diagnosis, such as ‘question of’ of and ‘rule out’, should not represented here. Instead, they should be recorded in the OBSERVATION table, if they are used for analyses. However, this information is not always available.

ETL Conventions

Source codes and source text fields mapped to Standard Concepts of the Condition Domain have to be recorded here.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
condition_occurrence_id The unique key given to a condition record for a person. Refer to the ETL for how duplicate conditions during the same visit were handled. Each instance of a condition present in the source data should be assigned this unique key. In some cases, a person can have multiple records of the same condition within the same visit. It is valid to keep these duplicates and assign them individual, unique, CONDITION_OCCURRENCE_IDs, though it is up to the ETL how they should be handled. integer Yes Yes No
person_id The PERSON_ID of the PERSON for whom the condition is recorded. integer Yes No Yes PERSON
condition_concept_id The CONDITION_CONCEPT_ID field is recommended for primary use in analyses, and must be used for network studies. This is the standard concept mapped from the source value which represents a condition The CONCEPT_ID that the CONDITION_SOURCE_VALUE maps to. Only records whose source values map to concepts with a domain of “Condition” should go in this table. Accepted Concepts. integer Yes No Yes CONCEPT Condition
condition_start_date Use this date to determine the start date of the condition Most often data sources do not have the idea of a start date for a condition. Rather, if a source only has one date associated with a condition record it is acceptable to use that date for both the CONDITION_START_DATE and the CONDITION_END_DATE. date Yes No No
condition_start_datetime If a source does not specify datetime the convention is to set the time to midnight (00:00:0000) datetime No No No
condition_end_date Use this date to determine the end date of the condition Most often data sources do not have the idea of a start date for a condition. Rather, if a source only has one date associated with a condition record it is acceptable to use that date for both the CONDITION_START_DATE and the CONDITION_END_DATE. date No No No
condition_end_datetime If a source does not specify datetime the convention is to set the time to midnight (00:00:0000) datetime No No No
condition_type_concept_id This field can be used to determine the provenance of the Condition record, as in whether the condition was from an EHR system, insurance claim, registry, or other sources. Choose the CONDITION_TYPE_CONCEPT_ID that best represents the provenance of the record. Accepted Concepts. A more detailed explanation of each Type Concept can be found on the vocabulary wiki. integer Yes No Yes CONCEPT Type Concept
condition_status_concept_id This concept represents the point during the visit the diagnosis was given (admitting diagnosis, final diagnosis), whether the diagnosis was determined due to laboratory findings, if the diagnosis was exclusionary, or if it was a preliminary diagnosis, among others. Choose the Concept in the Condition Status domain that best represents the point during the visit when the diagnosis was given. These can include admitting diagnosis, principal diagnosis, and secondary diagnosis. Accepted Concepts. integer No No Yes CONCEPT Condition Status
stop_reason The Stop Reason indicates why a Condition is no longer valid with respect to the purpose within the source data. Note that a Stop Reason does not necessarily imply that the condition is no longer occurring. This information is often not populated in source data and it is a valid etl choice to leave it blank if the information does not exist. varchar(20) No No No
provider_id The provider associated with condition record, e.g. the provider who made the diagnosis or the provider who recorded the symptom. The ETL may need to make a choice as to which PROVIDER_ID to put here. Based on what is available this may or may not be different than the provider associated with the overall VISIT_OCCURRENCE record, for example the admitting vs attending physician on an EHR record. integer No No Yes PROVIDER
visit_occurrence_id The visit during which the condition occurred. Depending on the structure of the source data, this may have to be determined based on dates. If a CONDITION_START_DATE occurs within the start and end date of a Visit it is a valid ETL choice to choose the VISIT_OCCURRENCE_ID from the Visit that subsumes it, even if not explicitly stated in the data. While not required, an attempt should be made to locate the VISIT_OCCURRENCE_ID of the CONDITION_OCCURRENCE record. integer No No Yes VISIT_OCCURRENCE
visit_detail_id The VISIT_DETAIL record during which the condition occurred. For example, if the person was in the ICU at the time of the diagnosis the VISIT_OCCURRENCE record would reflect the overall hospital stay and the VISIT_DETAIL record would reflect the ICU stay during the hospital visit. Same rules apply as for the VISIT_OCCURRENCE_ID. integer No No Yes VISIT_DETAIL
condition_source_value This field houses the verbatim value from the source data representing the condition that occurred. For example, this could be an ICD10 or Read code. This code is mapped to a Standard Condition Concept in the Standardized Vocabularies and the original code is stored here for reference. varchar(50) No No No
condition_source_concept_id This is the concept representing the condition source value and may not necessarily be standard. This field is discouraged from use in analysis because it is not required to contain Standard Concepts that are used across the OHDSI community, and should only be used when Standard Concepts do not adequately represent the source detail for the Condition necessary for a given analytic use case. Consider using CONDITION_CONCEPT_ID instead to enable standardized analytics that can be consistent across the network. If the CONDITION_SOURCE_VALUE is coded in the source data using an OMOP supported vocabulary put the concept id representing the source value here. integer No No Yes CONCEPT
condition_status_source_value This field houses the verbatim value from the source data representing the condition status. This information may be called something different in the source data but the field is meant to contain a value indicating when and how a diagnosis was given to a patient. This source value is mapped to a standard concept which is stored in the CONDITION_STATUS_CONCEPT_ID field. varchar(50) No No No

 cost : Entity

 

COST

Table Description

The COST table captures records containing the cost of any medical event recorded in one of the OMOP clinical event tables such as DRUG_EXPOSURE, PROCEDURE_OCCURRENCE, VISIT_OCCURRENCE, VISIT_DETAIL, DEVICE_OCCURRENCE, OBSERVATION or MEASUREMENT.

Each record in the cost table account for the amount of money transacted for the clinical event. So, the COST table may be used to represent both receivables (charges) and payments (paid), each transaction type represented by its COST_CONCEPT_ID. The COST_TYPE_CONCEPT_ID field will use concepts in the Standardized Vocabularies to designate the source (provenance) of the cost data. A reference to the health plan information in the PAYER_PLAN_PERIOD table is stored in the record for information used for the adjudication system to determine the persons benefit for the clinical event.

User Guide

When dealing with summary costs, the cost of the goods or services the provider provides is often not known directly, but derived from the hospital charges multiplied by an average cost-to-charge ratio.

ETL Conventions

One cost record is generated for each response by a payer. In a claims databases, the payment and payment terms reported by the payer for the goods or services billed will generate one cost record. If the source data has payment information for more than one payer (i.e. primary insurance and secondary insurance payment for one entity), then a cost record is created for each reporting payer. Therefore, it is possible for one procedure to have multiple cost records for each payer, but typically it contains one or no record per entity. Payer reimbursement cost records will be identified by using the PAYER_PLAN_ID field. Drug costs are composed of ingredient cost (the amount charged by the wholesale distributor or manufacturer), the dispensing fee (the amount charged by the pharmacy and the sales tax).

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
cost_id integer Yes Yes No
cost_event_id integer Yes No No
cost_domain_id varchar(20) Yes No Yes DOMAIN
cost_type_concept_id integer Yes No Yes CONCEPT
currency_concept_id integer No No Yes CONCEPT
total_charge float No No No
total_cost float No No No
total_paid float No No No
paid_by_payer float No No No
paid_by_patient float No No No
paid_patient_copay float No No No
paid_patient_coinsurance float No No No
paid_patient_deductible float No No No
paid_by_primary float No No No
paid_ingredient_cost float No No No
paid_dispensing_fee float No No No
payer_plan_period_id integer No No No
amount_allowed float No No No
revenue_code_concept_id integer No No Yes CONCEPT
revenue_code_source_value Revenue codes are a method to charge for a class of procedures and conditions in the U.S. hospital system. varchar(50) No No No
drg_concept_id integer No No Yes CONCEPT
drg_source_value Diagnosis Related Groups are US codes used to classify hospital cases into one of approximately 500 groups. varchar(3) No No No

 death : Entity

DEATH

Table Description

The death domain contains the clinical event for how and when a Person dies. A person can have up to one record if the source system contains evidence about the Death, such as: Condition in an administrative claim, status of enrollment into a health plan, or explicit record in EHR data.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
person_id integer Yes No Yes PERSON
death_date The date the person was deceased. If the precise date include day or month is not known or not allowed, December is used as the default month, and the last day of the month the default day. date Yes No No
death_datetime If not available set time to midnight (00:00:00) datetime No No No
death_type_concept_id This is the provenance of the death record, i.e., where it came from. It is possible that an administrative claims database would source death information from a government file so do not assume the Death Type is the same as the Visit Type, etc. Use the type concept that be reflects the source of the death record. Accepted Concepts. A more detailed explanation of each Type Concept can be found on the vocabulary wiki. integer No No Yes CONCEPT Type Concept
cause_concept_id This is the Standard Concept representing the Person’s cause of death, if available. There is no specified domain for this concept, just choose the Standard Concept Id that best represents the person’s cause of death. integer No No Yes CONCEPT
cause_source_value If available, put the source code representing the cause of death here. varchar(50) No No No
cause_source_concept_id If the cause of death was coded using a Vocabulary present in the OMOP Vocabularies put the CONCEPT_ID representing the cause of death here. integer No No Yes CONCEPT

 device_exposure : Entity

 

DEVICE_EXPOSURE

Table Description

The Device domain captures information about a person’s exposure to a foreign physical object or instrument which is used for diagnostic or therapeutic purposes through a mechanism beyond chemical action. Devices include implantable objects (e.g. pacemakers, stents, artificial joints), medical equipment and supplies (e.g. bandages, crutches, syringes), other instruments used in medical procedures (e.g. sutures, defibrillators) and material used in clinical care (e.g. adhesives, body material, dental material, surgical material).

User Guide

The distinction between Devices or supplies and Procedures are sometimes blurry, but the former are physical objects while the latter are actions, often to apply a Device or supply.

ETL Conventions

Source codes and source text fields mapped to Standard Concepts of the Device Domain have to be recorded here.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
device_exposure_id The unique key given to records a person’s exposure to a foreign physical object or instrument. Each instance of an exposure to a foreign object or device present in the source data should be assigned this unique key. integer Yes Yes No
person_id integer Yes No Yes PERSON
device_concept_id The DEVICE_CONCEPT_ID field is recommended for primary use in analyses, and must be used for network studies. This is the standard concept mapped from the source concept id which represents a foreign object or instrument the person was exposed to. The CONCEPT_ID that the DEVICE_SOURCE_VALUE maps to. integer Yes No Yes CONCEPT Device
device_exposure_start_date Use this date to determine the start date of the device record. Valid entries include a start date of a procedure to implant a device, the date of a prescription for a device, or the date of device administration. date Yes No No
device_exposure_start_datetime This is not required, though it is in v6. If a source does not specify datetime the convention is to set the time to midnight (00:00:0000) datetime No No No
device_exposure_end_date The DEVICE_EXPOSURE_END_DATE denotes the day the device exposure ended for the patient, if given. Put the end date or discontinuation date as it appears from the source data or leave blank if unavailable. date No No No
device_exposure_end_datetime If a source does not specify datetime the convention is to set the time to midnight (00:00:0000) datetime No No No
device_type_concept_id You can use the TYPE_CONCEPT_ID to denote the provenance of the record, as in whether the record is from administrative claims or EHR. Choose the drug_type_concept_id that best represents the provenance of the record, for example whether it came from a record of a prescription written or physician administered drug. Accepted Concepts. A more detailed explanation of each Type Concept can be found on the vocabulary wiki. integer Yes No Yes CONCEPT Type Concept
unique_device_id This is the Unique Device Identification (UDI-DI) number for devices regulated by the FDA, if given. For medical devices that are regulated by the FDA, a Unique Device Identification (UDI) is provided if available in the data source and is recorded in the UNIQUE_DEVICE_ID field. varchar(255) No No No
production_id This is the Production Identifier (UDI-PI) portion of the Unique Device Identification. varchar(255) No No No
quantity integer No No No
provider_id The Provider associated with device record, e.g. the provider who wrote the prescription or the provider who implanted the device. The ETL may need to make a choice as to which PROVIDER_ID to put here. Based on what is available this may or may not be different than the provider associated with the overall VISIT_OCCURRENCE record. integer No No Yes PROVIDER
visit_occurrence_id The Visit during which the device was prescribed or given. To populate this field device exposures must be explicitly initiated in the visit. integer No No Yes VISIT_OCCURRENCE
visit_detail_id The Visit Detail during which the device was prescribed or given. To populate this field device exposures must be explicitly initiated in the visit detail record. integer No No Yes VISIT_DETAIL
device_source_value This field houses the verbatim value from the source data representing the device exposure that occurred. For example, this could be an NDC or Gemscript code. This code is mapped to a Standard Device Concept in the Standardized Vocabularies and the original code is stored here for reference. varchar(50) No No No
device_source_concept_id This is the concept representing the device source value and may not necessarily be standard. This field is discouraged from use in analysis because it is not required to contain Standard Concepts that are used across the OHDSI community, and should only be used when Standard Concepts do not adequately represent the source detail for the Device necessary for a given analytic use case. Consider using DEVICE_CONCEPT_ID instead to enable standardized analytics that can be consistent across the network. If the DEVICE_SOURCE_VALUE is coded in the source data using an OMOP supported vocabulary put the concept id representing the source value here. integer No No Yes CONCEPT
unit_concept_id UNIT_SOURCE_VALUES should be mapped to a Standard Concept in the Unit domain that best represents the unit as given in the source data. There is no standardization requirement for units associated with DEVICE_CONCEPT_IDs, however, it is the responsibility of the ETL to choose the most plausible unit. If there is no unit associated with a Device record, set to NULL. integer No No Yes CONCEPT Unit
unit_source_value This field houses the verbatim value from the source data representing the unit of the Device. For example, blood transfusions are considered devices and can be given in mL quantities. This code is mapped to a Standard Condition Concept in the Standardized Vocabularies and the original code is stored here for reference. Using the blood transfusion example, blood transfusion is represented by the DEVICE_CONCEPT_ID and the unit (mL) would be housed in the UNIT_SOURCE_VALUE and mapped to a standard concept in the unit domain. varchar(50) No No No
unit_source_concept_id This is the concept representing the UNIT_SOURCE_VALUE and may not necessarily be standard. This field is discouraged from use in analysis because it is not required to contain Standard Concepts that are used across the OHDSI community, and should only be used when Standard Concepts do not adequately represent the source detail for the Unit necessary for a given analytic use case. Consider using UNIT_CONCEPT_ID instead to enable standardized analytics that can be consistent across the network. If the UNIT_SOURCE_VALUE is coded in the source data using an OMOP supported vocabulary put the concept id representing the source value here. integer No No Yes CONCEPT

 domain : Entity

DOMAIN

Table Description

The DOMAIN table includes a list of OMOP-defined Domains the Concepts of the Standardized Vocabularies can belong to. A Domain defines the set of allowable Concepts for the standardized fields in the CDM tables. For example, the “Condition” Domain contains Concepts that describe a condition of a patient, and these Concepts can only be stored in the condition_concept_id field of the CONDITION_OCCURRENCE and CONDITION_ERA tables. This reference table is populated with a single record for each Domain and includes a descriptive name for the Domain.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
domain_id A unique key for each domain. varchar(20) Yes Yes No
domain_name The name describing the Domain, e.g. Condition, Procedure, Measurement etc. varchar(255) Yes No No
domain_concept_id A Concept representing the Domain Concept the DOMAIN record belongs to. integer Yes No Yes CONCEPT

 dose_era : Entity

 

DOSE_ERA

Table Description

A Dose Era is defined as a span of time when the Person is assumed to be exposed to a constant dose of a specific active ingredient.

ETL Conventions

Dose Eras will be derived from records in the DRUG_EXPOSURE table and the Dose information from the DRUG_STRENGTH table using a standardized algorithm. Dose Form information is not taken into account. So, if the patient changes between different formulations, or different manufacturers with the same formulation, the Dose Era is still spanning the entire time of exposure to the Ingredient.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
dose_era_id integer Yes Yes No
person_id integer Yes No Yes PERSON
drug_concept_id The Concept Id representing the specific drug ingredient. integer Yes No Yes CONCEPT Drug
unit_concept_id The Concept Id representing the unit of the specific drug ingredient. integer Yes No Yes CONCEPT Unit
dose_value The numeric value of the dosage of the drug_ingredient. float Yes No No
dose_era_start_date The date the Person started on the specific dosage, with at least 31 days since any prior exposure. date Yes No No
dose_era_end_date The date the Person was no longer exposed to the dosage of the specific drug ingredient. An era is ended if there are 31 days or more between dosage records. date Yes No No

 drug_era : Entity

 

DRUG_ERA

Table Description

A Drug Era is defined as a span of time when the Person is assumed to be exposed to a particular active ingredient. A Drug Era is not the same as a Drug Exposure: Exposures are individual records corresponding to the source when Drug was delivered to the Person, while successive periods of Drug Exposures are combined under certain rules to produce continuous Drug Eras.

ETL Conventions

The SQL script for generating DRUG_ERA records can be found here.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
drug_era_id integer Yes Yes No
person_id integer Yes No Yes PERSON
drug_concept_id The Concept Id representing the specific drug ingredient. integer Yes No Yes CONCEPT Drug
drug_era_start_date The Drug Era Start Date is the start date of the first Drug Exposure for a given ingredient, with at least 31 days since the previous exposure. date Yes No No
drug_era_end_date The Drug Era End Date is the end date of the last Drug Exposure. The End Date of each Drug Exposure is either taken from the field drug_exposure_end_date or, as it is typically not available, inferred using the following rules: For pharmacy prescription data, the date when the drug was dispensed plus the number of days of supply are used to extrapolate the End Date for the Drug Exposure. Depending on the country-specific healthcare system, this supply information is either explicitly provided in the day_supply field or inferred from package size or similar information. For Procedure Drugs, usually the drug is administered on a single date (i.e., the administration date). A standard Persistence Window of 30 days (gap, slack) is permitted between two subsequent such extrapolated DRUG_EXPOSURE records to be considered to be merged into a single Drug Era. date Yes No No
drug_exposure_count integer No No No
gap_days The Gap Days determine how many total drug-free days are observed between all Drug Exposure events that contribute to a DRUG_ERA record. It is assumed that the drugs are “not stockpiled” by the patient, i.e. that if a new drug prescription or refill is observed (a new DRUG_EXPOSURE record is written), the remaining supply from the previous events is abandoned. The difference between Persistence Window and Gap Days is that the former is the maximum drug-free time allowed between two subsequent DRUG_EXPOSURE records, while the latter is the sum of actual drug-free days for the given Drug Era under the above assumption of non-stockpiling. integer No No No

 drug_exposure : Entity

 

DRUG_EXPOSURE

Table Description

This table captures records about the exposure to a Drug ingested or otherwise introduced into the body. A Drug is a biochemical substance formulated in such a way that when administered to a Person it will exert a certain biochemical effect on the metabolism. Drugs include prescription and over-the-counter medicines, vaccines, and large-molecule biologic therapies. Radiological devices ingested or applied locally do not count as Drugs.

User Guide

The purpose of records in this table is to indicate an exposure to a certain drug as best as possible. In this context a drug is defined as an active ingredient. Drug Exposures are defined by Concepts from the Drug domain, which form a complex hierarchy. As a result, one DRUG_SOURCE_CONCEPT_ID may map to multiple standard concept ids if it is a combination product. Records in this table represent prescriptions written, prescriptions dispensed, and drugs administered by a provider to name a few. The DRUG_TYPE_CONCEPT_ID can be used to find and filter on these types. This table includes additional information about the drug products, the quantity given, and route of administration.

ETL Conventions

Information about quantity and dose is provided in a variety of different ways and it is important for the ETL to provide as much information as possible from the data. Depending on the provenance of the data fields may be captured differently i.e. quantity for drugs administered may have a separate meaning from quantity for prescriptions dispensed. If a patient has multiple records on the same day for the same drug or procedures the ETL should not de-dupe them unless there is probable reason to believe the item is a true data duplicate. Take note on how to handle refills for prescriptions written.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
drug_exposure_id The unique key given to records of drug dispensings or administrations for a person. Refer to the ETL for how duplicate drugs during the same visit were handled. Each instance of a drug dispensing or administration present in the source data should be assigned this unique key. In some cases, a person can have multiple records of the same drug within the same visit. It is valid to keep these duplicates and assign them individual, unique, DRUG_EXPOSURE_IDs, though it is up to the ETL how they should be handled. integer Yes Yes No
person_id The PERSON_ID of the PERSON for whom the drug dispensing or administration is recorded. This may be a system generated code. integer Yes No Yes PERSON
drug_concept_id The DRUG_CONCEPT_ID field is recommended for primary use in analyses, and must be used for network studies. This is the standard concept mapped from the source concept id which represents a drug product or molecule otherwise introduced to the body. The drug concepts can have a varying degree of information about drug strength and dose. This information is relevant in the context of quantity and administration information in the subsequent fields plus strength information from the DRUG_STRENGTH table, provided as part of the standard vocabulary download. The CONCEPT_ID that the DRUG_SOURCE_VALUE maps to. The concept id should be derived either from mapping from the source concept id or by picking the drug concept representing the most amount of detail you have. Records whose source values map to standard concepts with a domain of Drug should go in this table. When the Drug Source Value of the code cannot be translated into Standard Drug Concept IDs, a Drug exposure entry is stored with only the corresponding SOURCE_CONCEPT_ID and DRUG_SOURCE_VALUE and a DRUG_CONCEPT_ID of 0. The Drug Concept with the most detailed content of information is preferred during the mapping process. These are indicated in the CONCEPT_CLASS_ID field of the Concept and are recorded in the following order of precedence: ‘Branded Pack’, ‘Clinical Pack’, ‘Branded Drug’, ‘Clinical Drug’, ‘Branded Drug Component’, ‘Clinical Drug Component’, ‘Branded Drug Form’, ‘Clinical Drug Form’, and only if no other information is available ‘Ingredient’. Note: If only the drug class is known, the DRUG_CONCEPT_ID field should contain 0. Accepted Concepts. integer Yes No Yes CONCEPT Drug
drug_exposure_start_date Use this date to determine the start date of the drug record. Valid entries include a start date of a prescription, the date a prescription was filled, or the date on which a Drug administration was recorded. It is a valid ETL choice to use the date the drug was ordered as the DRUG_EXPOSURE_START_DATE. date Yes No No
drug_exposure_start_datetime This is not required, though it is in v6. If a source does not specify datetime the convention is to set the time to midnight (00:00:0000) datetime No No No
drug_exposure_end_date The DRUG_EXPOSURE_END_DATE denotes the day the drug exposure ended for the patient. If this information is not explicitly available in the data, infer the end date using the following methods:

1. Start first with duration or days supply using the calculation drug start date + days supply -1 day. 2. Use quantity divided by daily dose that you may obtain from the sig or a source field (or assumed daily dose of 1) for solid, indivisibile, drug products. If quantity represents ingredient amount, quantity divided by daily dose * concentration (from drug_strength) drug concept id tells you the dose form. 3. If it is an administration record, set drug end date equal to drug start date. If the record is a written prescription then set end date to start date + 29. If the record is a mail-order prescription set end date to start date + 89. The end date must be equal to or greater than the start date. Ibuprofen 20mg/mL oral solution concept tells us this is oral solution. Calculate duration as quantity (200 example) * daily dose (5mL) /concentration (20mg/mL) 200*5/20 = 50 days. Examples by dose form
date Yes No No
drug_exposure_end_datetime This is not required, though it is in v6. If a source does not specify datetime the convention is to set the time to midnight (00:00:0000) datetime No No No
verbatim_end_date This is the end date of the drug exposure as it appears in the source data, if it is given Put the end date or discontinuation date as it appears from the source data or leave blank if unavailable. date No No No
drug_type_concept_id You can use the TYPE_CONCEPT_ID to delineate between prescriptions written vs. prescriptions dispensed vs. medication history vs. patient-reported exposure, etc. Choose the drug_type_concept_id that best represents the provenance of the record, for example whether it came from a record of a prescription written or physician administered drug. Accepted Concepts. A more detailed explanation of each Type Concept can be found on the vocabulary wiki. integer Yes No Yes CONCEPT Type Concept
stop_reason The reason a person stopped a medication as it is represented in the source. Reasons include regimen completed, changed, removed, etc. This field will be retired in v6.0. This information is often not populated in source data and it is a valid etl choice to leave it blank if the information does not exist. varchar(20) No No No
refills This is only filled in when the record is coming from a prescription written this field is meant to represent intended refills at time of the prescription. integer No No No
quantity To find the dose form of a drug the RELATIONSHIP table can be used where the relationship_id is ‘Has dose form’. If liquid, quantity stands for the total amount dispensed or ordered of ingredient in the units given by the drug_strength table. If the unit from the source data does not align with the unit in the DRUG_STRENGTH table the quantity should be converted to the correct unit given in DRUG_STRENGTH. For clinical drugs with fixed dose forms (tablets etc.) the quantity is the number of units/tablets/capsules prescribed or dispensed (can be partial, but then only 1/2 or 1/3, not 0.01). Clinical drugs with divisible dose forms (injections) the quantity is the amount of ingredient the patient got. For example, if the injection is 2mg/mL but the patient got 80mL then quantity is reported as 160. Quantified clinical drugs with divisible dose forms (prefilled syringes), the quantity is the amount of ingredient similar to clinical drugs. Please see how to calculate drug dose for more information. float No No No
days_supply Days supply of the drug. This should be the verbatim days_supply as given on the prescription. If the drug is physician administered use duration end date if given or set to 1 as default if duration is not available. integer No No No
sig This is the verbatim instruction for the drug as written by the provider. Put the written out instructions for the drug as it is verbatim in the source, if available. varchar(MAX) No No No
route_concept_id The standard CONCEPT_ID that the ROUTE_SOURCE_VALUE maps to in the route domain. integer No No Yes CONCEPT Route
lot_number varchar(50) No No No
provider_id The Provider associated with drug record, e.g. the provider who wrote the prescription or the provider who administered the drug. The ETL may need to make a choice as to which PROVIDER_ID to put here. Based on what is available this may or may not be different than the provider associated with the overall VISIT_OCCURRENCE record, for example the ordering vs administering physician on an EHR record. integer No No Yes PROVIDER
visit_occurrence_id The Visit during which the drug was prescribed, administered or dispensed. To populate this field drug exposures must be explicitly initiated in the visit. integer No No Yes VISIT_OCCURRENCE
visit_detail_id The VISIT_DETAIL record during which the drug exposure occurred. For example, if the person was in the ICU at the time of the drug administration the VISIT_OCCURRENCE record would reflect the overall hospital stay and the VISIT_DETAIL record would reflect the ICU stay during the hospital visit. Same rules apply as for the VISIT_OCCURRENCE_ID. integer No No Yes VISIT_DETAIL
drug_source_value This field houses the verbatim value from the source data representing the drug exposure that occurred. For example, this could be an NDC or Gemscript code. This code is mapped to a Standard Drug Concept in the Standardized Vocabularies and the original code is stored here for reference. varchar(50) No No No
drug_source_concept_id This is the concept representing the drug source value and may not necessarily be standard. This field is discouraged from use in analysis because it is not required to contain Standard Concepts that are used across the OHDSI community, and should only be used when Standard Concepts do not adequately represent the source detail for the Drug necessary for a given analytic use case. Consider using DRUG_CONCEPT_ID instead to enable standardized analytics that can be consistent across the network. If the DRUG_SOURCE_VALUE is coded in the source data using an OMOP supported vocabulary put the concept id representing the source value here. integer No No Yes CONCEPT
route_source_value This field houses the verbatim value from the source data representing the drug route. This information may be called something different in the source data but the field is meant to contain a value indicating when and how a drug was given to a patient. This source value is mapped to a standard concept which is stored in the ROUTE_CONCEPT_ID field. varchar(50) No No No
dose_unit_source_value This field houses the verbatim value from the source data representing the dose unit of the drug given. This information may be called something different in the source data but the field is meant to contain a value indicating the unit of dosage of drug given to the patient. This is an older column and will be deprecated in an upcoming version. varchar(50) No No No

 drug_strength : Entity

 

DRUG_STRENGTH

Table Description

The DRUG_STRENGTH table contains structured content about the amount or concentration and associated units of a specific ingredient contained within a particular drug product. This table is supplemental information to support standardized analysis of drug utilization.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
drug_concept_id The Concept representing the Branded Drug or Clinical Drug Product. integer Yes No Yes CONCEPT
ingredient_concept_id The Concept representing the active ingredient contained within the drug product. Combination Drugs will have more than one record in this table, one for each active Ingredient. integer Yes No Yes CONCEPT
amount_value The numeric value or the amount of active ingredient contained within the drug product. float No No No
amount_unit_concept_id The Concept representing the Unit of measure for the amount of active ingredient contained within the drug product. integer No No Yes CONCEPT
numerator_value The concentration of the active ingredient contained within the drug product. float No No No
numerator_unit_concept_id The Concept representing the Unit of measure for the concentration of active ingredient. integer No No Yes CONCEPT
denominator_value The amount of total liquid (or other divisible product, such as ointment, gel, spray, etc.). float No No No
denominator_unit_concept_id The Concept representing the denominator unit for the concentration of active ingredient. integer No No Yes CONCEPT
box_size The number of units of Clinical Branded Drug or Quantified Clinical or Branded Drug contained in a box as dispensed to the patient. integer No No No
valid_start_date The date when the Concept was first recorded. The default value is 1-Jan-1970. date Yes No No
valid_end_date The date when then Concept became invalid. date Yes No No
invalid_reason Reason the concept was invalidated. Possible values are D (deleted), U (replaced with an update) or NULL when valid_end_date has the default value. varchar(1) No No No

 episode : Entity

 

EPISODE

Table Description

The EPISODE table aggregates lower-level clinical events (VISIT_OCCURRENCE, DRUG_EXPOSURE, PROCEDURE_OCCURRENCE, DEVICE_EXPOSURE) into a higher-level abstraction representing clinically and analytically relevant disease phases,outcomes and treatments. The EPISODE_EVENT table connects qualifying clinical events (VISIT_OCCURRENCE, DRUG_EXPOSURE, PROCEDURE_OCCURRENCE, DEVICE_EXPOSURE) to the appropriate EPISODE entry. For example cancers including their development over time, their treatment, and final resolution.

User Guide

Valid Episode Concepts belong to the ‘Episode’ domain. For cancer episodes please see [article], for non-cancer episodes please see [article]. If your source data does not have all episodes that are relevant to the therapeutic area, write only those you can easily derive from the data. It is understood that that table is not currently expected to be comprehensive.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
episode_id A unique identifier for each Episode. integer Yes Yes No
person_id The PERSON_ID of the PERSON for whom the episode is recorded. integer Yes No Yes PERSON
episode_concept_id The EPISODE_CONCEPT_ID represents the kind abstraction related to the disease phase, outcome or treatment. Choose a concept in the Episode domain that best represents the ongoing disease phase, outcome, or treatment. Please see [article] for cancers and [article] for non-cancers describing how these are defined. Accepted Concepts integer Yes No Yes CONCEPT Episode
episode_start_date The date when the Episode beings. Please see [article] for how to define an Episode start date. date Yes No No
episode_start_datetime The date and time when the Episode begins. datetime No No No
episode_end_date The date when the instance of the Episode is considered to have ended. Please see [article] for how to define an Episode end date. date No No No
episode_end_datetime The date when the instance of the Episode is considered to have ended. datetime No No No
episode_parent_id Use this field to find the Episode that subsumes the given Episode record. This is used in the case that an Episode are nested into each other. If there are multiple nested levels to how Episodes are represented, the EPISODE_PARENT_ID can be used to record this relationship. integer No No No
episode_number For sequences of episodes, this is used to indicate the order the episodes occurred. For example, lines of treatment could be indicated here. Please see [article] for the details of how to count episodes. integer No No No
episode_object_concept_id A Standard Concept representing the disease phase, outcome, or other abstraction of which the episode consists. For example, if the EPISODE_CONCEPT_ID is treatment regimen then the EPISODE_OBJECT_CONCEPT_ID should contain the chemotherapy regimen concept, like Afatinib monotherapy. Episode entries from the ‘Disease Episode’ concept class should have an episode_object_concept_id that comes from the Condition domain. Episode entries from the ‘Treatment Episode’ concept class should have an episode_object_concept_id that scome from the ‘Procedure’ domain or ‘Regimen’ concept class. integer Yes No Yes CONCEPT Procedure, Regimen
episode_type_concept_id This field can be used to determine the provenance of the Episode record, as in whether the episode was from an EHR system, insurance claim, registry, or other sources. Choose the EPISODE_TYPE_CONCEPT_ID that best represents the provenance of the record. Accepted Concepts. A more detailed explanation of each Type Concept can be found on the vocabulary wiki. integer Yes No Yes CONCEPT Type Concept
episode_source_value The source code for the Episdoe 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. varchar(50) No No No
episode_source_concept_id A foreign key to a Episode Concept that refers to the code used in the source. Given that the Episodes are user-defined it is unlikely that there will be a Source Concept available. If that is the case then set this field to zero. integer No No Yes CONCEPT

 episode_event : Entity

 

EPISODE_EVENT

Table Description

The EPISODE_EVENT table connects qualifying clinical events (such as CONDITION_OCCURRENCE, DRUG_EXPOSURE, PROCEDURE_OCCURRENCE, MEASUREMENT) to the appropriate EPISODE entry. For example, linking the precise location of the metastasis (cancer modifier in MEASUREMENT) to the disease episode.

User Guide

This connecting table is used instead of the FACT_RELATIONSHIP table for linking low-level events to abstracted Episodes.

ETL Conventions

Some episodes may not have links to any underlying clinical events. For such episodes, the EPISODE_EVENT table is not populated.

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
episode_id Use this field to link the EPISODE_EVENT record to its EPISODE. Put the EPISODE_ID that subsumes the EPISODE_EVENT record here. integer Yes No Yes EPISODE
event_id This field is the primary key of the linked record in the database. For example, if the Episode Event is a Condition Occurrence, then the CONDITION_OCCURRENCE_ID of the linked record goes in this field. Put the primary key of the linked record here. integer Yes No No
episode_event_field_concept_id This field is the CONCEPT_ID that identifies which table the primary key of the linked record came from. Put the CONCEPT_ID that identifies which table and field the EVENT_ID came from. Accepted Concepts integer Yes No Yes CONCEPT Metadata

 fact_relationship : Entity

 

FACT_RELATIONSHIP

Table Description

The FACT_RELATIONSHIP table contains records about the relationships between facts stored as records in any table of the CDM. Relationships can be defined between facts from the same domain, or different domains. Examples of Fact Relationships include: Person relationships (parent-child), care site relationships (hierarchical organizational structure of facilities within a health system), indication relationship (between drug exposures and associated conditions), usage relationships (of devices during the course of an associated procedure), or facts derived from one another (measurements derived from an associated specimen).

ETL Conventions

All relationships are directional, and each relationship is represented twice symmetrically within the FACT_RELATIONSHIP table. For example, two persons if person_id = 1 is the mother of person_id = 2 two records are in the FACT_RELATIONSHIP table (all strings in fact concept_id records in the Concept table: - Person, 1, Person, 2, parent of - Person, 2, Person, 1, child of

CDM Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
domain_concept_id_1 integer Yes No Yes CONCEPT
fact_id_1 integer Yes No No
domain_concept_id_2 integer Yes No Yes CONCEPT
fact_id_2 integer Yes No No