|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_overd||Overdosage symptoms and treatment||Varchar(255)||Xxxxxxxxxx||N/A|
|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_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|
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.
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.):
|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.