COMMENTARY | We’re all pretty much aware that relational databases like MySQL, ORACLE, and SQL*Server have basically won the war between hierarchical, network, associative, and object-oriented databases. However, in the healthcare industry the need to store semi-structured and highly flexible data has never been greater. Relational databases work well for heavy transaction-oriented business systems, but in healthcare, especially on the clinical side, there is a growing need for more extensible “dynamic” data models because information associated with healthcare entities such as patients, hospitals, and insurance plans change with time, environment, location, and facility.

Dynamic data models, for the purposes of this discussion, refer to models whose structure can change (accommodate new data types) without requiring database administrators to modify physical tables and indexes. So, how can you make relational databases whose structures are typically fixed at design time act more dynamic? Here are a few options:

Use post-relational databases such as Intersystems Caché or Progress ObjectStore. Since most vendors understand that the relational model has won, object-oriented databases and multidimensional databases such as Caché and ObjectStore have made some pretty big gains, since they no longer only compete with SQL and the relational concept; instead, they embrace SQL and still allow flexible modeling. Caché is especially interesting because it’s based on the MUMPS legacy environment, which fundamentally allows for dynamic data models. Its innate ability to manage dynamic structures is one reason why MUMPS is so heavily used in healthcare.

Use the Entity-Attribute-Value (EAV) Model. The EAV is a generalized form of row modeling, which basically means a single table with three columns, an entity (such as a patient), an attribute (such as an allergy, which is actually a pointer into a metadata table), and a value for the attribute (e.g., penicillin). In EAV models, a single fact is stored in an “entity attribute” table. By contrast, in a conventional table that has one column per attribute, a row can store multiple facts. EAV works well when the number of attributes that potentially apply to an entity is significantly more than those that actually apply to an individual entity. For instance, if a single patient can potentially have hundreds of attributes, but typically each patient only has a dozen attributes, then EAV may work well. Although EAV models are quite extensible, they are typically considered much harder to manage by DBAs because applying standard relational techniques such as normalizing, adding constraints, attaching triggers, etc., doesn’t work very well. While I have used the EAV model with great success in the past, I only suggest it for groups with very strong understanding of relational data models that can understand the drawbacks as well as the benefits.

Use a mixed standard relational and Entity-Attribute-Value (EAV) Model. One other mechanism I’ve used with great success is to use standard (normalized) entity tables for many attributes (using columns) but also have an additional EAV set of tables for the entity to hold the “sparse” attributes where dynamic data are needed. This model allows you to do traditional modeling for the majority of “known” requirements and at the same time use EAV for the unknown portions.

In my last article called “Healthcare Data Models Matter,” I argued that applications come and go but data live on forever and that you need to focus on making sure that your models are extensible. Caché as a product and EAV as a pattern are both real ways to ensure that your applications remain flexible because you have a dynamic data model.

Shahid N. Shah is a health-IT blogger and Java/.NET enterprise architect who has worked for CardinalHealth, American Red Cross, NIH, and COMSYS. His blog is The Healthcare IT Guy, and he also runs the HITSphere blog aggregator. E-mail: shahid.shah@netspective.com.