TL;DR
This paper introduces the Columbia Clinical Repository, a novel relational database design for a clinical information system that uses a minimal number of tables and a generic approach to store diverse clinical data. It details the methods for accessing this repository from both local and remote applications, highlighting the trade-offs between static and dynamic SQL, and the use of Data Access Modules (DAMs) and HL7 for data exchange.
What Problem Does This Paper Address?
Managing clinical data effectively is a significant challenge in healthcare. Existing clinical information systems often struggle with the complexity of storing and accessing a wide variety of patient data, from structured lab results to unstructured narrative reports. The authors address the need for a robust, flexible, and efficient clinical data repository that can serve as the foundation for a comprehensive clinical information system. Specifically, they aim to provide a system that can handle transactions for individual patients efficiently, store controlled vocabulary, enable real-time monitoring of patient events, and be accessible to diverse applications running on different platforms.
Methods
The Columbia Clinical Repository is implemented as a relational database on an IBM mainframe using DB2. A key innovation is its generic design employing a small number of tables. New data elements are added by defining them in a Medical Entities Dictionary (MED) rather than altering the database schema. This approach is similar to other systems but is normalized, based on a conceptual schema of clinical events.
To facilitate access, Data Access Modules (DAMs) were developed. These are mainframe programs written in PL/I that can issue static SQL. For mainframe applications, a "query template" serves as the interface, allowing DAMs to execute pre-defined static SQL statements. For remote applications, Health Level 7 (HL7) is used as the data exchange protocol, with a "clinical data mediator" on a UNIX machine facilitating communication with mainframe DAMs via APPC.
The paper also compares static SQL (compiled into programs) with dynamic SQL (parsed at execution time). Static SQL is generally more efficient but less flexible, while dynamic SQL offers greater flexibility but incurs more overhead.
Datasets
Not discussed in the paper. The paper focuses on the architecture and access methods for the Columbia Clinical Repository, which is a system implementation rather than a study on a specific dataset.
Key Insights and Ideas
- Generic Design with Minimal Tables: Storing clinical data in a highly normalized, generic relational schema with a small number of tables is a powerful approach. It allows for flexibility in adding new data types without complex schema modifications, relying instead on a central dictionary (MED).
- DAMs as Abstraction Layers: Data Access Modules (DAMs) are crucial for insulating client applications from the underlying database structure and SQL complexity. They handle request parsing, data conversion (using MED), database operations, and notification.
- HL7 for Remote Access: Using HL7 as a standard for remote applications provides independence from the repository's implementation details, simplifying integration for ancillary systems.
- Query Templates for Mainframe Access: Locally defined query templates offer a simpler interface for mainframe applications compared to HL7, though they require maintaining a separate set of DAMs.
- Procedural Code for Complex Temporal Queries: For certain complex temporal queries (e.g., "the last 3 serum sodium levels"), procedural code embedded within DAMs is significantly simpler and more efficient than expressing the same logic in SQL alone.
- Trade-offs in SQL: Static SQL offers better performance for routine operations, while dynamic SQL provides the flexibility needed for ad hoc queries and more complex data manipulation, albeit with a performance penalty.
Key Results
- Performance Comparison: Timing studies showed that review of laboratory data using a mainframe program took an average of 0.375 seconds per transaction. Upload of laboratory data required 0.685 seconds per transaction.
- Static vs. Dynamic SQL Performance: For remote queries, static SQL was faster than dynamic SQL. Retrieving pharmacy orders took 0.98 seconds with static SQL versus 1.58 seconds with dynamic SQL. Retrieving lab results took 0.81 seconds with static SQL versus 0.97 seconds with dynamic SQL. The authors note that the lab query, which involved a relational join, was faster with static SQL.
Limitations and Future Work
- Maintenance Burden of DAMs: The software maintenance of DAMs that interpret client requests and execute SQL is identified as a significant drawback, consistent with earlier findings that data access is a costly aspect of developing clinical modules.
- Inflexibility of Static SQL: Static SQL's inflexibility, particularly in handling variable table names or complex joins, contributes to the complexity of DAMs.
- User Understanding of Generic Design: While the generic design provides efficient access, it has proven difficult for users to understand.
- User Interface Complexity: The HL7 standard, while useful for remote access, is described as cumbersome for human users like developers of review programs and MLM authors.
- Future Work: The paper suggests that data access modules supporting dynamic SQL are appropriate for high-level, flexible data access needs, but they must also provide a user-friendly view of the database and support temporal operations not easily expressed in SQL. Further study is indicated in this direction.
Summary updated