OMOP CDMv4 for PostgreSQL

-

create table care_site 
    ( 
     care_site_id integer  not null , 
     location_id integer , 
     organization_id integer , 
     place_of_service_concept_id integer , 
     care_site_source_value text , 
     place_of_service_source_value text 
    ) 
;



comment on table care_site is 'information about the site of care.'
;


comment on column care_site.care_site_id is 'a system-generated unique identifier for each care site. a care site is the place where the provider delivered the healthcare to the person.' 
;

comment on column care_site.location_id is 'a foreign key to the geographic location in the location table, where the detailed address information is stored.' 
;

comment on column care_site.organization_id is 'a foreign key to the organization in the organization table, where the detailed information is stored.' 
;

comment on column care_site.place_of_service_concept_id is 'a foreign key to the predefined concept identifier in the vocabulary reflecting the place of service.' 
;

comment on column care_site.care_site_source_value is 'the identifier for the care site as it appears in the source data, stored here for reference.' 
;

comment on column care_site.place_of_service_source_value is 'the source code for the place of service as it appears in the source data, stored here for reference.' 
;

alter table care_site 
    add constraint care_site_pk primary key ( care_site_id ) ;



create table cohort 
    ( 
     cohort_id integer  not null , 
     cohort_concept_id integer  not null , 
     cohort_start_date date  not null , 
     cohort_end_date date , 
     subject_id integer  not null , 
     stop_reason text 
    ) 
;



comment on table cohort is 'person, provider or visit cohorts.'
;


comment on column cohort.cohort_id is 'a system-generated unique identifier for each cohort record.' 
;

comment on column cohort.cohort_concept_id is 'a foreign key to a standard cohort concept identifier in the vocabulary. cohort concepts identify the cohorts: whether they are defined through persons, providers or visits, or any combination thereof.' 
;

comment on column cohort.cohort_start_date is 'the date when the cohort definition criteria for the person, provider or visit first match.' 
;

comment on column cohort.cohort_end_date is 'the date when the cohort definition criteria for the person, provider or visit no longer match or the cohort membership was terminated.' 
;

comment on column cohort.subject_id is 'a foreign key to the subject in the cohort. these could be referring to records in the person, provider, visit occurrence table.' 
;

comment on column cohort.stop_reason is 'the reason for the end of a cohort membership other than defined by the cohort definition criteria as it appears in the source data.' 
;

alter table cohort 
    add constraint cohort_pk primary key ( cohort_id ) ;



create table condition_era 
    ( 
     condition_era_id integer  not null , 
     person_id integer  not null , 
     condition_concept_id integer  not null , 
     condition_era_start_date date  not null , 
     condition_era_end_date date  not null , 
     condition_type_concept_id integer  not null , 
     condition_occurrence_count numeric (4) 
    ) 
;



comment on table condition_era is 'a diagnoses or conditions that over a period of time.'
;


comment on column condition_era.condition_era_id is 'a system-generated unique identifier for each condition era.' 
;

comment on column condition_era.person_id is 'a foreign key identifier to the person who is experiencing the condition during the condition era. the demographic details of that person are stored in the person table.' 
;

comment on column condition_era.condition_concept_id is 'a foreign key that refers to a standard condition concept identifier in the vocabulary. ' 
;

comment on column condition_era.condition_era_start_date is '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.' 
;

comment on column condition_era.condition_era_end_date is '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.' 
;

comment on column condition_era.condition_type_concept_id is ' a foreign key to the predefined concept identifier in the vocabulary reflecting the parameters used to construct the condition era. for a detailed current listing of condition types see appendix b: condition type concepts.' 
;

comment on column condition_era.condition_occurrence_count is 'the number of individual condition occurrences used to construct the condition era.' 
;

alter table condition_era 
    add constraint condition_era_pk primary key ( condition_era_id ) ;



create table condition_occurrence 
    ( 
     condition_occurrence_id integer  not null , 
     person_id integer  not null , 
     condition_concept_id integer  not null , 
     condition_start_date date  not null , 
     condition_end_date date , 
     condition_type_concept_id integer  not null , 
     stop_reason text , 
     associated_provider_id integer , 
     visit_occurrence_id integer , 
     condition_source_value text 
    ) 
;



comment on table condition_occurrence is 'a diagnosis or condition that has been recorded about a person at a certain time.'
;


comment on column condition_occurrence.condition_occurrence_id is 'a system-generated unique identifier for each condition occurrence event.' 
;

comment on column condition_occurrence.person_id is 'a foreign key identifier to the person who is experiencing the condition. the demographic details of that person are stored in the person table.' 
;

comment on column condition_occurrence.condition_concept_id is 'a foreign key that refers to a standard condition concept identifier in the vocabulary.  ' 
;

