Migration from the preview relational schema

The latest relational schema includes all resource types that were available in the preview schema, with 14 additional resource types. The new types are listed below in the section "Tables for new resource types".

Changes to existing tables

Upon reviewing the preview schema for usability gaps, our changes have primary centered around the following:

• We revamped the way codeable concept relationships work. Instead of using a single CODEABLE_CONCEPT table across all resource types, selected codeable concepts have a dedicated dimension table.
• We have "top-leveled" additional columns to make them more quickly accessible.
• We discovered many columns and child tables that are either very sparsely populated or populated exclusively with data that doesn't provide information. These have been removed from the schema.
• We renamed many columns to follow consistent conventions and avoid keyword collisions.

condition

Column changes

• code_codeable_concept_id has been renamed to condition_code_id, which references the condition_code table.
• clinical_status_codeable_concept_id is removed. Use the clinical_status field to get the value directly.
• verification_status_codeable_concept_id is removed. Use the verification_status field to get the value directly.
• condition_severity_codeable_concept_id is removed.
• onset_date_time and onset_string are removed. Use the onset field.
• abatement_date_time and abatement_string are removed. Use the abatement field.
• asserter_patient_id and recorder_patient_id are removed as the asserter and recorder are always Practitioners in the current dataset.

Changes to related tables

• condition_body_site has been removed due to lack of data
• condition_category has been redefined to represent unique values of the category field (a CodeableConcept structure) in FHIR condition resources
• condition_category_coding is a new table containing all unique codings that reference condition_category
• condition_code is a new table that represents all unique values of code in FHIR condition resources
• codntiion_code_coding is a new table containing codings that reference condition_code
• condition_note: author_string has been removed due to lack of data. text has been renamed text_markdown to avoid colliding with the name of a SQL data type

condition_summary_snomed

This table has been renamed to lens_snomed_condition. This follows a convention of using lens as a prefix for all tables that contain Lens derived data.

Column changes to this table:

• clinical_status_codeable_concept_id has been removed. Use the clinical_status field to get the value directly.
• code_codeable_concept_id has been renamed lens_snomed_condition_code_id
• onset_date_time has been renamed onset

Changes to related tables

• lens_snomed_condition_code is a new table that represents all unique values of code on the FHIR condition resource
• lens_snomed_condition_code_coding contains all unique codings that reference lens_snomed_condition_code
• condition_summary_snomed_aggregated_from has been removed due to lack of use cases
• condition_summary_snomed_identitifier has been removed due to lack fo data

encounter

Column changes

• class_coding_id has been removed. Use class_code to retrieve an enriched version of the encounter class.
• service_type_codeable_concept_id has been removed
• hospitalization_admit_source_codeable_concept_id has been removed due to lack of data
• part_of_encounter_id has been removed due to lack of data
• type_display has been added as a humna-readable field of the encounter type
• location_id has been added to reference the associated Location
• hospitalization_discharge_disposition_codeable_concept_id has been renamed to encounter_hospitalization_discharge_disposition_id

Changes to related tables

• encounter_diagnosis now includes a use_code column containing a scalar value instead of use_codeable_concept_id, as most CodeableConcept values contained only a code element.
• encounter_reason_code now includes columns for the SNOMED, ICD-10-CM, and ICD-9-CM values, and a human-readable display field. The codeable_concept_id columns has been removed.
• encounter_participant TODO
• encounter_hospitalization_discharge_disposition is a new table that represents all unique values of encounter hospitalization discharge dispositions.
• encounter_hospitalization_discharge_disposition_coding contains the codings that reference encounter_hospitalization_discharge_disposition
• encounter_location has been removed. Use the location_id column on the encounter table instead.
• encounter_type is a new table that represents all unque values of the encounter type.
• encounter_type_coding contains all unique codings that reference encounter_type.

episode_of_care

This table has been renamed to lens_transition_of_care.

Column changes

• encounter_class_coding_id has been removed. Use the encounter_class_code column instead to get the extracted coding of the encounter class, and encounter_class_display for a human-readable display name for the encounter class.
• discharge_disposition_codeable_concept_id has been renamed transition_of_care_discharge_disposition_id

