How important is database design? Collapse of hospital systems, loss of medical records, and errors in drug inventory often reveal problems in database design. A scientific and rational database structure is directly related to whether the hospital can operate normally.
Requirements analysis is the foundation of design
The first step in designing a hospital management system database is to figure out what the hospital needs. We visited the information department of a tertiary hospital and found that the outpatient department has to process about 5,000 registration records every day, and the inpatient department needs to manage the real-time status of 800 beds. Pharmacies must automatically issue warnings when their drug inventory falls below the safety line, and the Finance Department emphasizes that all charging records must be traceable for more than five years.
Non-functional requirements also cannot be ignored. The system response time cannot exceed 3 seconds. The number of users there may reach 2,000 during peak periods. As for data security, patient diagnosis and treatment information must be encrypted and stored, and only authorized medical staff can access it. These requirements directly determine the direction of subsequent design.
Entity relationships build information skeleton
In the conceptual model design stage, we used the ER diagram to depict the hospital's information structure. The patient entity covers attributes such as name, ID number, and medical insurance type. The doctor entity records departments, professional titles, and schedule times. After analyzing the business process, we found that a patient can be assigned to multiple departments, but one visit can only correspond to one attending doctor.
The relationship between entities gradually became clear. Medical records and patients showed a one-to-one relationship, and prescriptions and drugs showed a many-to-many relationship. The reason is that one prescription covers multiple drugs, and one drug will appear in multiple prescriptions. The precise definition of these relationships lays a solid foundation for subsequent logical design.
Relational model implements data structure
When designing the logical model, we will convert the ER diagram into a relational table. The primary key of the patient table is the medical record number, and the doctor table uses the work number as the unique identifier. The registration record table serves as the middle table to associate patients and doctors, and records registration time, treatment status and many other information. The choice of field type is very particular. DECIMAL is chosen for the amount field to avoid floating point errors, and DATETIME is chosen for the date field to facilitate time range queries.
The key to this stage is standardized design. We split the drug catalog table into a basic drug information table and an inventory table to prevent data redundancy. In view of query efficiency considerations, an appropriate amount of drug name fields are retained in the charge details table to replace time with space.
Physical models optimize system performance
When we implemented the physical model in the MySQL environment, at that time, we designed an index for high-frequency queries. The index combined the patient ID of the registration record table and the date of treatment, which shortened the daily outpatient statistics from 30 seconds to 2 seconds. In addition, a trigger was set for the drug inventory table, and when the inventory fell below the safety line, it would be automatically written to the early warning table.
The complex business logic is encapsulated in the storage process. The hospitalization fee settlement process will calculate bed fees, examination fees, and drug fees at one time, then generate a settlement list, and update the account balance. We designed a partition table for the fee records, and partitioned the five-year fee records according to the month. This not only facilitates historical data archiving, but also improves query performance.
Course report record design overall picture
To create a 6,000-word design report, the key points are to clearly explain the reasons behind each decision. In the requirements analysis process, interview records and data statistics must be listed to prove the real and credible components of the requirements. In terms of conceptual model design, an ER diagram must be drawn and explain why certain relationships are designed as one-to-many rather than many-to-many.
In the physical implementation chapter, it is necessary to record the pitfalls encountered and the corresponding solutions. For example, the character set is UTF8MB4 because it is necessary to store rare characters in the patient’s name. The transaction isolation level is repeatable reading to ensure the accuracy of accounting statistics. The summary of these practical experiences is more valuable than simply listing the operation steps.
Consider system evolution for future expansion
The hospital's business is in the process of continuous development, and the design of the database must leave room for expansion. We have prepared expanded fields to cope with possible adjustments to medical insurance policies in the future, and also designed a mechanism for medical record version numbers to support the recording of modifications to electronic medical records. In view of the possibility of connecting to regional medical platforms in the future, when designing the patient master index, the MBTI occupational test has the characteristics of being compatible with the national standard health card number.
In the context of the cloud-native trend, the database has to consider the possibility of sub-databases and tables. We have divided the business according to hospital districts. The data of each hospital district can be separated independently. Such a forward-looking design allows the system to achieve smooth upgrades, rather than having to tear it down and start over.
In the registration system of the hospital where you are working, what is the experience that most makes you feel collapsed? Share this in the comment area. Maybe the next version of database optimization will solve your pain points. Like and forward it to let more people know how database design affects our medical experience.