comment on column condition_occurrence.condition_start_date is 'the date when the instance of the condition is recorded.' 
;

comment on column condition_occurrence.condition_end_date is 'the date when the instance of the condition is last
recorded.' 
;

comment on column condition_occurrence.condition_type_concept_id is 'a foreign key to the predefined concept identifier in the vocabulary reflecting the source data from which the condition was recorded, the level of standardization, and the type of occurrence. conditions are defined as primary or secondary diagnoses, problem lists and person statuses. for a detailed current listing of condition types see appendix b: condition type concepts.' 
;

comment on column condition_occurrence.stop_reason is 'the reason, if available, that the condition was no longer recorded, as indicated in the source data. valid values include discharged, resolved, etc.' 
;

comment on column condition_occurrence.associated_provider_id is 'a foreign key to the provider in the provider table who was responsible for determining (diagnosing) the condition.' 
;

comment on column condition_occurrence.visit_occurrence_id is 'a foreign key to the visit in the visit table during which the condition was determined (diagnosed).' 
;

comment on column condition_occurrence.condition_source_value is 'the source code for the condition as it appears in the source data. this code is mapped to a standard condition concept in the vocabulary and the original code is , stored here for reference. condition source codes are typically icd-9-cm diagnosis codes from medical claims or discharge status/disposition codes from ehrs.' 
;

alter table condition_occurrence 
    add constraint condition_occurrence_pk primary key ( condition_occurrence_id ) ;



create table death 
    ( 
     person_id integer  not null , 
     death_date date  not null , 
     death_type_concept_id integer  not null , 
     cause_of_death_concept_id integer , 
     cause_of_death_source_value text 
    ) 
;



comment on table death is 'time and cause of death of the person. '
;


comment on column death.person_id is 'system-generated foreign key identifier to the deceased person. the demographic details of that person are stored in the person table.' 
;

comment on column death.death_date is 'the date the person deceased. if the precise date including 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.' 
;

comment on column death.death_type_concept_id is 'a foreign key referring to the predefined concept identifier in the vocabulary reflecting how the death was represented in the source data.' 
;

comment on column death.cause_of_death_concept_id is 'a foreign key referring to a standard concept identifier in the vocabulary for conditions.' 
;

comment on column death.cause_of_death_source_value is 'the source code for the cause of death as it appears in the source data. this code is mapped to a standard concept in the vocabulary and the original code is , stored here for reference. ' 
;

alter table death 
    add constraint death_pk primary key ( person_id, death_type_concept_id ) ;



create table drug_cost 
    ( 
     drug_cost_id integer  not null , 
     drug_exposure_id integer  not null , 
     paid_copay numeric (8,2) , 
     paid_coinsurance numeric (8,2) , 
     paid_toward_deductible numeric (8,2) , 
     paid_by_payer numeric (8,2) , 
     paid_by_coordination_benefits numeric (8,2) , 
     total_out_of_pocket numeric (8,2) , 
     total_paid numeric (8,2) , 
     ingredient_cost numeric (8,2) , 
     dispensing_fee numeric (8,2) , 
     average_wholesale_price numeric (8,2) , 
     payer_plan_period_id integer 
    ) 
;



comment on table drug_cost is 'for each drug exposure record additional information about cost and payments.'
;


comment on column drug_cost.drug_cost_id is 'a system-generated unique identifier for each drug cost record.' 
;

comment on column drug_cost.drug_exposure_id is 'a foreign key identifier to the drug record for which cost data are recorded. ' 
;

comment on column drug_cost.paid_copay is 'the amount paid by the person as a fixed contribution to the expenses. copay does not contribute to the out_of_pocket expenses.' 
;

comment on column drug_cost.paid_coinsurance is 'the amount paid by the person as a joint assumption of risk. typically, this is a percentage of the expenses defined by the payer plan (policy) after the person''s deductible is exceeded.' 
;

comment on column drug_cost.paid_toward_deductible is 'the amount paid by the person that is counted toward the deductible defined by the payer plan (policy).' 
;

comment on column drug_cost.paid_by_payer is 'the amount paid by the payer (insurer). if there is more than one payer, several drug_cost records indicate that fact.' 
;

comment on column drug_cost.paid_by_coordination_benefits is 'the amount paid by a secondary payer through the coordination of benefits.' 
;

comment on column drug_cost.total_out_of_pocket is 'the total amount paid by the person as a share of the expenses, excluding the copay.' 
;

comment on column drug_cost.total_paid is 'the total amount paid for the expenses of drug exposure.' 
;

comment on column drug_cost.ingredient_cost is 'the portion of the drug expenses due to the cost charged by the manufacturer for the drug, typically a percentage of the average wholesale price.' 
;

comment on column drug_cost.dispensing_fee is 'the portion of the drug expenses due to the dispensing fee charged by the pharmacy, typically a fixed amount.' 
;

