One of the most important skills for Healthcare IT is an understanding of healthcare database concepts and structure. It doesn’t matter if you are a Project Manager, Application Analyst, or System Administrator; an understanding of databases in Healthcare will take you a long way in your career.
So with all the different Healthcare IT systems on the market, how can one article provide value? In my career I’ve worked with a medical supply software system, pharmacy systems, three different electronic medical records systems, a home health application, and various other products. They all had their differences, but had many things in common.
For example, just about every system has some kind of “departments” table. In some systems, it may be called locations, or areas- but the idea is the same. With that, I will try to use vendor-neutral descriptions. Let’s dive in.
First off, let’s review some basic database concepts:
- A database is an organized collection of data and is hosted on a database server
- A database contains tables, usually many
- Tables contain rows
For our example, let’s say we have one database called ‘HEALTH_PRODUCTION’, with many tables defined within that database. Here are some tables that are likely to exist:
The Enterprise Table
The enterprise table is defined to represent your organization at the highest level. There will have many departments under this entity, and reports sent to Federal regulatory agencies will reference the enterprise. It could also be called “Facility” or “Organization”.
- “Smith County Hospital” is an Enterprise that has many departments
- “Jones County Hospital” would be another Enterprise
The Departments Table
The departments table contains all the departments in your enterprise. There will be clinical and non-clinical locations, and most but not all will have physical locations. Those without physical locations are sometimes called virtual departments, and are just for accounting or administrative purposes. Most will have some kind of cost center code. Here are some examples:
- Davis Primary Care
- East Side Urgent Care
- Emergency Services
- Information Technology
The Users Table
The users table contains all the users in a system. You might guess that all users in a particular system actually log into that system. However, that’s not always the case. An example would be a doctor who does not work for your organization whom you refer patients to. Referrals to this doctor are part of the patients’ official medical record, but that doctor has no direct connection with your organization. Users will have varying levels of security in any given system. Here are some examples of user types:
- Dr. Joyce Carson, MD
- James Carson, RN
- Tom Duncan, CIO
- Sarah Chang, CEO
- James Reeves, Database Administrator
- Bob Sherman, Materials Management
The Patients Table
The patients table will usually be huge, possibly having one row for each patient. It will have the demographic data on each patient, and may or may not have specific clinical information. For this example, let’s assume clinical visits and orders like current meds are not in this table.
The Orders Table
The orders table contains orders that get placed for patients, such as:
- X-Ray of the left ankle, Stat
- Keflex 100 mg once daily for seven days
- Referral to Orthopedic specialist
Orders are almost always authorized and signed by a licensed provider such as a physician, therapist, and in some cases a nurse. In some systems, initial order entry could be done by a non-provider, but the order would be in a ‘pended’ status until signed by the provider.
The Items Table
While the previous paragraph was about the orders that get placed, here we have the items, meds, or procedures themselves that are defined in their own table, waiting to be ordered. Duration, quantity, and timing are not usually hard-coded into this table, but are specified when specific orders are placed.
Example of Record Linking
If clinical information is not in each patient data row, then there will be pointers to other tables. A pointer is just an identifier in a table to provide linking to data in other tables. Notice here we have an order for Glucose Test Strips that has a column called “Ord_Item”…
That happens to be the same value we have in the Items table for column “Item_ID”…
The Medications Table
Medications usually have their own table, separate from other things that get ordered. Due to complexity, liability, and regulatory issues, most systems rely on imports from third-party firms that provide a comprehensive formulary of medications. One vendor who provides this data is First Data Bank, and we refer to the process as the “FBD Import”.
The Encounters Table
These are the events that occur when anything is done on a patient. Examples are:
- Scheduled office visits
- Admissions to the hospital
- Telephone consultations
The Notes Table
A system may have a table that stores notes that caregivers write to document a patient encounter. It may have a field that stores a large amount of free text to be displayed in an application, possibly using HTML or other formatting.
The Diagnosis Table
This contains industry specified codes and descriptions for every condition, disease, or disorder imaginable. Like the medication formulary, this data is almost always downloaded from an outside data provider. The most popular one is Intelligent Medical Objects (IMO).
Types Of Healthcare Data
Clinical healthcare data can be organized into several different categories:
- Patient charting data from electronic health records systems:
- Progress notes
- Vital signs
- Population health data, such as disease registries,
- Clinical research/trials data,
- Billing data,
- Administrative data,
- Non-discrete data such as documents that are scanned and attached to patient charts.
This doesn’t cover all the possible database concepts in Healthcare IT, but should give you a good starting point. If you want to learn much more about databases in healthcare, check out this course on Udemy called Data Analytics: Intro to SQL using Healthcare Data (SSMS), created by Curtis Norman.