Database Structure

From OpenEMR Project Wiki

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 exceptions:
  • form_eye_mag (in InnoDB, was throwing following error on some newer OS's 'Row size too large (> 8126).')
Above table(s) are 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.1, the form_eye_mag table was removed; thus database was 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 - I don't see anything that uses this. (considering removal)
  • 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:
    • 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. 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).
  • 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".
  • 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.
  • categories_seq - Holds the value of the last-used "id" for the categories table.
  • categories_to_documents - Cross-references the categories and documents tables.
  • 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.
  • claims - records events related to the queueing and generation of claims. The Billing page summarizes this information for each encounter shown.
  • 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.
  • code_types - Stores the code types used in the codes table. (added in version 4.0.0)
  • config - Not used anymore in the codebase. (considering removal)
  • config_seq - Presumably the last "id" used in the config table. This is not used anymore in the codebase.
  • 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.
  • 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.
  • drug_inventory - Supports in-house drug sales. Each row represents a "lot" of purchased drugs.
  • drug_sales - Supports in-house drug sales. Each row represents a drug sale, i.e. an invoice line item.
  • drug_templates - Supports in-house drug sales. Each row represents a shortcut for a common dispensation of a drug.
  • eligibility_response - 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.
  • enc_category_map - Used by CDR engine to categorize encounters (added in version 4.1.0)
  • erx_ttl_touch - Stores information for ePrescribe, NewCrop.
  • esign_signatures - Stores information relating to the electronic signing and locking of Encounter Forms.
  • extended_log - Contains logging.
  • 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.
  • 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_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.
  • forms - An index of all encounter form instances.
  • gacl_* - Contains all the access control tables (embedded php-GACL)
  • geo_country_reference - Maps country names to codes. Not clear if this is used.
  • geo_zone_reference - Maps U.S. states, Canadian provinces and some other geographic regions to codes. Note clear if this is used.
  • 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.
  • 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.
  • issue_types - Lists the issue types.
  • lang_constants - Supports language translation. Assigns an ID to each translatable string.
  • 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_languages - Supports language translation. Identifies the supported languages.
  • 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.
  • 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_comment_encrypt - (added in version 4.2.0)
  • 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)
  • notes - I'm not sure if or how this table is used.
  • notification_log - For the patient automatic notification module (works with tables automatic_notification and notification_settings)
  • notification_settings - For the patient automatic notification module (works with tables automatic_notification and notification_log)
  • onotes - These are the "office notes" recorded and viewed in the Notes panel.
  • 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.
  • openemr_postcalendar_topics - used internally by the embedded PostNuke code.
  • openemr_session_info - used internally by the embedded PostNuke code.
  • 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_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).
  • 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+)
  • pma_* tables created by embedded phpMyAdmin
  • 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”).
  • 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_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 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_relationships - Not included on a default installation. Is installed when import the SNOMED codeset. (added in version 4.1.0)
  • 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)
  • 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.
  • 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.
Retrieved from "https://www.open-emr.org/wiki/index.php?title=Database_Structure&oldid=28247"