comment on column drug_cost.average_wholesale_price is 'list price of a drug set by the manufacturer.' 
;

comment on column drug_cost.payer_plan_period_id is 'a foreign key to the payer_plan_period table, where the details of the payer, plan and family are stored.' 
;

alter table drug_cost 
    add constraint drug_cost_pk primary key ( drug_cost_id ) ;



create table drug_era 
    ( 
     drug_era_id integer  not null , 
     person_id integer  not null , 
     drug_concept_id integer  not null , 
     drug_era_start_date date  not null , 
     drug_era_end_date date  not null , 
     drug_type_concept_id integer  not null , 
     drug_exposure_count numeric (4) 
    ) 
;



comment on table drug_era is 'association between a person and a drug over a specific time period.'
;


comment on column drug_era.drug_era_id is 'a system-generated unique identifier for each drug era.' 
;

comment on column drug_era.person_id is 'a foreign key identifier to the person who is subjected to the drug during the drug era. the demographic details of that person are stored in the person table.' 
;

comment on column drug_era.drug_concept_id is 'a foreign key that refers to a standard concept identifier in the vocabulary for the drug concept.' 
;

comment on column drug_era.drug_era_start_date is 'the start date for the drug era constructed from the individual instances of drug exposures. it is the start date of the very first chronologically recorded instance of utilization of a drug.' 
;

comment on column drug_era.drug_era_end_date is 'the end date for the drug era constructed from the individual instance of drug exposures. it is the end date of the final continuously recorded instance of utilization of a drug.' 
;

comment on column drug_era.drug_type_concept_id is ' a foreign key to the predefined concept identifier in the vocabulary reflecting the parameters used to construct the drug era. for a detailed current listing of drug types see appendix a: drug type codes.' 
;

comment on column drug_era.drug_exposure_count is 'the number of individual drug exposure occurrences used to construct the drug era.' 
;

alter table drug_era 
    add constraint drug_era_pk primary key ( drug_era_id ) ;



create table drug_exposure 
    ( 
     drug_exposure_id integer  not null , 
     person_id integer  not null , 
     drug_concept_id integer  not null , 
     drug_exposure_start_date date  not null , 
     drug_exposure_end_date date , 
     drug_type_concept_id integer  not null , 
     stop_reason text , 
     refills numeric (3) , 
     quantity numeric (4) , 
     days_supply numeric (4) , 
     sig text , 
     prescribing_provider_id integer , 
     visit_occurrence_id integer , 
     relevant_condition_concept_id integer , 
     drug_source_value text 
    ) 
;



comment on table drug_exposure is 'association between a person and a drug at a specific time.'
;


comment on column drug_exposure.drug_exposure_id is 'a system-generated unique identifier for each drug utilization event.' 
;

comment on column drug_exposure.person_id is 'system-generated foreign key identifier for the person who is subjected to the drug. the demographic details of that person are stored in the person table.' 
;

comment on column drug_exposure.drug_concept_id is 'a foreign key that refers to a standard concept identifier in the vocabulary for the drug concept.' 
;

comment on column drug_exposure.drug_exposure_start_date is '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.' 
;

comment on column drug_exposure.drug_exposure_end_date is 'the end date for the current instance of drug utilization. it is not available from all sources.' 
;

comment on column drug_exposure.drug_type_concept_id is ' a foreign key to the predefined concept identifier in the vocabulary reflecting the type of drug exposure recorded. it indicates how the drug exposure was represented in the source data: as medication history, filled prescriptions, etc. for a detailed current listing of drug types see appendix a: drug type codes' 
;

comment on column drug_exposure.stop_reason is 'the reason the medication was stopped, where available. reasons include regimen completed, changed, removed, etc..' 
;

comment on column drug_exposure.refills is 'the number of refills after the initial prescription. the initial prescription is not counted,  values start with 0.' 
;

comment on column drug_exposure.quantity is 'the quantity of drug as recorded in the original prescription or dispensing record.' 
;

comment on column drug_exposure.days_supply is 'the number of days of supply of the medication as recorded in the original prescription or dispensing record.' 
;

comment on column drug_exposure.sig is 'the directions ("signetur") on the drug prescription as recorded in the original prescription (and printed on the container) or dispensing record.' 
;

comment on column drug_exposure.visit_occurrence_id is 'a foreign key to the visit in the visit table during which the drug exposure initiated.' 
;

comment on column drug_exposure.relevant_condition_concept_id is 'a foreign key to the predefined concept identifier in the vocabulary reflecting the condition that was the cause for initiation of the drug exposure. note that this is not a direct reference to a specific condition record in the condition table, but rather a condition concept in the vocabulary.' 
;

comment on column drug_exposure.drug_source_value is 'the source code for the drug as it appears in the source data. this code is mapped to a standard drug concept in the vocabulary and the original code is , stored here for reference.' 
;

