Two weeks ago I wrote about the value of EHR timelines and audit trails. Today’s post takes that discussion a little further by demonstrating various ways of keeping track of data accesses and changes in EHR data element values. Of course, this functionality is useful for any clinical database that might be covered under HIPAA. This discussion is divided into two sections. The first addresses basic techniques for tracking data value changes. The second describes a method for adding audit trail capability.
Keeping a Data History
Databases can be designed to serve many purposes. One feature that is often useful is the ability to track trends; that is, keep a chronological record of changes to specific data elements or records over time. For example, one may wish to track referral patterns to a primary care clinic based on patient ZIP Codes. Consider how this might be done using a demographics table with the design depicted below.
Table 1 – Demographics
Using this table, one can only know the patient’s current ZIP Code. Thus, it is possible to determine only where all patients currently live. It is useless for determining where patients who attend the clinic have lived over the years. In addition, from this table, we cannot determine how long a patient has lived in a neighborhood or if they have moved while they have been patients. In order to capture trends, one needs to track ZIP Codes and their active dates. The easiest way to do this is to redesign the database and create a ZIP Code table.
Table 2 – ZIP Code
Table 1a – Demographics
Using the ZIP Code table, we now have a record of past locations that are connected to the patient by the medical record number (MR#). A flag (Active) is used to indicate the current ZIP Code (users see only the record flagged as active when accessing patient information).
When it is known in advance that historical data are required, designing a database to accommodate that requirement is easy, as demonstrated above. A more interesting situation occurs when the need for historical data arises long after the database has been built and populated. In this situation, one can redesign the database or add a trend capture feature. When redesign is not an option, what can be done?
Here, the goal is to begin tracking ZIP Code trends without redesigning the Demographics table (Table 1). One way to do this is by creating a new table, which we will call the DataHistory table, and using it to keep a record of changes to the ZIP Code field in the Demographics table.
In the last post, Database Shopping, I discussed data integrity features common to RDBMS. Two of them can be used to solve the current problem–triggers and stored procedures. Both consist of SQL code that resides in the database and is attached to a table. I will use a stored procedure (SP) for this example (stored procedures can be called by application programming code, triggers cannot).
Stored procedures can be used to perform a range of actions within a database and make it easy to maintain a data history table. This example uses a SP that executes whenever the value changes in the ZIP Code field of the Demographic table. Here is the structure of the DataHistory table.
Table 3 – DataHistory
Table 1 – Demographics
The RowID is the primary key for the table. OTblName indicates the table of origin of the tracked data element. OTblRecID captures the primary key of the record containing the data element being changed. The Field and Value columns hold the actual data elements being tracked. ChDate captures the date the data element’s value changed. In this example, the old ZIP Code (44102) was placed in the DataHistory table when the new ZIP Code (44105) was added to the Demographics table (January 24, 2012). The active ZIP Code remains in the Demographics table. Whenever the ZIP Code changes, the former ZIP Code is not forgotten. Below is the pseudocode for a stored procedure that maintains the ZIP Code history.
CREATE PROC Update Zip
Parameters @OTbleRecID, @OTblName, @New, @Old
SET ZIPCode = @New
INSERT INTO DataHistory (OTblName, OTblRecID, Field, Value, ChDate)
Values (@OTblName, @OTblRecID, “ZIP”,@Old, DATE())
This procedure first inserts the new ZIP Code into the Demographics table. Next, it adds the old ZIP Code to the DataHistory table along with the date it became invalid. This SP is executed only when an existing record is updated. Keeping track of the date the initial ZIP Code was captured (i.e., the date when the patient registered at the clinic) could be done by using a SP that writes a record to the DataHistory table whenever a new record (patient) is added to the Demographics table.
The methods discussed thus far describe ways to track data element values over time, either as part of the original design or as a later add-on. However, what if the goal is more ambitious such as tracking every user interaction with the database? Actually, the approach is very similar.
EHR 2014 Certification standards require that EHRs keep audit logs. At a minimum, they must log the following data elements:
Date and Time of Event
Type of Action (additions, deletions, changes, queries, print, copy)
Identification of the Patient Data that is Accessed
An audit trail that conforms to certification requirements can be created using the same techniques applied in maintaining the DataHistory table. The DataHistory table already has most of the necessary fields: date, table affected, patient ID (MR#). Adding Time, UserID, and ActionType (update, delete, insert, etc.) fields would seem to meet certification requirements. (Exactly what must be captured in order to satisfy the final requirement, Identification of the Patient Data that is Accessed, is not clearly stated– unless I am missing something.)
There are many ways that one can keep track of what goes on inside a database. Historical information can be useful in many situations and well worth the effort involved in capturing and maintaining it. Whatever the impetus for keeping historical data, you will get the best results when tracking needs are incorporated into the initial database design. However, even databases that have been in use for a while can be updated to provide this capability to some extent.