EHR Design Basics: Tracking Data Changes and Accesses Using Audit Trails

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.

MR# LName FName ZIPCode
2341 Doe John 44102

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.

RowID MR# ZIPCode Active ActiveDate
1000 2341 44102 Y 01/24/2012
637 2341 44105 N 11/17/2009

Table 2 – ZIP Code

MR# LName FName
2341 Doe John

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.

RowID OTblName OTblRecID Field Value ChDate
200012 Demographics 2341 ZIP 44102 01/24/12

Table 3 – DataHistory

MR# LName FName ZIPCode
2341 Doe John 44105

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.

Parameters  @OTbleRecID, @OTblName, @New, @Old

UPDATE Demographics
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
Patient Identification
User Identification
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.



  1. This is certainly a good way of keeping history. We do it a little differently though (not sure if this will let me use HTML in the comment so I won’t try):

    We have our original Demographics table, similar to above, but our HistoryTable has the same columns as the ‘OriginalTable’ and instead of a userid column, we have a foreign key to an Actions table. There are a few more points of data we keep for easier lookup that I won’t show here.

    Table: Address
    Fields: Street1, Street2, City, State, Zip, Country

    Table: AddressHistory
    Fields: Street1, Street2, City, State, Zip, Country, FK->Action

    And our Actions table keeps track of When and Who did it

    InsertDate, Userid, Type, comment(optional)

    This way, we can keep track of modifications that happened two multiple things in the same transaction. For instance
    we also have a PhoneNumber table, which might have been modified by clicking the same button which triggered
    an HL7 ADT message. We want to tie all of those things together with them all pointing to the same Action row. So we know all the things that user caused to happen by pushing that button.

    We realize this duplicates a decent amount of data, but we have found that it is far more valuable to be able to have a ‘show history’ button on all our elements so that our users can see who has made changes to what and when.

    1. Nick, thanks for your comment. The method you use is a good one, very flexible. Thanks for sharing it with EHR Science readers!

      I hadn’t given much thought to audit trails and historical data until I read the article a few weeks ago about the difficulties of using EHRs in legal proceedings. I was struck by the idea of being able to rollback an EHR to a specific point in time–sort of like a really sophisticated “undo” function. If nothing else, it would make for a great e-discovery tool!

Leave a Reply

Your email address will not be published. Required fields are marked *