alter table drug_exposure 
    add constraint drug_exposure_pk primary key ( drug_exposure_id ) ;



create table location 
    ( 
     location_id integer  not null , 
     address_1 text , 
     address_2 text , 
     city text , 
     state char (2) , 
     zip text , 
     county text , 
     location_source_value text 
    ) 
;



comment on table location is 'physical addresses.'
;


comment on column location.location_id is 'a system-generated unique identifier for each geographic location.' 
;

comment on column location.address_1 is 'the address field 1, typically used for the street address, as it appears in the source data.' 
;

comment on column location.address_2 is 'the address field 2, typically used for additional detail such as buildings, suites, floors, as it appears in the source data.' 
;

comment on column location.city is 'the city field as it appears in the source data.' 
;

comment on column location.state is 'the state field as it appears in the source data.' 
;

comment on column location.zip is 'the zip code. for us addresses, valid zip codes can be 3, 5 or 9 digits long, depending on the source data.' 
;

comment on column location.county is 'the county. the county information is necessary because not all zip codes fall into one and the same county.' 
;

comment on column location.location_source_value is 'the verbatim information that is used to uniquely identify the location as it appears in the source data.' 
;

alter table location 
    add constraint location_pk primary key ( location_id ) ;



create table observation 
    ( 
     observation_id integer  not null , 
     person_id integer  not null , 
     observation_concept_id integer  not null , 
     observation_date date  not null , 
     observation_time date , 
     value_as_number numeric (14,3) , 
     value_as_string text , 
     value_as_concept_id integer , 
     unit_concept_id integer , 
     range_low numeric (14,3) , 
     range_high numeric (14,3) , 
     observation_type_concept_id integer  not null , 
     associated_provider_id integer  not null , 
     visit_occurrence_id integer , 
     relevant_condition_concept_id integer , 
     observation_source_value text , 
     units_source_value text 
    ) 
;



comment on table observation is 'symptoms, clinical observations, lab tests etc. about the person.'
;


comment on column observation.observation_id is 'a system-generated unique identifier for each observation.' 
;

comment on column observation.person_id is 'a foreign key identifier to the person about whom the observation was recorded. the demographic details of that person are stored in the person table.' 
;

comment on column observation.observation_concept_id is 'a foreign key to the standard observation concept identifier in the vocabulary. ' 
;

comment on column observation.observation_date is 'the date of the observation' 
;

comment on column observation.observation_time is 'the time of the observation' 
;

comment on column observation.value_as_number is 'the observation result stored as a number. this is applicable to observations where the result is expressed as a numeric value.' 
;

comment on column observation.value_as_string is 'the observation result stored as a string. this is applicable to observations where the result is expressed as verbatim text, such as in radiology or pathology report' 
;

comment on column observation.value_as_concept_id is 'a foreign key to an observation result stored as a concept identifier. this is applicable to observations where the result can be expressed as a standard concept from the vocabulary (e.g., positive/negative, present/absent, low/high, etc.).' 
;

comment on column observation.unit_concept_id is 'a foreign key to a standard concept identifier of measurement units in the vocabulary.' 
;

comment on column observation.range_low is 'the lower limit of the normal range of the observation. it is not applicable if the observation results are non-numeric or categorical, and must be in the same units of measure as the observation value.' 
;

comment on column observation.range_high is 'the upper limit of the normal range of the observation. it is not applicable if the observation results are non-numeric or categorical, and must be in the same units of measure as the observation value.' 
;

comment on column observation.observation_type_concept_id is 'a foreign key to the predefined concept identifier in the vocabulary reflecting the type of the observation.' 
;

comment on column observation.associated_provider_id is 'a foreign key to the provider in the provider table who was responsible for making the observation.' 
;

comment on column observation.visit_occurrence_id is 'a foreign key to the visit in the visit table during which the observation was recorded.' 
;

comment on column observation.relevant_condition_concept_id is 'a foreign key to the predefined concept identifier in the vocabulary reflecting the condition that was associated with the observation. note that this is not a direct reference to a specific condition record in the condition table, but rather a condition concept in the vocabulary.' 
;

comment on column observation.observation_source_value is 'the observation code as it appears in the source data. this code is mapped to a standard concept in the vocabulary and the original code is , stored here for reference.' 
;

comment on column observation.units_source_value is 'the source code for the unit as it appears in the source data. this code is mapped to a standard unit concept in the vocabulary and the original code is , stored here for reference. ' 
;
create index observation_person_idx on observation 
    ( 
     person_id asc , 
     observation_concept_id asc 
    ) 
;

alter table observation 
    add constraint observation_pk primary key ( observation_id ) ;



