Difficulties in Hospital Database Architecture


ER Diagram

ER Diagram.
Figure 1. ER Diagram.


Database Schema.
Figure 2. Database Schema.

Data Dictionary

Table Name Attribute Name Contents Type Format Range Required PK or FK FK reference table
substance sub_id Identification number of a substance integer ###### 1-999999 Y PK
sub_name Name of the substance Varchar(24) Xxxxxxxxxx N/A Y
sub_mng Substance management rules Varchar(255) Xxxxxxxxxx N/A
sub_therind Therapeutic indications Varchar(255) Xxxxxxxxxx N/A Y
sub_ddi Drug-to-drug interactions Varchar(255) Xxxxxxxxxx N/A
sub_cntind Contra-indications Varchar(255) Xxxxxxxxxx N/A
sub_adm Substance administration Varchar(255) Xxxxxxxxxx N/A
sub_overd Overdosage symptoms and treatment Varchar(255) Xxxxxxxxxx N/A
sub_storeq Storage requirements Varchar(255) Xxxxxxxxxx N/A Y
gr_id Medication group identification number integer ###### 1-999999 Y FK group
Group gr_id Medication group identification number integer ###### 1-999999 Y PK
gr_name Medication group name Varchar(24) Xxxxxxxxxx N/A Y
gr_descript Medication group description Varchar(255) Xxxxxxxxxx N/A Y
Storage sto_id Storage admission identification number integer ######### 1-999999999 Y PK
sub_id Identification number of a substance integer ###### 1-999999 Y FK substance
sto_date Stocking date date DD-MON_YYYY N/A Y
sto_usedate Beyond-use date date DD-MON_YYYY N/A Y
sto_util Is the substance utilized? yes no YES/NO N/A Y
emp_id The identification number of an employee, who utilized the substance integer ##### 1-99999 FK employee
employee emp_id Employee identification number integer ##### 1-99999 PK
emp_fname First name of an employee Varchar(24) Xxxxxxxxxx N/A Y
emp_lname Last name of an employee Varchar(24) Xxxxxxxxxx N/A Y
emp_midinit An employee’s middle initial Char(1) X N/A
dept_id The identification number of a department integer ###### 1-999999 Y FK department
department dept_id The identification number of a department integer ###### 1-999999 Y PK
dept_name Name of the department Varchar(24) Xxxxxxxxxx N/A Y
dept_head The name of the head of the department Varchar(24) Xxxxxxxxxx N/A Y


Design Difficulties

While designing the database, I encountered two major difficulties that appeared troublesome to overcome. First, it was hard to decide on organizing therapeutic indications. I wanted to provide links to the conditions that could be treated with medication; however, most of the drugs are prescribed for symptoms rather than to cure a disease. Therefore, it was chosen to have a therapeutic indication in plain text.

Second, there appeared a concern about database usability and convenience due to a large number of attributes and a massive amount of information. One of the primary objectives of the design was to focus on substance management rules; however, the relevant information was scattered across many attributes. It was decided to make another attribute called sub_mng that would include concise information and alerts about the substance’s administration, storage requirements, and utilization. Even though the architecture of the database seems acceptable, alterations may be made in the process of development.

Hosting Considerations

As the information stored in the proposed database will be in the text rather than multimedia, the server is required to be high-end. According to iWeb, the optimal characteristics for the server would be as follows (“Hardware Guide,” n.d.):

Processor RAM Storage Raid Bandwidth
Intel® Xeon® E5-1620 V4 32GB 2 × 2TB HDD SW 30TB

The proposed server can be rented and changed if it is underused or does not meet the hospital’s needs.

Required Resources and Estimated Budget

The proposed database requires substantial resources to gather the information and enter it into the database. A hospital using the proposed database design will need to hire a database administrator who will be in charge of entering data and managing the database. The person will have to work together with a pharmacist who will gather all the relevant data. The estimated budget will consist of a salary of a system administrator, approximately $61,000 a year (“Average systems administrator salary,” n.d.), and payments for the hosting, $3,000 a year (“Hardware Guide,” n.d.).

The salary of the pharmacist is not counted, as every hospital usually has on. However, a hospital may need to pay for 20 additional hours of a pharmacist to insert all the data with a database administrator. The estimated cost of the additional hours would be $1800 (“2016 pharmacist,” 2016).

In addition to the listed expenses, personnel training costs should be counted. Every employee using the database will have to spend 2 hours on personalized training with the system administrator. Therefore, the payments for extra hours should be added to the cost total. However, database management may also be done by the hospital’s systems administrator if he or she has the required skills. As the database will use cloud-hosting, the hospital will not need to spend additional money on new computers, or wiring (“7 benefits,” 2016). Therefore, while the database may need considerable effort to set up, the estimated maintenance budget may be as low as $3000 a year.

Design Rationale, Limitations, and Extensions

The design was developed to meet all the needs of people working with pharmaceutical products on hospital grounds. All the decisions were made by database design demands offered by Coronel and Morris (2019), which include design elegance, processing speed, and information requirements. The proposed model includes a massive amount of information that can be easily accessed without special training. In short, the design was chosen for its simplicity and efficiency.

The limitations of the design are closely connected with the possible future extensions. First, the database does not provide information about pharmacodynamics and pharmacokinetics of the substances, nor does it offer the composition of the drugs. Therefore, one of the possible extensions would be to add the information as attributes of the substance table. Second, the database does not offer information about patients and the medications prescribed to them.

Consequently, the design could be extended to incorporate information about patients, including their names, phone numbers, addresses, current conditions, medical history, prescribed drugs, and billing information. The addition of clients’ current conditions would be beneficial to avoid prescription errors due to contra-indications. In short, even though the design is chosen for its efficiency and information requirements, it can be improved or become a part of a larger hospital database.


Average systems administrator salary. (n.d.). Web.

Coronel, C., & Morris, S. (2019). Database systems: Design, implementation, and management. (13th ed.). Web.

Hardware guide. (n.d.). Web.

7 benefits of using a cloud database service. (2016). Web.

2016 pharmacist salary guide. (2016). Web.