Difference between revisions of "Database Structure"

From OpenEMR Project Wiki
(recurring appointments in 4.2.1)
 
(40 intermediate revisions by 3 users not shown)
Line 1: Line 1:
==Database==
=Database=


==Overview==
[[File:OpenEmrOriginalSchema.pdf]] - Full Graphical Map of the current schema as of version 3.2.0 (note there have been many more table added since this, which are listed below).
[[File:OpenEmrOriginalSchema.pdf]] - Full Graphical Map of the current schema as of version 3.2.0 (note there have been many more table added since this, which are listed below).


Used DbSchema to reverse-engineer and auto-generate foreign keys - simulate column dependencies. Posting here for anyone that might find it useful. See at following address - scroll around the image is very large.
[[File:OpenEMR_5_0_2SchemaBreakdown.pdf‎]] - Breaks the current schema down into various categories to quickly see the tables by functionality for the upcoming version 5.0.2. Note many tables are missing as this is a work in progress.
http://htmlpreview.github.io/?https://github.com/fndtn357/openemr/blob/master/assets/openemrER.html
 
==Engine==
:For OpenEMR 5.0.0, engine was converted to InnoDB, except for following exception:
::*form_eye_mag (in InnoDB, was throwing following error on some newer OS's 'Row size too large (> 8126).')
::Above table was set to be skipped in the upgrade script auto InnoDB conversion here: https://github.com/openemr/openemr/blob/master/library/sql_upgrade_fx.php#L640
:For OpenEMR 5.0.2, the form_eye_mag table was removed; thus database became 100% InnoDB.
 
==Tables==


OpenEMR installs many database tables.  Following is a list, with brief descriptions:
OpenEMR installs many database tables.  Following is a list, with brief descriptions:


*Here is a googledoc that is actively undergoing development in order to hopefully replace below table listing:
**https://docs.google.com/spreadsheets/d/1V-5UhtVCwqckaFqwv1mXwu3ezqY2qPYZ3hI-DF3sag8/
<br>
*addresses - Contains street addresses for two other tables: insurance_companies and pharmacies.  So it is effectively an extension of those tables. This is inconsistent in that some other tables (e.g. facility, patient_data, users) have their addresses embedded. The "foreign_id" column equals the "id" of the foreign table, but curiously the addresses table does not identify the foreign table itself.
*addresses - Contains street addresses for two other tables: insurance_companies and pharmacies.  So it is effectively an extension of those tables. This is inconsistent in that some other tables (e.g. facility, patient_data, users) have their addresses embedded. The "foreign_id" column equals the "id" of the foreign table, but curiously the addresses table does not identify the foreign table itself.
*amc_misc_data - For tracking of some Automated Measure Calculations (AMC). '''(added in version 4.1.0)'''
*amc_misc_data - For tracking of some Automated Measure Calculations (AMC). '''(added in version 4.1.0)'''
*amendments - '''(added in version 4.2.0)'''
*amendments - '''(added in version 4.2.0)'''
*amendments_history - '''(added in version 4.2.0)'''
*amendments_history - '''(added in version 4.2.0)'''
*array - I don't see anything that uses this.
*api_token - For the api. '''(added in version 5.0.2)'''
*ar_activity – This is the primary repository for Accounts Receivable information.  In general it contains multiple rows per encounter (visit).  Each row corresponds to either a payment or an adjustment. Columns are:
*array - Not used in codebase. '''(added in version 2.8.2)''' '''(REMOVED in version 5.0.2)'''
*ar_activity – This is the primary repository for Accounts Receivable information.  In general it contains multiple rows per encounter (visit).  Each row corresponds to either a payment or an adjustment. '''(added in version 2.8.2)''' and columns are:
**pid, encounter: Identifies the visit.
**pid, encounter: Identifies the visit.
**sequence_no: A “line number” of the activity item for this visit.
**sequence_no: A “line number” of the activity item for this visit.
Line 24: Line 36:
**pay_amount: The amount paid, if this is a payment.
**pay_amount: The amount paid, if this is a payment.
**adj_amount: The amount of adjustment, if this is an adjustment.
**adj_amount: The amount of adjustment, if this is an adjustment.
*ar_session – Also for Accounts Receivable, this table is applicable to insurance EOBs.  Each row corresponds to a “posting session” in which a single payment covers a set of claims.  Columns are:
*ar_session – Also for Accounts Receivable, this table is applicable to insurance EOBs.  Each row corresponds to a “posting session” in which a single payment covers a set of claims.  '''(added in version 2.8.2)''' and columns are:
**session_id: A unique numeric identifier for the session.
**session_id: A unique numeric identifier for the session.
**payer_id: The ID of the payer in the insurance_companies table.
**payer_id: The ID of the payer in the insurance_companies table.
Line 35: Line 47:
*audit_details: Used by patient portal(s). '''(added in version 4.1.0)'''
*audit_details: Used by patient portal(s). '''(added in version 4.1.0)'''
*audit_master: Used by patient portal(s). '''(added in version 4.1.0)'''
*audit_master: Used by patient portal(s). '''(added in version 4.1.0)'''
*automatic_notification - For the patient automatic notification module (works with tables notification_settings and notification_log).
*automatic_notification - For the patient automatic notification module (works with tables notification_settings and notification_log). '''(added in version 4.1.0)'''
*background_services - For tracking background services '''(added in version 4.1.2)'''
*background_services - For tracking background services '''(added in version 4.1.2)'''
*batchcom - Records messages sent to groups of patients using the "Batch Communication Tool".
*batchcom - Records messages sent to groups of patients using the "Batch Communication Tool". '''(added in version 2.7.3)'''
*billing - Billing items.  Contains a row for every CPT4 code, HCPCS code, and ICD9 code recorded into an encounter.  In addition has a row for each co-pay entered.  The billing process also maintains state information in this table.
*billing - Billing items.  Contains a row for every CPT4 code, HCPCS code, and ICD10 code recorded into an encounter.  In addition has a row for each co-pay entered.  The billing process also maintains state information in this table.
*categories - Defines the tree of document category types. These are the category names that you see when viewing patient documents.  Note that the "parent" column is a reference to the "id" column of the same table, thus the tree structure.
*calendar_external - Used for calendar holiday import. '''(added in version 5.0.0)'''
*categories_seq - Holds the value of the last-used "id" for the categories table.
*categories - Defines the tree of document category types. These are the category names that you see when viewing patient documents.  Note that the "parent" column is a reference to the "id" column of the same table, thus the tree structure. '''(added in version 2.6.1)'''
*categories_to_documents - Cross-references the categories and documents tables.
*categories_seq - Holds the value of the last-used "id" for the categories table. '''(added in version 2.6.1)'''
*categories_to_documents - Cross-references the categories and documents tables. '''(added in version 2.6.1)'''
*ccda - Used for CCDA implementation. '''(added in version 4.2.1)'''
*ccda - Used for CCDA implementation. '''(added in version 4.2.1)'''
*ccda_components - Used for CCDA implementation. '''(added in version 4.2.1)'''
*ccda_components - Used for CCDA implementation. '''(added in version 4.2.1)'''
Line 47: Line 60:
*ccda_sections - Used for CCDA implementation. '''(added in version 4.2.1)'''
*ccda_sections - Used for CCDA implementation. '''(added in version 4.2.1)'''
*ccda_table_mapping - Used for CCDA implementation. '''(added in version 4.2.1)'''
*ccda_table_mapping - Used for CCDA implementation. '''(added in version 4.2.1)'''
*chart_tracker – This supports the “chart tracking” feature whereby you can keep track of the location and history of paper charts.  For each check-out or check-in, it records the patient ID, timestamp, user and location.  
*chart_tracker – This supports the “chart tracking” feature whereby you can keep track of the location and history of paper charts.  For each check-out or check-in, it records the patient ID, timestamp, user and location. '''(added in version 3.0.0)'''
*claims - records events related to the queueing and generation of claims.  The Billing page summarizes this information for each encounter shown.
*claims - records events related to the queueing and generation of claims.  The Billing page summarizes this information for each encounter shown. '''(added in version 2.8.3)'''
*clinical_plans - Used by CDR engine. '''(added in version 4.1.0)'''
*clinical_plans - Used by CDR engine. '''(added in version 4.1.0)'''
*clinical_plans_rules - Used by CDR engine. '''(added in version 4.1.0)'''
*clinical_plans_rules - Used by CDR engine. '''(added in version 4.1.0)'''
*clinical_rules - Used by CDR engine. '''(added in version 4.1.0)'''
*clinical_rules - Used by CDR engine. '''(added in version 4.1.0)'''
*clinical_rules_log - Used by CDR engine. '''(added in version 4.2.1)'''
*clinical_rules_log - Used by CDR engine. '''(added in version 4.2.1)'''
*codes - The table of available billing codes including their modifiers, descriptions and fees.  For the U.S. these will normally be CPT, HCPCS and ICD9 codes.
*codes - The table of available billing codes including their modifiers, descriptions and fees.  For the U.S. these will normally be CPT, HCPCS and ICD10 codes.
*codes_history - '''(added in version 5.0.0)'''
*code_types - Stores the code types used in the codes table. '''(added in version 4.0.0)'''
*code_types - Stores the code types used in the codes table. '''(added in version 4.0.0)'''
*config - Not sure what this is.
*config - Not used anymore in the codebase. '''(added in version 2.6.1)''' '''(REMOVED in version 5.0.2)'''
*config_seq - Presumably the last "id" used in the config table.
*config_seq - Presumably the last "id" used in the config table. This is not used anymore in the codebase. '''(REMOVED in version 5.0.2)'''
*customlists - Used by Nation Notes. '''(added in version 4.1.0)'''
*customlists - Used by Nation Notes. '''(added in version 4.1.0)'''
*dated_reminders - Used by the dated reminders module. '''(added in version 4.1.0(patch 8))'''
*dated_reminders - Used by the dated reminders module. '''(added in version 4.1.0(patch 8))'''
*dated_reminders_link - Used by the dated reminders module. '''(added in version 4.1.0(patch 8))'''
*dated_reminders_link - Used by the dated reminders module. '''(added in version 4.1.0(patch 8))'''
*direct_message_log -For logging and tracking of Direct messaging '''(added in version 4.1.2)'''
*direct_message_log -For logging and tracking of Direct messaging '''(added in version 4.1.2)'''
*documents - Holds metadata for patient documents.  The "foreign_id" column references "pid" in the patient_data table.
*documents - Holds metadata for patient documents.  The "foreign_id" column references "pid" in the patient_data table. '''(added in version 2.6.1)'''
*documents_legal_categories - Used by patient portal(s). '''(added in version 4.1.0)'''
*documents_legal_categories - Used by patient portal(s). '''(added in version 4.1.0)'''
*documents_legal_detail - Used by patient portal(s). '''(added in version 4.1.0)'''
*documents_legal_detail - Used by patient portal(s). '''(added in version 4.1.0)'''
*documents_legal_master - Used by patient portal(s). '''(added in version 4.1.0)'''
*documents_legal_master - List of Master Docs to be signed. Used by patient portal(s). '''(added in version 4.1.0)'''
*drugs - Supports in-house drug sales.  This is the list of available drugs.
*drugs - Supports in-house drug sales.  This is the list of available drugs. '''(added in version 2.8.2)'''
*drug_inventory - Supports in-house drug sales.  Each row represents a "lot" of purchased drugs.
*drug_inventory - Supports in-house drug sales.  Each row represents a "lot" of purchased drugs. '''(added in version 2.8.2)'''
*drug_sales - Supports in-house drug sales.  Each row represents a drug sale, i.e. an invoice line item.
*drug_sales - Supports in-house drug sales.  Each row represents a drug sale, i.e. an invoice line item. '''(added in version 2.8.2)'''
*drug_templates - Supports in-house drug sales.  Each row represents a shortcut for a common dispensation of a drug.
*drug_templates - Supports in-house drug sales.  Each row represents a shortcut for a common dispensation of a drug. '''(added in version 2.8.2)'''
*eligibility_response - For insurance eligibility checking feature. '''(added in version 4.0.0)'''
*eligibility_response - For insurance eligibility checking feature. '''(added in version 4.0.0)''' '''(REMOVED in version 5.0.2)'''
*eligibility_verification - For insurance eligibility checking feature. '''(added in version 4.0.0)'''
*eligibility_verification - For insurance eligibility checking feature. '''(added in version 4.0.0)'''
*employer_data - Contains patient employer information.  The "pid" column references "pid" in the patient_data table.  In general there is more than one of these accumulated per patient, and the current one is that with the most recent "date" value.  This is inconsistent with subscriber employer information, which is included in the insurance_data table.
*employer_data - Contains patient employer information.  The "pid" column references "pid" in the patient_data table.  In general there is more than one of these accumulated per patient, and the current one is that with the most recent "date" value.  This is inconsistent with subscriber employer information, which is included in the insurance_data table.
*enc_category_map - Used by CDR engine to categorize encounters '''(added in version 4.1.0)'''
*enc_category_map - Used by CDR engine to categorize encounters '''(added in version 4.1.0)'''
*erx_ttl_touch - Stores information for ePrescribe, NewCrop.
*erx_drug_paid - Used by WENO rx module. '''(added in version 5.0.1)''' '''(REMOVED in version 5.0.2)'''
*esign_signatures - Stores information relating to the electronic signing and locking of Encounter Forms.
*erx_narcotics - Used by WENO rx module. '''(added in version 5.0.1)'''
*extended_log - Contains logging.
*erx_rx_log - Used by WENO rx module. '''(added in version 5.0.1)'''
*erx_ttl_touch - Stores information for ePrescribe, NewCrop. '''(added in version 4.2.0)'''
*erc_weno_drugs - Used by WENO rx module. '''(added in version 5.0.2)'''
*esign_signatures - Stores information relating to the electronic signing and locking of Encounter Forms. '''(added in version 4.2.0)'''
*extended_log - Contains logging. '''(added in version 4.0.0)'''
*external_encounters - Needed for MU2. '''(added in version 4.2.1)'''
*external_encounters - Needed for MU2. '''(added in version 4.2.1)'''
*external_procedures - Needed for MU2. '''(added in version 4.2.1)'''
*external_procedures - Needed for MU2. '''(added in version 4.2.1)'''
*facility - Facilities are entered for treatment or billing purposes, or both.  It is important that exactly one facility has its "billing_location" flag set, as this is the one used as the billing facility in generated claims.
*facility - Facilities are entered for treatment or billing purposes, or both.  It is important that exactly one facility has its "billing_location" flag set, as this is the one used as the billing facility in generated claims.
*facility_user_ids - Holds data that is facility-specific for users. '''(added in version 4.1.1)'''
*facility_user_ids - Holds data that is facility-specific for users. '''(added in version 4.1.1)'''
*fee_sheet_options – Supports a method of categorizing services in the Fee Sheet.  For each category and item within the category, it identifies one or more specific services that would be added to the Fee Sheet when that item is selected.
*fee_sheet_options – Supports a method of categorizing services in the Fee Sheet.  For each category and item within the category, it identifies one or more specific services that would be added to the Fee Sheet when that item is selected. '''(added in version 2.9.0)'''
*forms - An index of all encounter form instances.
*forms - An index of all encounter form instances.
*form_* - Most "encounter forms" (other EHR systems call these "templates") implement a table whose name is "form_" followed by the name of the form.
*form_* - Most "encounter forms" (other EHR systems call these "templates") implement a table whose name is "form_" followed by the name of the form.
*form_care_plan - '''(added in version 4.2.1)'''
*form_clinical_instructions - '''(added in version 4.2.1)'''
*form_dictation
*form_encounter - This is the "encounter form" table for the most basic information about the patient encounter.  Most importantly this contains the encounter date.  Some A/R data is included: last payer level billed, last payer level closed, last statement date, and number of statements previously sent.
*form_encounter - This is the "encounter form" table for the most basic information about the patient encounter.  Most importantly this contains the encounter date.  Some A/R data is included: last payer level billed, last payer level closed, last statement date, and number of statements previously sent.
*form_misc_billing_options - An encounter form that is a catch-all for billing information that is not always needed and is not captured elsewhere.  For example, a specialist will put "prior authorization" numbers here.
*form_eye_acuity - '''(added in version 5.0.2)'''
*form_eye_antseg - '''(added in version 5.0.2)'''
*form_eye_base - '''(added in version 5.0.2)'''
*form_eye_biometrics - '''(added in version 5.0.2)'''
*form_eye_external - '''(added in version 5.0.2)'''
*form_eye_hpi - '''(added in version 5.0.2)'''
*form_eye_locking - '''(added in version 5.0.2)'''
*form_eye_mag - '''(added in version 5.0.0)''' '''(REMOVED in version 5.0.2)'''
*form_eye_mag_dispense - '''(added in version 5.0.0)'''
*form_eye_mag_impplan - '''(added in version 5.0.0)'''
*form_eye_mag_orders - '''(added in version 5.0.0)'''
*form_eye_mag_prefs - '''(added in version 5.0.0)'''
*form_eye_mag_wearing - '''(added in version 5.0.0)'''
*form_eye_neuro - '''(added in version 5.0.2)'''
*form_eye_postseg - '''(added in version 5.0.2)'''
*form_eye_refraction - '''(added in version 5.0.2)'''
*form_eye_ros - '''(added in version 5.0.2)'''
*form_eye_vitals - '''(added in version 5.0.2)'''
*form_functional_cognitive_status - '''(added in version 4.2.1)'''
*form_groups_encounter - '''(added in version 5.0.1)'''
*form_group_attendance - '''(added in version 5.0.1)'''
*form_misc_billing_options - An encounter form that is a catch-all for billing information that is not always needed and is not captured elsewhere.  For example, a specialist will put "prior authorization" numbers here. '''(added in version 2.8.3)'''
*form_observation - '''(added in version 4.2.1)'''
*form_reviewofs
*form_ros
*form_soap
*form_taskman - '''(added in version 5.0.0)'''
*form_vitals - '''(added in version 5.0.0)'''
*gacl_* - Contains all the access control tables (embedded php-GACL)
*gacl_* - Contains all the access control tables (embedded php-GACL)
*geo_country_reference - Maps country names to codes. I don't think this is used.
*gacl_acl - embedded php-GACL '''(added in version 3.0.0)'''
*geo_zone_reference - Maps U.S. states, Canadian provinces and some other geographic regions to codes. I don't think this is used either.
*gacl_acl_sections - embedded php-GACL '''(added in version 3.0.0)'''
*globals - Stores settings. '''(added in version 4.0.0)'''
*gacl_acl_seq - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aco - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aco_map - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aco_sections - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aco_sections_seq - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aco_seq - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aro - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aro_groups - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aro_groups_id_seq - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aro_groups_map - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aro_map - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aro_sections - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aro_sections_seq - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_aro_seq - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_axo - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_axo_groups - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_axo_groups_map - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_axo_map - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_axo_sections - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_groups_aro_map - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_groups_axo_map - embedded php-GACL '''(added in version 3.0.0)'''
*gacl_phpgacl - embedded php-GACL '''(added in version 3.0.0)'''
*geo_country_reference - Maps country names to codes. Not used in codebase. '''(added in version 2.6.1)''' '''(REMOVED in version 5.0.2)'''
*geo_zone_reference - Maps U.S. states, Canadian provinces and some other geographic regions to codes. Not used in codebase. '''(added in version 2.6.1)''' '''(REMOVED in version 5.0.2)'''
*globals - Stores global configuration settings. '''(added in version 4.0.0)'''
*gprelations - Used to link pnotes with other items such as documents. '''(added in version 3.2.0)'''
*gprelations - Used to link pnotes with other items such as documents. '''(added in version 3.2.0)'''
*groups - Used to assign one or more "group names" to each user.  Supports grouping of users.
*groups - Used to assign one or more "group names" to each user.  Supports grouping of users.
Line 104: Line 176:
*icd10_reimbr_dx_9_10 - ICD9/10 diagnosis code reimbursement equivalence mapping. '''(added in version 4.1.1)'''
*icd10_reimbr_dx_9_10 - ICD9/10 diagnosis code reimbursement equivalence mapping. '''(added in version 4.1.1)'''
*icd10_reimbr_pcs_9_10 - ICD9/10 procedure/service code reimbursement equivalence mapping. '''(added in version 4.1.1)'''
*icd10_reimbr_pcs_9_10 - ICD9/10 procedure/service code reimbursement equivalence mapping. '''(added in version 4.1.1)'''
*immunizations - Records immunizations given to patients.
*immunizations - Records immunizations given to patients. '''(added in version 2.7.2)'''
*immunization_observation - Used to store data needed for MU2 certification. '''(added in version 5.0.0)'''
*insurance_companies - These are the payers.  Most importantly it contains the payer's name and the external payer ID (cms_id).  However may clearinghouses will map your payer names to the IDs, so you might not need to put in the IDs.
*insurance_companies - These are the payers.  Most importantly it contains the payer's name and the external payer ID (cms_id).  However may clearinghouses will map your payer names to the IDs, so you might not need to put in the IDs.
*insurance_data - Contains rows of insurance information for each patient, including at least one each for primary, secondary and tertiary insurance.  Holds information about each patient insurance plan, including subscriber information.  An important recent improvement to this is the maintenance of the "date" column which is the effective date of the plan; thus it is possible to store multiple plans of each type, with different  effective dates.  "provider" here is a reference to insurance_companies.id.
*insurance_data - Contains rows of insurance information for each patient, including at least one each for primary, secondary and tertiary insurance.  Holds information about each patient insurance plan, including subscriber information.  An important recent improvement to this is the maintenance of the "date" column which is the effective date of the plan; thus it is possible to store multiple plans of each type, with different  effective dates.  "provider" here is a reference to insurance_companies.id.
*insurance_numbers - Cross-references providers and insurance companies, and contains information specific to that relation, such as the credentialing number assigned by the insurance company to that provider.  Where insurance_company_id is NULL, this indicates "default" values for the provider.
*insurance_numbers - Cross-references providers and insurance companies, and contains information specific to that relation, such as the credentialing number assigned by the insurance company to that provider.  Where insurance_company_id is NULL, this indicates "default" values for the provider.
*integration_mapping - With the need for SQL-Ledger eliminated, this table is obsolete.  It associates certain items in the OpenEMR database (MySQL) with corresponding items in the SQL-Ledger database (PostgreSQL). local_table and local_id are values for the OpenEMR table name and primary key. foreign_table and foreign_id are those for the SQL-Ledger database. The logical entities of interest are users (providers), and patients.
*integration_mapping - It associates certain items in the OpenEMR database (MySQL) with corresponding items in the SQL-Ledger database (PostgreSQL). local_table and local_id are values for the OpenEMR table name and primary key. foreign_table and foreign_id are those for the SQL-Ledger database. The logical entities of interest are users (providers), and patients. SQL-Ledger support was removed and this table then became obsolete.  '''(REMOVED in version 5.0.0)'''
*issue_encounter - Cross-references the lists table (representing "issues") with encounters (identified by patient ID and encounter ID).  The idea here is to identify those encounters that address a given problem of a given patient.
*issue_encounter - Cross-references the lists table (representing "issues") with encounters (identified by patient ID and encounter ID).  The idea here is to identify those encounters that address a given problem of a given patient. '''(added in version 2.7.2)'''
*issue_types - Lists the issue types.
*issue_types - Lists the issue types. '''(added in version 4.1.2)'''
*lang_constants - Supports language translation.  Assigns an ID to each translatable string.
*keys - Holds private key sets for encryption/decryption. '''(added in version 5.0.2)'''
*lang_constants - Supports language translation.  Assigns an ID to each translatable string. '''(added in version 2.8.1)'''
*lang_custom - Records local translation modifications. '''(added in version 4.0.0)'''
*lang_custom - Records local translation modifications. '''(added in version 4.0.0)'''
*lang_definitions - Supports language translation.  Provides the translation string for each language and string ID.
*lang_definitions - Supports language translation.  Provides the translation string for each language and string ID. '''(added in version 2.8.1)'''
*lang_languages - Supports language translation.  Identifies the supported languages.
*lang_languages - Supports language translation.  Identifies the supported languages. '''(added in version 2.8.1)'''
*layout_group_properties - '''(added in version 5.0.1)'''
*layout_options – Defines the visual layout for patient demographics, history, referrals and some types of issues.  This table is maintained and customized via the Layouts administrative interface.
*layout_options – Defines the visual layout for patient demographics, history, referrals and some types of issues.  This table is maintained and customized via the Layouts administrative interface.
*lbf_data -Supports and records Layout based forms data '''(added in version 3.2.0)'''
*lbf_data -Supports and records Layout based forms data '''(added in version 3.2.0)'''
Line 120: Line 195:
*lists - These are patient "issues": medical problems, allergies, medications, surgeries, etc.  They are similar to patient history items, but represent those items currently under treatment and thus that one might logically want to associate with encounters.
*lists - These are patient "issues": medical problems, allergies, medications, surgeries, etc.  They are similar to patient history items, but represent those items currently under treatment and thus that one might logically want to associate with encounters.
*lists_touch - Tracks whether items in 'lists' have been used. This allows the option to choose 'none' for items in 'lists'. '''(added in version 4.1.0)'''
*lists_touch - Tracks whether items in 'lists' have been used. This allows the option to choose 'none' for items in 'lists'. '''(added in version 4.1.0)'''
*list_options – Defines most static lists.  This table is maintained and customized via the Lists administrative interface.
*list_options – Defines most static lists.  This table is maintained and customized via the Lists administrative interface. '''(added in version 2.9.0)'''
*log - A history of information access events.  This table can become very large and as of yet there is no easy too for cleaning it out or archiving it.  The logging feature also needs work in that some things are not logged that should be.
*log - A history of information access events.  This table can become very large and as of yet there is no easy too for cleaning it out or archiving it.  The logging feature also needs work in that some things are not logged that should be.
*login_mfa_registrations - '''(added in version 5.0.2)'''
*log_comment_encrypt - '''(added in version 4.2.0)'''
*log_comment_encrypt - '''(added in version 4.2.0)'''
*log_validator - '''(added in version 5.0.0)'''
*medex_icons - For MedEX module. '''(added in version 5.0.1)'''
*medex_outgoing - For MedEX module. '''(added in version 5.0.1)'''
*medex_prefs - For MedEX module. '''(added in version 5.0.1)'''
*medex_recalls - For MedEX module. '''(added in version 5.0.1)'''
*misc_address_book - Place that addresses imported from the CCR/CCD/CCDA importing tool are stored. '''(added in version 4.1.2)'''
*misc_address_book - Place that addresses imported from the CCR/CCD/CCDA importing tool are stored. '''(added in version 4.1.2)'''
*modules - '''(added in version 4.2.0)'''
*modules - '''(added in version 4.2.0)'''
Line 131: Line 212:
*module_acl_user_settings - '''(added in version 4.2.0)'''
*module_acl_user_settings - '''(added in version 4.2.0)'''
*module_configuration - '''(added in version 4.2.0)'''
*module_configuration - '''(added in version 4.2.0)'''
*notes - I'm not sure if or how this table is used.
*multiple_db - For multiple db zend module. '''(added in version 5.0.1)'''
*notification_log - For the patient automatic notification module (works with tables automatic_notification and notification_settings)
*notes - Stores document notes. '''(added in version 2.6.1)'''
*notification_settings - For the patient automatic notification module (works with tables automatic_notification and notification_log)
*notification_log - For the patient automatic notification module (works with tables automatic_notification and notification_settings). '''(added in version 4.1.0)'''
*notification_settings - For the patient automatic notification module (works with tables automatic_notification and notification_log). '''(added in version 4.1.0)'''
*onotes - These are the "office notes" recorded and viewed in the Notes panel.
*onotes - These are the "office notes" recorded and viewed in the Notes panel.
*onsite_documents - Patient portal. '''(added in version 5.0.1)'''
*onsite_mail - Patient portal. '''(added in version 5.0.1)'''
*onsite_messages - Patient portal. '''(added in version 5.0.1)'''
*onsite_online - Patient portal. '''(added in version 5.0.1)'''
*onsite_portal_activity - Patient portal. '''(added in version 5.0.1)'''
*onsite_signatures - Patient portal. '''(added in version 5.0.1)'''
*openemr_modules - used internally by the embedded PostNuke code.
*openemr_modules - used internally by the embedded PostNuke code.
*openemr_modules_vars - used internally by the embedded PostNuke code.
*openemr_modules_vars - used internally by the embedded PostNuke code.
*openemr_postcalendar_categories - These are the definitions of the calendar event categories.  You maintain these by going into Administration / Calendar / Categories in OpenEMR.
*openemr_postcalendar_categories - These are the definitions of the calendar event categories.  You maintain these by going into Administration / Calendar / Categories in OpenEMR.
*openemr_postcalendar_events - These are individual calendar events: appointments, In Office, Out of Office, lunch, reserved times, etc.  Many of these will be repeating events. Repeating events are supported from '''(version 4.2.1)'''
*openemr_postcalendar_events - These are individual calendar events: appointments, In Office, Out of Office, lunch, reserved times, etc.  Many of these will be repeating events. Repeating events are supported from '''(version 4.2.1)'''
*openemr_postcalendar_limits - used internally by the embedded PostNuke code.
*openemr_postcalendar_limits - used internally by the embedded PostNuke code. '''(REMOVED in version 5.0.2)'''
*openemr_postcalendar_topics - used internally by the embedded PostNuke code.
*openemr_postcalendar_topics - used internally by the embedded PostNuke code. '''(REMOVED in version 5.0.2)'''
*openemr_session_info - used internally by the embedded PostNuke code.
*openemr_session_info - used internally by the embedded PostNuke code. '''(REMOVED in version 5.0.2)'''
*patient_access_offsite - Stores patient credentials for embedded patient portal. '''(added in version 4.1.0)'''
*patient_access_offsite - Stores patient credentials for embedded patient portal. '''(added in version 4.1.0)'''
*patient_access_onsite - Stores patient credentials for third party offsite patient portals. '''(added in version 4.1.0)'''
*patient_access_onsite - Stores patient credentials for third party offsite patient portals. '''(added in version 4.1.0)'''
*patient_data - The primary repository for patient demographics.
*patient_birthday_alert - '''(added in version 5.0.1)'''
*patient_data - The primary repository for patient demographics data.
*patient_portal_menu - Used by offsite portal. '''(added in version 4.2.1)'''
*patient_portal_menu - Used by offsite portal. '''(added in version 4.2.1)'''
*patient_reminders - Used by CDR engine. '''(added in version 4.1.0)'''
*patient_reminders - Used by CDR engine. '''(added in version 4.1.0)'''
*patient_tracker - Used by patient tracker board module. '''(added in version 4.2.1)'''
*patient_tracker - Used by patient tracker board module. '''(added in version 4.2.1)'''
*patient_tracker_element - Used by patient tracker board module. '''(added in version 4.2.1)'''
*patient_tracker_element - Used by patient tracker board module. '''(added in version 4.2.1)'''
*payments - Records information entered via the "Payment" popup window (accessable from the dropdown in the patient menu).
*payments - Records information entered via the "Payment" popup window (accessable from the dropdown in the patient menu). '''(added in version 2.8.1)'''
*payment_gateway_details - Used by offsite portal  '''(added in version 4.1.1)'''
*payment_gateway_details - Used by offsite portal  '''(added in version 4.1.1)'''
*pharmacies - The list of pharmacies.  This also references the "addresses" and "phone_numbers" tables via its "id" column.
*pharmacies - The list of pharmacies.  This also references the "addresses" and "phone_numbers" tables via its "id" column.
*phone_numbers - Contains telephone numbers of insurance_companies and pharmacies, and like the addresses table is effectively an extension of those tables.
*phone_numbers - Contains telephone numbers of insurance_companies and pharmacies, and like the addresses table is effectively an extension of those tables.
*pma_bookmark - Contains information about the canned reports available via the dropdown in the Reports menu.  We recommend not using this feature because it's crude and confusing. *(And now missing entirely from version 3.1+)
*pma_bookmark - Contains information about the canned reports available via the dropdown in the Reports menu.  We recommend not using this feature because it's crude and confusing. *(And now missing entirely from version 3.1+). '''(REMOVED in version 5.0.2)'''
*pma_column_info - Doesn't seem to be used.
*pma_* tables created by embedded phpMyAdmin '''(REMOVED in version 5.0.2)'''
*pma_history - Doesn't seem to be used.
*pma_pdf_pages - Doesn't seem to be used.
*pma_relation - Doesn't seem to be used.
*pma_table_coords - Doesn't seem to be used.
*pma_table_info - Doesn't seem to be used.
*pnotes - Patient notes.  This is a very useful feature where notes associated with a specific patient may be passed around among different users within the clinic, and eventually marked as closed.  The assigned_to column indicates who is the current owner of the issue.
*pnotes - Patient notes.  This is a very useful feature where notes associated with a specific patient may be passed around among different users within the clinic, and eventually marked as closed.  The assigned_to column indicates who is the current owner of the issue.
*prescriptions - Prescriptions.  In the case of in-house dispensation, drug_id will indicate the drug dispensed.
*prescriptions - Prescriptions.  In the case of in-house dispensation, drug_id will indicate the drug dispensed.
*prices – Contains price information for products and services, and supports multiple price levels.  This table obsoletes the “fee” column of the “codes” table.  Note that the price levels are defined in the “list_options” table (list_id = “pricelevel”).
*prices – Contains price information for products and services, and supports multiple price levels.  This table obsoletes the “fee” column of the “codes” table.  Note that the price levels are defined in the “list_options” table (list_id = “pricelevel”). '''(added in version 2.9.0)'''
*procedure_answers - For procedure/lab/module. '''(added in version 4.1.2)'''
*procedure_answers - For procedure/lab/module. '''(added in version 4.1.2)'''
*procedure_order_code - For procedure/lab/module. '''(added in version 4.1.2)'''
*procedure_order_code - For procedure/lab/module. '''(added in version 4.1.2)'''
Line 171: Line 255:
*procedure_result - For procedure/lab module. '''(added in version 4.0.0)'''
*procedure_result - For procedure/lab module. '''(added in version 4.0.0)'''
*procedure_type - For procedure/lab module. '''(added in version 4.0.0)'''
*procedure_type - For procedure/lab module. '''(added in version 4.0.0)'''
*product_registration - '''(added in version 5.0.0)'''
*product_warehouse - For procedure/lab/module. '''(added in version 4.1.1)'''
*product_warehouse - For procedure/lab/module. '''(added in version 4.1.1)'''
*registry - Contains metadata regarding "registered" encountered forms.  The Administration / Forms panel is used to register forms and maintain this information.
*registry - Contains metadata regarding "registered" encountered forms.  The Administration / Forms panel is used to register forms and maintain this information.
Line 190: Line 275:
*RXNSAT - Not included on a default installation. Is installed when import the RXNORM codeset. Attributes. '''(added in version 4.1.0)'''
*RXNSAT - Not included on a default installation. Is installed when import the RXNORM codeset. Attributes. '''(added in version 4.1.0)'''
*RXNSTY - Not included on a default installation. Is installed when import the RXNORM codeset. Semantic types. '''(added in version 4.1.0)'''
*RXNSTY - Not included on a default installation. Is installed when import the RXNORM codeset. Semantic types. '''(added in version 4.1.0)'''
*sct_concepts - Not included on a default installation. Is installed when import the SNOMED codeset. '''(added in version 4.1.0)'''
*sct_concepts - Not included on a default installation. Is installed when import the RF1 SNOMED codeset. '''(added in version 4.1.0)'''
*sct_descriptions - Not included on a default installation. Is installed when import the SNOMED codeset. '''(added in version 4.1.0)'''
*sct_descriptions - Not included on a default installation. Is installed when import the RF1 SNOMED codeset. '''(added in version 4.1.0)'''
*sct_relationships - Not included on a default installation. Is installed when import the SNOMED codeset. '''(added in version 4.1.0)'''
*sct_relationships - Not included on a default installation. Is installed when import the RF1 SNOMED codeset. '''(added in version 4.1.0)'''
*sct2_concept - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. '''(added in version 5.0.2)'''
*sct2_description - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. '''(added in version 5.0.2)'''
*sct2_identifier - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. '''(added in version 5.0.2)'''
*sct2_relationship - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. '''(added in version 5.0.2)'''
*sct2_statedrelationship - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. '''(added in version 5.0.2)'''
*sct2_textdefinition - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. '''(added in version 5.0.2)'''
*sequences - Holds the last "id" used by the integration_mapping table and for encounter IDs.
*sequences - Holds the last "id" used by the integration_mapping table and for encounter IDs.
*shared_attributes - '''(added in version 4.2.0)'''
*shared_attributes - '''(added in version 4.2.0)'''
Line 199: Line 290:
*syndromic_surveillance - Used by Syndromic Surveillance module. '''(added in version 4.0.0)'''
*syndromic_surveillance - Used by Syndromic Surveillance module. '''(added in version 4.0.0)'''
*template_users - Used by Nation Notes. '''(added in version 4.1.0)'''
*template_users - Used by Nation Notes. '''(added in version 4.1.0)'''
*therapy_groups - For group therapy module. '''(added in version 5.0.1)'''
*therapy_groups_counselors - For group therapy module. '''(added in version 5.0.1)'''
*therapy_groups_participants - For group therapy module. '''(added in version 5.0.1)'''
*therapy_groups_participant_attendance - For group therapy module. '''(added in version 5.0.1)'''
*transactions - Records entries made in the Transactions panel.
*transactions - Records entries made in the Transactions panel.
*users - This is a dual-purpose table.  It supports the list of local users with their login names, passwords and other information; and it supports the Address Book.  Non-local users are identifiable by having an empty "username" value.
*users - This is a dual-purpose table.  It supports the list of local users with their login names, passwords and other information; and it supports the Address Book.  Non-local users are identifiable by having an empty "username" value.
*users_facility - Used to support restrictions for multiple facilities setups.
*users_facility - Used to support restrictions for multiple facilities setups. joins users or patient_data to facility table. '''(added in version 3.1.0)'''
*users_secure - Holds user authentication credentials. '''(added in version 4.1.2)'''
*users_secure - Holds user authentication credentials. '''(added in version 4.1.2)'''
*users_settings - Stores user settings. '''(added in version 4.1.0)'''
*users_settings - Stores user settings. '''(added in version 4.1.0)'''
*valueset - Used to store standardized Value Set, which is used in MU2 CQM calculations'''(added in version 5.0.0)'''
*version - Stores the OpenEMR version and OpenEMR database version. '''(added in version 4.0.0)'''
*version - Stores the OpenEMR version and OpenEMR database version. '''(added in version 4.0.0)'''
*x12_partners - These are the entities to whom electronic claims are sent.  Normally there is just one, a clearinghouse.  Referenced by the insurance_companies and billing tables.
*voids - Used to void payments. '''(added in version 5.0.0)'''
*x12_partners - These are the entities to whom electronic claims are sent.  Normally there is just one, a clearinghouse.  Referenced by the insurance_companies and billing tables. '''(added in version 2.6.1)'''

Latest revision as of 20:58, 8 June 2019

Database

Overview

File:OpenEmrOriginalSchema.pdf - Full Graphical Map of the current schema as of version 3.2.0 (note there have been many more table added since this, which are listed below).

File:OpenEMR 5 0 2SchemaBreakdown.pdf - Breaks the current schema down into various categories to quickly see the tables by functionality for the upcoming version 5.0.2. Note many tables are missing as this is a work in progress.

Engine

For OpenEMR 5.0.0, engine was converted to InnoDB, except for following exception:
  • form_eye_mag (in InnoDB, was throwing following error on some newer OS's 'Row size too large (> 8126).')
Above table was set to be skipped in the upgrade script auto InnoDB conversion here: https://github.com/openemr/openemr/blob/master/library/sql_upgrade_fx.php#L640
For OpenEMR 5.0.2, the form_eye_mag table was removed; thus database became 100% InnoDB.

Tables

OpenEMR installs many database tables. Following is a list, with brief descriptions:


  • addresses - Contains street addresses for two other tables: insurance_companies and pharmacies. So it is effectively an extension of those tables. This is inconsistent in that some other tables (e.g. facility, patient_data, users) have their addresses embedded. The "foreign_id" column equals the "id" of the foreign table, but curiously the addresses table does not identify the foreign table itself.
  • amc_misc_data - For tracking of some Automated Measure Calculations (AMC). (added in version 4.1.0)
  • amendments - (added in version 4.2.0)
  • amendments_history - (added in version 4.2.0)
  • api_token - For the api. (added in version 5.0.2)
  • array - Not used in codebase. (added in version 2.8.2) (REMOVED in version 5.0.2)
  • ar_activity – This is the primary repository for Accounts Receivable information. In general it contains multiple rows per encounter (visit). Each row corresponds to either a payment or an adjustment. (added in version 2.8.2) and columns are:
    • pid, encounter: Identifies the visit.
    • sequence_no: A “line number” of the activity item for this visit.
    • code, modifier: Identifies a particular service item, if any, that this payment or adjustment is for; generally useful only for insurance.
    • payer_type: 0 = patient, 1 = primary insurance, 2 = secondary insurance, etc.
    • post_time: A timestamp for the event posting.
    • post_user: The ID of the user who posted the item.
    • session_id: The ID of the associated ar_session table row, if any; generally useful only for insurance.
    • memo: Used for the insurance adjustment reason, if applicable.
    • pay_amount: The amount paid, if this is a payment.
    • adj_amount: The amount of adjustment, if this is an adjustment.
  • ar_session – Also for Accounts Receivable, this table is applicable to insurance EOBs. Each row corresponds to a “posting session” in which a single payment covers a set of claims. (added in version 2.8.2) and columns are:
    • session_id: A unique numeric identifier for the session.
    • payer_id: The ID of the payer in the insurance_companies table.
    • user_id: The ID of the user who is posting the session.
    • closed: Indicates if the session is complete (1=yes, 0=no).
    • reference: Normally a check number or EOB number.
    • check_date: The check date as provided by the payer.
    • deposit_date: The date that the receiver deposits the payment.
    • pay_total: The amount of the payment.
  • audit_details: Used by patient portal(s). (added in version 4.1.0)
  • audit_master: Used by patient portal(s). (added in version 4.1.0)
  • automatic_notification - For the patient automatic notification module (works with tables notification_settings and notification_log). (added in version 4.1.0)
  • background_services - For tracking background services (added in version 4.1.2)
  • batchcom - Records messages sent to groups of patients using the "Batch Communication Tool". (added in version 2.7.3)
  • billing - Billing items. Contains a row for every CPT4 code, HCPCS code, and ICD10 code recorded into an encounter. In addition has a row for each co-pay entered. The billing process also maintains state information in this table.
  • calendar_external - Used for calendar holiday import. (added in version 5.0.0)
  • categories - Defines the tree of document category types. These are the category names that you see when viewing patient documents. Note that the "parent" column is a reference to the "id" column of the same table, thus the tree structure. (added in version 2.6.1)
  • categories_seq - Holds the value of the last-used "id" for the categories table. (added in version 2.6.1)
  • categories_to_documents - Cross-references the categories and documents tables. (added in version 2.6.1)
  • ccda - Used for CCDA implementation. (added in version 4.2.1)
  • ccda_components - Used for CCDA implementation. (added in version 4.2.1)
  • ccda_field_mapping - Used for CCDA implementation. (added in version 4.2.1)
  • ccda_sections - Used for CCDA implementation. (added in version 4.2.1)
  • ccda_table_mapping - Used for CCDA implementation. (added in version 4.2.1)
  • chart_tracker – This supports the “chart tracking” feature whereby you can keep track of the location and history of paper charts. For each check-out or check-in, it records the patient ID, timestamp, user and location. (added in version 3.0.0)
  • claims - records events related to the queueing and generation of claims. The Billing page summarizes this information for each encounter shown. (added in version 2.8.3)
  • clinical_plans - Used by CDR engine. (added in version 4.1.0)
  • clinical_plans_rules - Used by CDR engine. (added in version 4.1.0)
  • clinical_rules - Used by CDR engine. (added in version 4.1.0)
  • clinical_rules_log - Used by CDR engine. (added in version 4.2.1)
  • codes - The table of available billing codes including their modifiers, descriptions and fees. For the U.S. these will normally be CPT, HCPCS and ICD10 codes.
  • codes_history - (added in version 5.0.0)
  • code_types - Stores the code types used in the codes table. (added in version 4.0.0)
  • config - Not used anymore in the codebase. (added in version 2.6.1) (REMOVED in version 5.0.2)
  • config_seq - Presumably the last "id" used in the config table. This is not used anymore in the codebase. (REMOVED in version 5.0.2)
  • customlists - Used by Nation Notes. (added in version 4.1.0)
  • dated_reminders - Used by the dated reminders module. (added in version 4.1.0(patch 8))
  • dated_reminders_link - Used by the dated reminders module. (added in version 4.1.0(patch 8))
  • direct_message_log -For logging and tracking of Direct messaging (added in version 4.1.2)
  • documents - Holds metadata for patient documents. The "foreign_id" column references "pid" in the patient_data table. (added in version 2.6.1)
  • documents_legal_categories - Used by patient portal(s). (added in version 4.1.0)
  • documents_legal_detail - Used by patient portal(s). (added in version 4.1.0)
  • documents_legal_master - List of Master Docs to be signed. Used by patient portal(s). (added in version 4.1.0)
  • drugs - Supports in-house drug sales. This is the list of available drugs. (added in version 2.8.2)
  • drug_inventory - Supports in-house drug sales. Each row represents a "lot" of purchased drugs. (added in version 2.8.2)
  • drug_sales - Supports in-house drug sales. Each row represents a drug sale, i.e. an invoice line item. (added in version 2.8.2)
  • drug_templates - Supports in-house drug sales. Each row represents a shortcut for a common dispensation of a drug. (added in version 2.8.2)
  • eligibility_response - For insurance eligibility checking feature. (added in version 4.0.0) (REMOVED in version 5.0.2)
  • eligibility_verification - For insurance eligibility checking feature. (added in version 4.0.0)
  • employer_data - Contains patient employer information. The "pid" column references "pid" in the patient_data table. In general there is more than one of these accumulated per patient, and the current one is that with the most recent "date" value. This is inconsistent with subscriber employer information, which is included in the insurance_data table.
  • enc_category_map - Used by CDR engine to categorize encounters (added in version 4.1.0)
  • erx_drug_paid - Used by WENO rx module. (added in version 5.0.1) (REMOVED in version 5.0.2)
  • erx_narcotics - Used by WENO rx module. (added in version 5.0.1)
  • erx_rx_log - Used by WENO rx module. (added in version 5.0.1)
  • erx_ttl_touch - Stores information for ePrescribe, NewCrop. (added in version 4.2.0)
  • erc_weno_drugs - Used by WENO rx module. (added in version 5.0.2)
  • esign_signatures - Stores information relating to the electronic signing and locking of Encounter Forms. (added in version 4.2.0)
  • extended_log - Contains logging. (added in version 4.0.0)
  • external_encounters - Needed for MU2. (added in version 4.2.1)
  • external_procedures - Needed for MU2. (added in version 4.2.1)
  • facility - Facilities are entered for treatment or billing purposes, or both. It is important that exactly one facility has its "billing_location" flag set, as this is the one used as the billing facility in generated claims.
  • facility_user_ids - Holds data that is facility-specific for users. (added in version 4.1.1)
  • fee_sheet_options – Supports a method of categorizing services in the Fee Sheet. For each category and item within the category, it identifies one or more specific services that would be added to the Fee Sheet when that item is selected. (added in version 2.9.0)
  • forms - An index of all encounter form instances.
  • form_* - Most "encounter forms" (other EHR systems call these "templates") implement a table whose name is "form_" followed by the name of the form.
  • form_care_plan - (added in version 4.2.1)
  • form_clinical_instructions - (added in version 4.2.1)
  • form_dictation
  • form_encounter - This is the "encounter form" table for the most basic information about the patient encounter. Most importantly this contains the encounter date. Some A/R data is included: last payer level billed, last payer level closed, last statement date, and number of statements previously sent.
  • form_eye_acuity - (added in version 5.0.2)
  • form_eye_antseg - (added in version 5.0.2)
  • form_eye_base - (added in version 5.0.2)
  • form_eye_biometrics - (added in version 5.0.2)
  • form_eye_external - (added in version 5.0.2)
  • form_eye_hpi - (added in version 5.0.2)
  • form_eye_locking - (added in version 5.0.2)
  • form_eye_mag - (added in version 5.0.0) (REMOVED in version 5.0.2)
  • form_eye_mag_dispense - (added in version 5.0.0)
  • form_eye_mag_impplan - (added in version 5.0.0)
  • form_eye_mag_orders - (added in version 5.0.0)
  • form_eye_mag_prefs - (added in version 5.0.0)
  • form_eye_mag_wearing - (added in version 5.0.0)
  • form_eye_neuro - (added in version 5.0.2)
  • form_eye_postseg - (added in version 5.0.2)
  • form_eye_refraction - (added in version 5.0.2)
  • form_eye_ros - (added in version 5.0.2)
  • form_eye_vitals - (added in version 5.0.2)
  • form_functional_cognitive_status - (added in version 4.2.1)
  • form_groups_encounter - (added in version 5.0.1)
  • form_group_attendance - (added in version 5.0.1)
  • form_misc_billing_options - An encounter form that is a catch-all for billing information that is not always needed and is not captured elsewhere. For example, a specialist will put "prior authorization" numbers here. (added in version 2.8.3)
  • form_observation - (added in version 4.2.1)
  • form_reviewofs
  • form_ros
  • form_soap
  • form_taskman - (added in version 5.0.0)
  • form_vitals - (added in version 5.0.0)
  • gacl_* - Contains all the access control tables (embedded php-GACL)
  • gacl_acl - embedded php-GACL (added in version 3.0.0)
  • gacl_acl_sections - embedded php-GACL (added in version 3.0.0)
  • gacl_acl_seq - embedded php-GACL (added in version 3.0.0)
  • gacl_aco - embedded php-GACL (added in version 3.0.0)
  • gacl_aco_map - embedded php-GACL (added in version 3.0.0)
  • gacl_aco_sections - embedded php-GACL (added in version 3.0.0)
  • gacl_aco_sections_seq - embedded php-GACL (added in version 3.0.0)
  • gacl_aco_seq - embedded php-GACL (added in version 3.0.0)
  • gacl_aro - embedded php-GACL (added in version 3.0.0)
  • gacl_aro_groups - embedded php-GACL (added in version 3.0.0)
  • gacl_aro_groups_id_seq - embedded php-GACL (added in version 3.0.0)
  • gacl_aro_groups_map - embedded php-GACL (added in version 3.0.0)
  • gacl_aro_map - embedded php-GACL (added in version 3.0.0)
  • gacl_aro_sections - embedded php-GACL (added in version 3.0.0)
  • gacl_aro_sections_seq - embedded php-GACL (added in version 3.0.0)
  • gacl_aro_seq - embedded php-GACL (added in version 3.0.0)
  • gacl_axo - embedded php-GACL (added in version 3.0.0)
  • gacl_axo_groups - embedded php-GACL (added in version 3.0.0)
  • gacl_axo_groups_map - embedded php-GACL (added in version 3.0.0)
  • gacl_axo_map - embedded php-GACL (added in version 3.0.0)
  • gacl_axo_sections - embedded php-GACL (added in version 3.0.0)
  • gacl_groups_aro_map - embedded php-GACL (added in version 3.0.0)
  • gacl_groups_axo_map - embedded php-GACL (added in version 3.0.0)
  • gacl_phpgacl - embedded php-GACL (added in version 3.0.0)
  • geo_country_reference - Maps country names to codes. Not used in codebase. (added in version 2.6.1) (REMOVED in version 5.0.2)
  • geo_zone_reference - Maps U.S. states, Canadian provinces and some other geographic regions to codes. Not used in codebase. (added in version 2.6.1) (REMOVED in version 5.0.2)
  • globals - Stores global configuration settings. (added in version 4.0.0)
  • gprelations - Used to link pnotes with other items such as documents. (added in version 3.2.0)
  • groups - Used to assign one or more "group names" to each user. Supports grouping of users.
  • history_data - This maps 1:1 with the patient_data table and stores information about the patient's medical history.
  • icd9_dx_code - Reference for the ICD9 diagnosis codes. (added in version 4.1.1)
  • icd9_dx_long_code - Used when importing the ICD9 diagnosis codes reference, which are stored and used from icd9_dx_code. (added in version 4.1.1)
  • icd9_sg_code - Reference for the ICD9 procedure/service codes. (added in version 4.1.1)
  • icd9_sg_long_code - Used when importing the ICD9 procedure/services codes reference, which are stored and used from icd9_sg_code.(added in version 4.1.1)
  • icd10_dx_order_code - Reference for the ICD10 diagnosis codes. (added in version 4.1.1)
  • icd10_gem_dx_9_10 - ICD9/10 diagnosis code equivalence mapping. (added in version 4.1.1)
  • icd10_gem_dx_10_9 - ICD9/10 diagnosis code equivalence mapping. (added in version 4.1.1)
  • icd10_gem_pcs_9_10 - ICD9/10 procedure/service code equivalence mapping. (added in version 4.1.1)
  • icd10_gem_pcs_10_9 - ICD9/10 procedure/service code equivalence mapping. (added in version 4.1.1)
  • icd10_pcs_order_code - Reference for the ICD10 procedure/service codes. (added in version 4.1.1)
  • icd10_reimbr_dx_9_10 - ICD9/10 diagnosis code reimbursement equivalence mapping. (added in version 4.1.1)
  • icd10_reimbr_pcs_9_10 - ICD9/10 procedure/service code reimbursement equivalence mapping. (added in version 4.1.1)
  • immunizations - Records immunizations given to patients. (added in version 2.7.2)
  • immunization_observation - Used to store data needed for MU2 certification. (added in version 5.0.0)
  • insurance_companies - These are the payers. Most importantly it contains the payer's name and the external payer ID (cms_id). However may clearinghouses will map your payer names to the IDs, so you might not need to put in the IDs.
  • insurance_data - Contains rows of insurance information for each patient, including at least one each for primary, secondary and tertiary insurance. Holds information about each patient insurance plan, including subscriber information. An important recent improvement to this is the maintenance of the "date" column which is the effective date of the plan; thus it is possible to store multiple plans of each type, with different effective dates. "provider" here is a reference to insurance_companies.id.
  • insurance_numbers - Cross-references providers and insurance companies, and contains information specific to that relation, such as the credentialing number assigned by the insurance company to that provider. Where insurance_company_id is NULL, this indicates "default" values for the provider.
  • integration_mapping - It associates certain items in the OpenEMR database (MySQL) with corresponding items in the SQL-Ledger database (PostgreSQL). local_table and local_id are values for the OpenEMR table name and primary key. foreign_table and foreign_id are those for the SQL-Ledger database. The logical entities of interest are users (providers), and patients. SQL-Ledger support was removed and this table then became obsolete. (REMOVED in version 5.0.0)
  • issue_encounter - Cross-references the lists table (representing "issues") with encounters (identified by patient ID and encounter ID). The idea here is to identify those encounters that address a given problem of a given patient. (added in version 2.7.2)
  • issue_types - Lists the issue types. (added in version 4.1.2)
  • keys - Holds private key sets for encryption/decryption. (added in version 5.0.2)
  • lang_constants - Supports language translation. Assigns an ID to each translatable string. (added in version 2.8.1)
  • lang_custom - Records local translation modifications. (added in version 4.0.0)
  • lang_definitions - Supports language translation. Provides the translation string for each language and string ID. (added in version 2.8.1)
  • lang_languages - Supports language translation. Identifies the supported languages. (added in version 2.8.1)
  • layout_group_properties - (added in version 5.0.1)
  • layout_options – Defines the visual layout for patient demographics, history, referrals and some types of issues. This table is maintained and customized via the Layouts administrative interface.
  • lbf_data -Supports and records Layout based forms data (added in version 3.2.0)
  • lbt_data - Supports and records Layout based transactions data (added in version 4.2.1)
  • lists - These are patient "issues": medical problems, allergies, medications, surgeries, etc. They are similar to patient history items, but represent those items currently under treatment and thus that one might logically want to associate with encounters.
  • lists_touch - Tracks whether items in 'lists' have been used. This allows the option to choose 'none' for items in 'lists'. (added in version 4.1.0)
  • list_options – Defines most static lists. This table is maintained and customized via the Lists administrative interface. (added in version 2.9.0)
  • log - A history of information access events. This table can become very large and as of yet there is no easy too for cleaning it out or archiving it. The logging feature also needs work in that some things are not logged that should be.
  • login_mfa_registrations - (added in version 5.0.2)
  • log_comment_encrypt - (added in version 4.2.0)
  • log_validator - (added in version 5.0.0)
  • medex_icons - For MedEX module. (added in version 5.0.1)
  • medex_outgoing - For MedEX module. (added in version 5.0.1)
  • medex_prefs - For MedEX module. (added in version 5.0.1)
  • medex_recalls - For MedEX module. (added in version 5.0.1)
  • misc_address_book - Place that addresses imported from the CCR/CCD/CCDA importing tool are stored. (added in version 4.1.2)
  • modules - (added in version 4.2.0)
  • modules_hooks_settings - (added in version 4.2.0)
  • modules_settings - (added in version 4.2.0)
  • module_acl_group_settings - (added in version 4.2.0)
  • module_acl_sections - (added in version 4.2.0)
  • module_acl_user_settings - (added in version 4.2.0)
  • module_configuration - (added in version 4.2.0)
  • multiple_db - For multiple db zend module. (added in version 5.0.1)
  • notes - Stores document notes. (added in version 2.6.1)
  • notification_log - For the patient automatic notification module (works with tables automatic_notification and notification_settings). (added in version 4.1.0)
  • notification_settings - For the patient automatic notification module (works with tables automatic_notification and notification_log). (added in version 4.1.0)
  • onotes - These are the "office notes" recorded and viewed in the Notes panel.
  • onsite_documents - Patient portal. (added in version 5.0.1)
  • onsite_mail - Patient portal. (added in version 5.0.1)
  • onsite_messages - Patient portal. (added in version 5.0.1)
  • onsite_online - Patient portal. (added in version 5.0.1)
  • onsite_portal_activity - Patient portal. (added in version 5.0.1)
  • onsite_signatures - Patient portal. (added in version 5.0.1)
  • openemr_modules - used internally by the embedded PostNuke code.
  • openemr_modules_vars - used internally by the embedded PostNuke code.
  • openemr_postcalendar_categories - These are the definitions of the calendar event categories. You maintain these by going into Administration / Calendar / Categories in OpenEMR.
  • openemr_postcalendar_events - These are individual calendar events: appointments, In Office, Out of Office, lunch, reserved times, etc. Many of these will be repeating events. Repeating events are supported from (version 4.2.1)
  • openemr_postcalendar_limits - used internally by the embedded PostNuke code. (REMOVED in version 5.0.2)
  • openemr_postcalendar_topics - used internally by the embedded PostNuke code. (REMOVED in version 5.0.2)
  • openemr_session_info - used internally by the embedded PostNuke code. (REMOVED in version 5.0.2)
  • patient_access_offsite - Stores patient credentials for embedded patient portal. (added in version 4.1.0)
  • patient_access_onsite - Stores patient credentials for third party offsite patient portals. (added in version 4.1.0)
  • patient_birthday_alert - (added in version 5.0.1)
  • patient_data - The primary repository for patient demographics data.
  • patient_portal_menu - Used by offsite portal. (added in version 4.2.1)
  • patient_reminders - Used by CDR engine. (added in version 4.1.0)
  • patient_tracker - Used by patient tracker board module. (added in version 4.2.1)
  • patient_tracker_element - Used by patient tracker board module. (added in version 4.2.1)
  • payments - Records information entered via the "Payment" popup window (accessable from the dropdown in the patient menu). (added in version 2.8.1)
  • payment_gateway_details - Used by offsite portal (added in version 4.1.1)
  • pharmacies - The list of pharmacies. This also references the "addresses" and "phone_numbers" tables via its "id" column.
  • phone_numbers - Contains telephone numbers of insurance_companies and pharmacies, and like the addresses table is effectively an extension of those tables.
  • pma_bookmark - Contains information about the canned reports available via the dropdown in the Reports menu. We recommend not using this feature because it's crude and confusing. *(And now missing entirely from version 3.1+). (REMOVED in version 5.0.2)
  • pma_* tables created by embedded phpMyAdmin (REMOVED in version 5.0.2)
  • pnotes - Patient notes. This is a very useful feature where notes associated with a specific patient may be passed around among different users within the clinic, and eventually marked as closed. The assigned_to column indicates who is the current owner of the issue.
  • prescriptions - Prescriptions. In the case of in-house dispensation, drug_id will indicate the drug dispensed.
  • prices – Contains price information for products and services, and supports multiple price levels. This table obsoletes the “fee” column of the “codes” table. Note that the price levels are defined in the “list_options” table (list_id = “pricelevel”). (added in version 2.9.0)
  • procedure_answers - For procedure/lab/module. (added in version 4.1.2)
  • procedure_order_code - For procedure/lab/module. (added in version 4.1.2)
  • procedure_order - For procedure/lab module. (added in version 4.0.0)
  • procedure_providers - For procedure/lab/module. (added in version 4.1.2)
  • procedure_questions - For procedure/lab/module. (added in version 4.1.2)
  • procedure_report - For procedure/lab module. (added in version 4.0.0)
  • procedure_result - For procedure/lab module. (added in version 4.0.0)
  • procedure_type - For procedure/lab module. (added in version 4.0.0)
  • product_registration - (added in version 5.0.0)
  • product_warehouse - For procedure/lab/module. (added in version 4.1.1)
  • registry - Contains metadata regarding "registered" encountered forms. The Administration / Forms panel is used to register forms and maintain this information.
  • report_itemized - (added in version 4.2.0)
  • report_results - Stores results of applicable reports. (added in version 4.1.1(patch 6))
  • rule_action - Used by CDR engine. (added in version 4.1.0)
  • rule_action_item - Used by CDR engine. (added in version 4.1.0)
  • rule_filter - Used by CDR engine. (added in version 4.1.0)
  • rule_patient_data - Used by CDR engine. (added in version 4.1.0)
  • rule_reminder - Used by CDR engine. (added in version 4.1.0)
  • rule_target - Used by CDR engine. (added in version 4.1.0)
  • RXNATOMARCHIVE - Not included on a default installation. Is installed when import the RXNORM codeset. Data history. (added in version 4.1.0)
  • RXNCONSO - Not included on a default installation. Is installed when import the RXNORM codeset. Drug names and unique identifiers (added in version 4.1.0)
  • RXNCUI - Not included on a default installation. Is installed when import the RXNORM codeset. Data history. (added in version 4.1.0)
  • RXNCUICHANGES - Not included on a default installation. Is installed when import the RXNORM codeset. Data history. (added in version 4.1.0)
  • RXNDOC - Not included on a default installation. Is installed when import the RXNORM codeset. Metadata. (added in version 4.1.0)
  • RXNREL - Not included on a default installation. Is installed when import the RXNORM codeset. Relationships. (added in version 4.1.0)
  • RXNSAB - Not included on a default installation. Is installed when import the RXNORM codeset. Metadata. (added in version 4.1.0)
  • RXNSAT - Not included on a default installation. Is installed when import the RXNORM codeset. Attributes. (added in version 4.1.0)
  • RXNSTY - Not included on a default installation. Is installed when import the RXNORM codeset. Semantic types. (added in version 4.1.0)
  • sct_concepts - Not included on a default installation. Is installed when import the RF1 SNOMED codeset. (added in version 4.1.0)
  • sct_descriptions - Not included on a default installation. Is installed when import the RF1 SNOMED codeset. (added in version 4.1.0)
  • sct_relationships - Not included on a default installation. Is installed when import the RF1 SNOMED codeset. (added in version 4.1.0)
  • sct2_concept - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. (added in version 5.0.2)
  • sct2_description - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. (added in version 5.0.2)
  • sct2_identifier - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. (added in version 5.0.2)
  • sct2_relationship - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. (added in version 5.0.2)
  • sct2_statedrelationship - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. (added in version 5.0.2)
  • sct2_textdefinition - Not included on a default installation. Is installed when import the RF2 SNOMED codeset. (added in version 5.0.2)
  • sequences - Holds the last "id" used by the integration_mapping table and for encounter IDs.
  • shared_attributes - (added in version 4.2.0)
  • standardized_tables_track - For tracking of imported standardized tables (such as SNOMED, RxNORM, etc.). (added in version 4.1.0)
  • supported_external_dataloads - For tracking/validation of files used when importing standardized tables (such as ICD9, ICD10, etc.). (added in version 4.1.1)
  • syndromic_surveillance - Used by Syndromic Surveillance module. (added in version 4.0.0)
  • template_users - Used by Nation Notes. (added in version 4.1.0)
  • therapy_groups - For group therapy module. (added in version 5.0.1)
  • therapy_groups_counselors - For group therapy module. (added in version 5.0.1)
  • therapy_groups_participants - For group therapy module. (added in version 5.0.1)
  • therapy_groups_participant_attendance - For group therapy module. (added in version 5.0.1)
  • transactions - Records entries made in the Transactions panel.
  • users - This is a dual-purpose table. It supports the list of local users with their login names, passwords and other information; and it supports the Address Book. Non-local users are identifiable by having an empty "username" value.
  • users_facility - Used to support restrictions for multiple facilities setups. joins users or patient_data to facility table. (added in version 3.1.0)
  • users_secure - Holds user authentication credentials. (added in version 4.1.2)
  • users_settings - Stores user settings. (added in version 4.1.0)
  • valueset - Used to store standardized Value Set, which is used in MU2 CQM calculations(added in version 5.0.0)
  • version - Stores the OpenEMR version and OpenEMR database version. (added in version 4.0.0)
  • voids - Used to void payments. (added in version 5.0.0)
  • x12_partners - These are the entities to whom electronic claims are sent. Normally there is just one, a clearinghouse. Referenced by the insurance_companies and billing tables. (added in version 2.6.1)
Retrieved from "https://www.open-emr.org/wiki/index.php?title=Database_Structure&oldid=28284"