create table observation_period 
    ( 
     observation_period_id integer  not null , 
     person_id integer  not null , 
     observation_period_start_date date  not null , 
     observation_period_end_date date  not null 
    ) 
;



comment on table observation_period is 'time intervals at which health care information may be available.'
;


comment on column observation_period.observation_period_id is 'a system-generated unique identifier for each observation period.' 
;

comment on column observation_period.person_id is 'a foreign key identifier to the person for whom the observation period is defined. the demographic details of that person are stored in the person table.' 
;

comment on column observation_period.observation_period_start_date is 'the start date of the observation period for which data are available from the data source.' 
;

comment on column observation_period.observation_period_end_date is 'the end date of the observation period for which data are available from the data source.' 
;
create unique index observation_period_person on observation_period 
    ( 
     person_id asc , 
     observation_period_start_date asc 
    ) 
;

alter table observation_period 
    add constraint observation_period_pk primary key ( observation_period_id ) ;



create table organization 
    ( 
     organization_id integer  not null , 
     place_of_service_concept_id integer , 
     location_id integer , 
     organization_source_value text , 
     place_of_service_source_value text 
    ) 
;



comment on table organization is 'information about health care organizations. '
;


comment on column organization.organization_id is 'a system-generated unique identifier for each organization. here, an organization is defined as a collection of one or more care sites that share a single ehr database.' 
;

comment on column organization.place_of_service_concept_id is 'a foreign key that refers to a place of service concept identifier in the vocabulary.' 
;

comment on column organization.location_id is 'a foreign key to the geographic location of the administrative offices in the location table, where the detailed address information is stored.' 
;

comment on column organization.organization_source_value is 'the identifier for the organization in the source data , stored here for reference.' 
;

comment on column organization.place_of_service_source_value is 'the source code for the place of service as it appears in the source data, stored here for reference.' 
;
create index organization_oraganization_pos on organization 
    ( 
     organization_source_value asc , 
     place_of_service_source_value asc 
    ) 
;

alter table organization 
    add constraint organization_pk primary key ( organization_id ) ;



create table payer_plan_period 
    ( 
     payer_plan_period_id integer  not null , 
     person_id integer  not null , 
     payer_plan_period_start_date date  not null , 
     payer_plan_period_end_date date  not null , 
     payer_source_value text , 
     plan_source_value text , 
     family_source_value text 
    ) 
;



comment on table payer_plan_period is 'information about the coverage plan of the payer.'
;


comment on column payer_plan_period.payer_plan_period_id is 'a system-generated identifier for each unique combination of payer, plan, family code and time span' 
;

comment on column payer_plan_period.person_id is 'a foreign key identifier to the person covered by the payer. the demographic details of that person are stored in the person table.' 
;

comment on column payer_plan_period.payer_plan_period_start_date is 'the start date of the payer plan period.' 
;

comment on column payer_plan_period.payer_plan_period_end_date is 'the end date of the payer plan period defined for the person.' 
;

comment on column payer_plan_period.payer_source_value is 'the source code for the payer as it appears in the source data.' 
;

comment on column payer_plan_period.plan_source_value is 'the source code for the person''s coverage plan as it appears in the source data.' 
;

comment on column payer_plan_period.family_source_value is 'the source code for the person''s family as it appears in the source data.' 
;

alter table payer_plan_period 
    add constraint payer_plan_period_pk primary key ( payer_plan_period_id ) ;



create table person 
    ( 
     person_id integer  not null , 
     gender_concept_id integer  not null , 
     year_of_birth numeric (4)  not null , 
     month_of_birth numeric (2) , 
     day_of_birth numeric (2) , 
     race_concept_id integer , 
     ethnicity_concept_id integer , 
     location_id integer , 
     provider_id integer , 
     care_site_id integer , 
     person_source_value text , 
     gender_source_value text , 
     race_source_value text , 
     ethnicity_source_value text 
    ) 
;



comment on table person is 'demographic information about a person (patient).'
;


comment on column person.person_id is 'system-generated identifier to uniquely identify each person.' 
;

comment on column person.gender_concept_id is 'foreign key that refers to the standard concept code in the dictionary for the gender of the person.' 
;

comment on column person.year_of_birth is 'the year of birth of the person. for data sources with date of birth, only the year is extracted. for data sources where the year of birth is not available, the approximate year of birth is derived based on any age group categorization available.' 
;

comment on column person.month_of_birth is 'the month of birth of the person. for data sources that provide the precise date of birth, the month is extracted and stored in this field.' 
;

comment on column person.day_of_birth is 'the day of the month of birth of the person. for data sources that provide the precise date of birth, the day is extracted and stored in this field.' 
;

comment on column person.race_concept_id is 'a foreign key that refers to a standard concept identifier in the vocabulary for the race of the person.' 
;

comment on column person.ethnicity_concept_id is 'a foreign key that refers to the standard concept identifier in the vocabulary for the ethnicity of the person.' 
;

