Digitizing Patient Records Management: From Systems Analysis to Application Development
A transformative project to develop a patient record management application, supporting the OB-GYN Department at Martinez Memorial Hospital in their transition from paper-based records to a digital system.
The Challenge
The project aims to transition the OB-GYN Department at Martinez Memorial Hospital from a paper-based patient record system to a comprehensive digital application. This transformation involves several key challenges:
Medical Subsystem Transition
- Data Collection and Storage: The new system should be able to collect and store detailed medical information, including historical and current data from patient visits.
- Clinical Focus Areas: The system needs to support the department’s clinical consultations in women’s wellness, preventive medicine, and pregnancy care.
Administrative Subsystem Enhancement and Suitability
- Operational Tasks: The new system must streamline patient registration, scheduling, and billing processes, traditionally managed by medical secretaries. It is critical that it must not cause any disruptive changes to the current operational flow. Registration includes capturing personal details and retrieving patient records, while scheduling is currently manual and first-come, first-served with no online options. Billing must accommodate varying fees based on consultation type and patient status.
Essentially, The goal is to develop a digital solution that enhances efficiency and accessibility in managing patient records and administrative tasks.
Methodology
The study employs a comprehensive 10-step process that meticulously addresses every aspect of the project lifecycle. It starts with a thorough understanding of the client’s processes and needs, progresses through data modeling, and culminates in the development of the application using Python-Dash and PostgreSQL. This structured approach guarantees that each phase of the project is thoroughly addressed, leading to a well-developed and effective application tailored to the client’s needs.
Requirements Analysis and Modeling
Requirement Elicitation
| Functional Requirements | Non-Functional Requirements |
|---|---|
| The system must allow doctors and secretaries to search for and access records of returning patients. | Operational: The system should run on clinic computers and be connected to a printer. Internet access is optional for data backup. |
| Users must be able to create new patient charts by entering personal information: full name, address, contact number, birth date, and civil status. | Performance: The system must be operational at all times, accessible by only two users (the physician and the secretary) simultaneously. |
| Users should be able to add new records, including vitals, visit dates, and medical notes (e.g., clinical notes, lab results). | Security: Access is restricted to physicians and secretaries; patients cannot view their own or others’ data. The system must have virus protection. |
| The system must record payment amounts for services rendered during consultations. | Cultural and Political: Transactions will use Philippine pesos. Patient information must comply with the Data Privacy Act of 2012. |
Use Case Diagramming
A Use Case Diagram is a powerful tool used to visualize the interactions between different actors and a system, highlighting the various processes involved. In the context of healthcare, particularly in an OB-GYN department, it helps in identifying key areas for improvement, ensuring that the system supports both efficient operations and high-quality patient care.
Actors and Their Roles
Physician: The physician is the sole actor who has the ability to update patient charts (given the sensitive nature of data). In addition, they are able to review patient history, conduct physical exams, and order laboratory tests. The physician’s role is crucial in diagnosing and treating patients, ensuring that medical records are accurately maintained and updated.
Medical Secretary: The medical secretary manages the administrative and operational tasks of the department. Their duties encompass patient registration, verifying patient visiting history, and handling billing after consultations.
Conceptual Data Modeling
The ERD for the OB-GYN department shows five key entities connected through the associative entity Patient Visit, which links all other entities:
- Patient Visit - Medical Secretary: Each visit is managed by one medical secretary, who may handle multiple visits.
- Patient Visit - Patient: One visit accommodates one patient, but patients can have multiple visits.
- Patient Visit - Service: One service per visit, though services can be repeated across visits.
- Patient Visit - Payment: One payment per visit, tied exclusively to that visit.
- Patient Visit - Medication: Visits may include zero or multiple medications, customized for the patient.
As illustrated in the ERD, Patient Visit resides at the heart of the entities. It is an associative entity connecting all the other entities, allowing it access to three of the primary keys namely Patient_ID, MedSec_ID, and Service_ID. An associative entity aims to join instances of entities and keep their respective attributes along the way. Patient Visit was made into such due to the ternary nature of the connections between Patient Visit and the other three entities: Patient, Medical Secretary, and Service; and because it essentially houses the relationship among them. It has two additional attributes, Visit Note and Visit Status, with the former further branching out to several attributes needed in consultation services.
Relational (Logical) Data Modeling
The ternary relationship among Patient, Service, and Medical Secretary is represented by the associative entity Patient Visit, which includes the primary keys of these entities. This makes Patient Visit a child entity of the three.
Patient, Service, and Medical Secretary are converted into regular tables with their attributes and primary keys. The one-to-many relationship between Patient Visit and Medication transfers the primary key VISIT-N as a foreign key in the Medication table. The one-to-one relationship between Patient Visit and Payments maps VISIT-N to the Payments table.
The resulting data model is normalized with minimal redundancy, forming the basis for the relational database design for the OB-GYN Clinic at Martinez Memorial Hospital.
Application and Database Building
To effectively implement the systems analysis and data modeling, the application is developed using the Dash framework. Dash, a powerful Python framework, allows for the creation of interactive web applications with seamless integration of data visualization and user interface components. It is particularly well-suited for building data-driven applications that require real-time updates and dynamic interactions.
The application is connected to a PostgreSQL database, which serves as the backend for storing and managing patient data, medical records, and other relevant information. PostgreSQL is a robust and scalable relational database management system that ensures data integrity, supports complex queries, and provides efficient data retrieval and storage.
- GitHub Repository: View the project on GitHub
- User Manual: Download the user manual
Above presents sample snapshots of the application. At a high-level, there are 6 modules:
Dashboard Provides an overview of key metrics and system status, offering a centralized view of important information such as patient statistics, recent activities, and system alerts.
Patient Records Allows users to access, search, and view detailed patient records, including medical history, visit notes, and treatment details, facilitating informed decision-making.
Patient Registration Manages the process of registering new patients by capturing their personal information and creating their profiles in the system.
Queue Organizes and displays the list of patients waiting for consultation or service, helping manage patient flow and appointment scheduling efficiently.
Medicine Inventory Tracks and manages the inventory of medications, including stock levels, usage records, and reordering needs to ensure availability of necessary drugs.
Payment Handles billing and payment processes, recording payments for consultations and services, and generating transaction summaries for financial tracking.