Changes to related tables

• episode_of_care_diagnosis has been renamed lens_transition_of_care_diagnosis. The column role_codeable_concept_id has been removed in favor of top-leveled columns role_code and role_display.
• episode_of_care_aggregated_from has been renamed lens_transition_of_care_aggregated_from
• episode_of_care_identifier has been renamed lens_transition_of_care_identifier
• episode_of_care_discharge_disposition has been renamed lens_transition_of_care_disposition
• episode_of_care_discharge_disposition_coding has been renamed lens_transition_of_care_disposition_coding
• episode_of_care_narrative has been renamed lens_transition_of_care_narrative

medication_dispense

Column changes

• days_supply_value has been renamed days_supply

Changes to related tables

• medication_dispense_performer has been removed because the medication dispenses in our data currently have at most one performer. Use the performer_actor_organization_id column on medication_dispense instead.
• medication_dispense_authorizing_prescription has been removed because the medication dispenses in our data currently have at most one authorizing prescription. Use the authorizing_prescription_medication_request_id column on medication_dispense instead.
• medication_dispense_dosage_instruction has been removed because the medication dispenses in our data currently have at most one dosage instruction. Use the dosage_instruction on medication_dispense instead to get the text of the instruction.

medication_statement

Column changes

• medication_codeable_concept_id has been renamed to medication_concept_id, and references the medication_concept table.
• context_display has been removed
• effective_period_start has been renamed effective_start
• effective_period_end has been renamed effective_end
• information_source_patient_id has been removed due to lack of data
• text_div and text_status have been removed
• medication_refills, medication_last_fill_date, and medication_prescribed_date have been removed as these columns are only populated on Lens resources

Changes to related tables

• medication_statement_derived_from has been removed as this data is only populated for Lens resources
• medication_statement_dosage has been removed as medication statements in our data currently have at most one dosage. Use the dosage_id column on the medication_statement table instead.
• medication_statement_reason_code has been removed. Use the medication_statement_reason_code_bridge table to join to medication_statement_reason_code_coding

medication_statement_summary_rxnorm

This table has been renamed to lens_rxnorm_medicationstatement. This follows a convention of using lens as a prefix for all tables that contain Lens derived data.

Column changes

• text_div and text_status have been removed.
• medication_codeable_concept_idhas been renamed to medication_concept_id, and references the medication_concept table.
• effective_period_start and effective_period_end have been removed due to lack of data.
• medication_refills has been renamed refills
• medication_last_fill_date has been renamed last_fill_date
• medication_prescribed_date has been renamed prescribed_date
• medication_days_supply has been renamed days_supply
• medication_quantity has been renamed quantity
• medication_last_prescriber_practitioner_id has been renamed last_prescriber_practitioner_id
• medication_last_prescriber_medication_request_id has been renamed last_prescriber_medication_request_id

Changes to related tables

• medication_statement_summary_rxnorm_aggregated_from has been removed due to lack fo use cases.
• medication_statement_summary_rxnorm_derived_from has been removed due to lack of use cases.
• medication_statement_summary_rxnorm_dosage has been removed. Use the dosage_id field on the lens_rxnorm_medicationstatement instead.

observation

Column changes

• text_div and text_status have been removed.
• code_codeable_concept_id has been renamed observation_code_id and references the observation_code table.
• value_codeable_concept_id has been renamed observation_value_concept_id and references the observation_value_concept table.
• data_absent_reason_codeable_concept_id has been removed. data_absent_reason contains the coding for the reason for data absence.
• body_site_codeable_concept_id has been renamed body_site_id and references the body_site table.
• method_codeable_concept_id has been renamed observation_method_id and references the observation_method table.

Changes to related tables

• observation_component has been removed.
• observation_interpreation no longer has codeable_concept_id, instead it encodes all unique values of the interpretation field on FHIR Observations, and can be joined to observation and observation_interpretation_coding.
• observation_note: text has been renamed text_markdown
• observation_reference_range has been removed. The high, low, unit, and value fields have been top-leveled on the observation table.

organization