comment on column person.location_id is 'a foreign key to the place of residency for the person in the location table, where the detailed address information is stored.' 
;

comment on column person.provider_id is 'a foreign key to the primary care provider the person is seeing in the provider table.' 
;

comment on column person.care_site_id is 'a foreign key to the primary care site in the care site table, where the details of the care site are stored.' 
;

comment on column person.person_source_value is 'an encrypted key derived from the person identifier in the source data. this is necessary when a drug safety issue requires a link back to the person data at the source dataset. no value with any medical or demographic significance must be stored.
' 
;

comment on column person.gender_source_value is 'the source code for the gender of the person as it appears in the source data. the person gender is mapped to a standard gender concept in the vocabulary and the corresponding concept identifier is, stored here for reference.' 
;

comment on column person.race_source_value is 'the source code for the race of the person as it appears in the source data. the person race is mapped to a standard race concept in the vocabulary and the original code is, stored here for reference.' 
;

comment on column person.ethnicity_source_value is 'the source code for the ethnicity of the person as it appears in the source data. the person ethnicity is mapped to a standard ethnicity concept in the vocabulary and the original code is, stored here for reference.' 
;

alter table person 
    add constraint person_pk primary key ( person_id ) ;



create table procedure_cost 
    ( 
     procedure_cost_id integer  not null , 
     procedure_occurrence_id integer  not null , 
     paid_copay numeric (8,2) , 
     paid_coinsurance numeric (8,2) , 
     paid_toward_deductible numeric (8,2) , 
     paid_by_payer numeric (8,2) , 
     paid_by_coordination_benefits numeric (8,2) , 
     total_out_of_pocket numeric (8,2) , 
     total_paid numeric (8,2) , 
     disease_class_concept_id integer , 
     revenue_code_concept_id integer , 
     payer_plan_period_id integer , 
     disease_class_source_value text , 
     revenue_code_source_value text 
    ) 
;



comment on table procedure_cost is 'for each procedure additional information about cost and payments.'
;


comment on column procedure_cost.procedure_cost_id is 'a system-generated unique identifier for each procedure cost record.' 
;

comment on column procedure_cost.procedure_occurrence_id is 'a foreign key identifier to the procedure record for which cost data are recorded. ' 
;

comment on column procedure_cost.paid_copay is 'the amount paid by the person as a fixed contribution to the expenses. copay does not contribute to the out_of_pocket expenses.' 
;

comment on column procedure_cost.paid_coinsurance is 'the amount paid by the person as a joint assumption of risk. typically, this is a percentage of the expenses defined by the payer plan (policy) after the person''s deductible is exceeded.' 
;

comment on column procedure_cost.paid_toward_deductible is 'the amount paid by the person that is counted toward the deductible defined by the payer plan (policy).' 
;

comment on column procedure_cost.paid_by_payer is 'the amount paid by the payer (insurer). if there is more than one payer, several procedure_cost records indicate that fact.' 
;

comment on column procedure_cost.paid_by_coordination_benefits is 'the amount paid by a secondary payer through the coordination of benefits.' 
;

comment on column procedure_cost.total_out_of_pocket is 'the total amount paid by the person as a share of the expenses, excluding the copay.' 
;

comment on column procedure_cost.total_paid is 'the total amount paid for the expenses of the procedure.' 
;

comment on column procedure_cost.disease_class_concept_id is 'a foreign key referring to a standard concept identifier in the vocabulary for disease classes, such as drgs and apcs.' 
;

comment on column procedure_cost.revenue_code_concept_id is 'a foreign key referring to a standard concept identifier in the vocabulary for revenue codes.' 
;

comment on column procedure_cost.payer_plan_period_id is 'a foreign key to the payer_plan_period table, where the details of the payer, plan and family are stored.' 
;

comment on column procedure_cost.disease_class_source_value is 'the source code for the disease class as it appears in the source data , stored here for reference.' 
;

comment on column procedure_cost.revenue_code_source_value is 'the source code for the revenue code as it appears in the source data , stored here for reference.' 
;

alter table procedure_cost 
    add constraint procedure_cost_pk primary key ( procedure_cost_id ) ;



create table procedure_occurrence 
    ( 
     procedure_occurrence_id integer  not null , 
     person_id integer  not null , 
     procedure_concept_id integer  not null , 
     procedure_date date  not null , 
     procedure_type_concept_id integer  not null , 
     associated_provider_id integer , 
     visit_occurrence_id integer , 
     relevant_condition_concept_id integer , 
     procedure_source_value text 
    ) 
;



comment on table procedure_occurrence is 'procedures carried out on the person.'
;


comment on column procedure_occurrence.procedure_occurrence_id is 'a system-generated unique identifier for each procedure occurrence.' 
;

comment on column procedure_occurrence.person_id is '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.' 
;

comment on column procedure_occurrence.procedure_concept_id is 'a foreign key that refers to a standard procedure concept identifier in the vocabulary. ' 
;

comment on column procedure_occurrence.procedure_date is 'the date on which the procedure was performed.' 
;

comment on column procedure_occurrence.procedure_type_concept_id is 'a foreign key to the predefined concept identifier in the vocabulary reflecting the type of the procedure. for a detailed current listing of procedure types see appendix c: procedure type concepts.' 
;

comment on column procedure_occurrence.associated_provider_id is 'a foreign key to the provider in the provider table who was responsible for carrying out the procedure.' 
;

comment on column procedure_occurrence.visit_occurrence_id is 'a foreign key to the visit in the visit table during which the procedure was carried out.' 
;

comment on column procedure_occurrence.relevant_condition_concept_id is 'a foreign key to the predefined concept identifier in the vocabulary reflecting the condition that was the cause for initiation of the procedure. note that this is not a direct reference to a specific condition record in the condition table, but rather a condition concept in the vocabulary.' 
;

comment on column procedure_occurrence.procedure_source_value is 'the source code for the procedure as it appears in the source data. this code is mapped to a standard procedure concept in the vocabulary and the original code is , stored here for reference. procedure source codes are typically icd-9-proc, cpt-4 or hcpcs codes.' 
;

alter table procedure_occurrence 
    add constraint procedure_occurrence_pk primary key ( procedure_occurrence_id ) ;



create table provider 
    ( 
     provider_id integer  not null , 
     npi text , 
     dea text , 
     specialty_concept_id integer , 
     care_site_id integer , 
     provider_source_value text  not null , 
     specialty_source_value text 
    ) 
;



comment on table provider is 'information about health care providers.'
;


comment on column provider.provider_id is 'a system-generated unique identifier for each provider.' 
;

comment on column provider.npi is 'the national provider identifier (npi) of the provider.' 
;

comment on column provider.dea is 'the drug enforcement administration (dea) number of the provider.' 
;

comment on column provider.specialty_concept_id is 'a foreign key to a standard provider''s specialty concept identifier in the vocabulary. ' 
;

comment on column provider.care_site_id is 'a foreign key to the main care site where the provider is practicing.' 
;

comment on column provider.provider_source_value is 'the identifier used for the provider in the source data.rmation that is used to uniquely identify the provider as it appears in the source data , stored here for reference.' 
;

comment on column provider.specialty_source_value is 'the source code for the provider specialty as it appears in the source data , stored here for reference.' 
;

alter table provider 
    add constraint provider_pk primary key ( provider_id ) ;



create table visit_occurrence 
    ( 
     visit_occurrence_id integer  not null , 
     person_id integer  not null , 
     visit_start_date date  not null , 
     visit_end_date date  not null , 
     place_of_service_concept_id integer  not null , 
     care_site_id integer , 
     place_of_service_source_value text 
    ) 
;



comment on table visit_occurrence is 'visits for health care services of the person.'
;


comment on column visit_occurrence.visit_occurrence_id is 'a system-generated unique identifier for each person''s visit or encounter at a healthcare provider.' 
;

comment on column visit_occurrence.person_id is '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.' 
;

comment on column visit_occurrence.visit_start_date is 'the start date of the visit.' 
;

comment on column visit_occurrence.visit_end_date is 'the end date of the visit. if this is a one-day visit the end date should match the start date.' 
;

comment on column visit_occurrence.place_of_service_concept_id is 'a foreign key that refers to a place of service concept identifier in the vocabulary.' 
;

comment on column visit_occurrence.care_site_id is 'a foreign key to the care site in the care site table that was visited.' 
;

comment on column visit_occurrence.place_of_service_source_value is 'the source code used to reflect the type or source of the visit in the source data. valid entries include office visits, hospital admissions, etc. these source codes can also be type-of service codes and activity type codes.' 
;
create index visit_occurrence_peson_date on visit_occurrence 
    ( 
     person_id asc , 
     visit_start_date asc 
    ) 
;

alter table visit_occurrence 
    add constraint visit_occurrence_pk primary key ( visit_occurrence_id ) ;




alter table care_site 
    add constraint care_site_location_fk foreign key 
    ( 
     location_id
    ) 
    references location 
    ( 
     location_id
    ) 
;


alter table care_site 
    add constraint care_site_organization_fk foreign key 
    ( 
     organization_id
    ) 
    references organization 
    ( 
     organization_id
    ) 
;


alter table condition_era 
    add constraint condition_era_person_fk foreign key 
    ( 
     person_id
    ) 
    references person 
    ( 
     person_id
    ) 
;


alter table condition_occurrence 
    add constraint condition_occurrence_person_fk foreign key 
    ( 
     person_id
    ) 
    references person 
    ( 
     person_id
    ) 