Column changes: no columns have been altered to removed. Columns have been added for the organization type and address.

Changes to related tables

• organization_address has been removed. Use the address_line_1, address_line_2, address_city, address_state, and address_postal_code fields on organization.
• organization_contact has been removed due to lakc of data.
• organization_contact_telecom has been removed due to lack of data.

patient

Column changes:

• marital_status_codeable_concept_id has been removed. Use marital_status to get the coding value.
• deceased_boolean has been renamed to deceased. This column is automatically set to true if deceased_datetime is populated.

Changes to related tables

• patient_communication
  • language_preference_type_coding_id has been removed. Use preferred_type to get the coding directly.
  • patient_proficiency_coding_id has been removed. Use language_ability and language_ability_display to get the coding and display name of the langauge proficiency.
  • language_codeable_concept_id has been renamed to patient_communication_language_id and references the patient_communication_language table.
• patient_contact:
  • name_human_name_id has been removed. Use the name_family, name_given_, and name_given_2 fields directly on the patient_contact table.
  • address_id has been removed. Use the address_line_1, address_line_2, address_city, address_state, and address_postal_code fields on the patient_contact table.
• patient_contact_relationship has been removed. Use the relationship field on the patient_contact table to get the code representing the contact's relationship to the patient.
• patient_address: address_id has been removed. The patient's address info can be directly found on this table, instead of joining to a separate address table.

practitioner

Column changes

• active has been removed due to lack of data.

Changes to related tables

• practitioner_name has been removed. Use the name_family, name_given_1, and name_given_2 fields on the practitioner table.
• practitioner_address has been removed. Use the address_line_1, address_line_2, address_city, address_state, and address_postal_code fields on the practitioner table.
• practitioner_qualification: code_codeable_concept_id has been removed. Instead, this table now represents all unique values of the qualification field on FHIR Practitioners and can be joined to practitioner and practitioner_qualification_coding.

procedure

Column changes

• text_div and text_status have been removed.
• code_codeable_concept_id has been renamed procedure_code_id, and references the procedure_code table.
• performed_start is now a timestamp.
• performed_end is now a timestamp.
• code_text has been renamed code_display.

Changes to related tables

• procedure_body_site has been dropped. Use body_site_concept_id to join to the body_site table instead.

related_person

Column changes: no changed or removed columns. Columns have been added for the related person's name and adddress.

Changes to related tables

• related_person_relationship: codeable_concept_id has been removed. Instead, this table represents all unique values of the relationship field on RelatedPerson resources, and can be joined to related_person and related_person_relationship_coding.
• related_person_name has been removed. Use the name_family, name_given_1, and name_given_2 fields on the related_person table.
• related_person_address has been removed. Use the address_line_1, address_line_2, address_city, address_state, and address_postal_code fields on the related_person table.

Tables for new resource types

• allergy_intolerance
• appointment
• care_plan
• care_team
• consent
• coverage
• device
• device_use_statement
• diagnostic_report
• document_reference
• immunization
• medication
• medication_administration
• medication_request

Migration from rectangularized schema

Compared to the rectangularized schema, the relational schema follows a number of rules for predictability:

• Words in identifiers are separated by underscores
• All resource tables include a resource_json column. This column contains the raw FHIR resource, and serves as an "escape hatch" for fields that haven't been extracted to their own columns.
• Other than the resource_json column, all columns are scalar values.
• Tables can be joined to each other using foreign key relationships. Primary key columns are always named id, and foreign key columns are named after the table that they reference. For example, a column that references the patient table may be called patient_id, or subject_patient_id to specify that the referenced patient is the subject of the referencing resource.
• An exception to the _id column name rule is the column builder_id, which does not reference a table.
• The relational schema separates summarized Condition, MedicationStatement, and Encounter resources from the source data. The summarized resources are in tables that begin with the lens_ prefix.

Added and removed resource types

The relational schema includes the following resource types, which were not included in the rectangularized schema:

• Transition of Care (Based on FHIR EpisodeOfCare)
• Medication
• RelatedPerson

The tables for QuestionnaireResponse and Provenance have been removed due to lack of analytic use cases.