;


alter table condition_occurrence 
    add constraint condition_provider_fk foreign key 
    ( 
     associated_provider_id
    ) 
    references provider 
    ( 
     provider_id
    ) 
;


alter table condition_occurrence 
    add constraint condition_visit_fk foreign key 
    ( 
     visit_occurrence_id
    ) 
    references visit_occurrence 
    ( 
     visit_occurrence_id
    ) 
;


alter table death 
    add constraint death_person_fk foreign key 
    ( 
     person_id
    ) 
    references person 
    ( 
     person_id
    ) 
;


alter table drug_cost 
    add constraint drug_cost_drug_exposure_fk foreign key 
    ( 
     drug_exposure_id
    ) 
    references drug_exposure 
    ( 
     drug_exposure_id
    ) 
;


alter table drug_cost 
    add constraint drug_cost_payer_plan_period_fk foreign key 
    ( 
     payer_plan_period_id
    ) 
    references payer_plan_period 
    ( 
     payer_plan_period_id
    ) 
;


alter table drug_era 
    add constraint drug_era_person_fk foreign key 
    ( 
     person_id
    ) 
    references person 
    ( 
     person_id
    ) 
;


alter table drug_exposure 
    add constraint drug_exposure_person_fk foreign key 
    ( 
     person_id
    ) 
    references person 
    ( 
     person_id
    ) 
;


alter table drug_exposure 
    add constraint drug_exposure_provider_fk foreign key 
    ( 
     prescribing_provider_id
    ) 
    references provider 
    ( 
     provider_id
    ) 
;


alter table drug_exposure 
    add constraint drug_visit_fk foreign key 
    ( 
     visit_occurrence_id
    ) 
    references visit_occurrence 
    ( 
     visit_occurrence_id
    ) 
;


alter table observation 
    add constraint observation_person_fk foreign key 
    ( 
     person_id
    ) 
    references person 
    ( 
     person_id
    ) 
;


alter table observation_period 
    add constraint observation_period_person_fk foreign key 
    ( 
     person_id
    ) 
    references person 
    ( 
     person_id
    ) 
;


alter table observation 
    add constraint observation_provider_fk foreign key 
    ( 
     associated_provider_id
    ) 
    references provider 
    ( 
     provider_id
    ) 
;


alter table observation 
    add constraint observation_visit_fk foreign key 
    ( 
     visit_occurrence_id
    ) 
    references visit_occurrence 
    ( 
     visit_occurrence_id
    ) 
;


alter table organization 
    add constraint organization_location_fk foreign key 
    ( 
     location_id
    ) 
    references location 
    ( 
     location_id
    ) 
;


alter table payer_plan_period 
    add constraint payer_plan_period_person_fk foreign key 
    ( 
     person_id
    ) 
    references person 
    ( 
     person_id
    ) 
;


alter table person 
    add constraint person_care_site_fk foreign key 
    ( 
     care_site_id
    ) 
    references care_site 
    ( 
     care_site_id
    ) 
;


alter table person 
    add constraint person_location_fk foreign key 
    ( 
     location_id
    ) 
    references location 
    ( 
     location_id
    ) 
;


alter table person 
    add constraint person_provider_fk foreign key 
    ( 
     provider_id
    ) 
    references provider 
    ( 
     provider_id
    ) 
;


alter table procedure_cost 
    add constraint procedure_cost_payer_plan_fk foreign key 
    ( 
     payer_plan_period_id
    ) 
    references payer_plan_period 
    ( 
     payer_plan_period_id
    ) 
;


alter table procedure_cost 
    add constraint procedure_cost_procedure_fk foreign key 
    ( 
     procedure_occurrence_id
    ) 
    references procedure_occurrence 
    ( 
     procedure_occurrence_id
    ) 
;


alter table procedure_occurrence 
    add constraint procedure_occurrence_person_fk foreign key 
    ( 
     person_id
    ) 
    references person 
    ( 
     person_id
    ) 
;


alter table procedure_occurrence 
    add constraint procedure_provider_fk foreign key 
    ( 
     associated_provider_id
    ) 
    references provider 
    ( 
     provider_id
    ) 
;


alter table procedure_occurrence 
    add constraint procedure_visit_fk foreign key 
    ( 
     visit_occurrence_id
    ) 
    references visit_occurrence 
    ( 
     visit_occurrence_id
    ) 
;


alter table provider 
    add constraint provider_care_site_fk foreign key 
    ( 
     care_site_id
    ) 
    references care_site 
    ( 
     care_site_id
    ) 
;


alter table visit_occurrence 
    add constraint visit_occurrence_person_fk foreign key 
    ( 
     person_id
    ) 
    references person 
    ( 
     person_id
    ) 
;