HIM 650 Database Project Proposal Part 2
Most drugstores and a growing number of practitioners depend on technology systems to
keep patient profiles up to date, including diagnostic data, laboratory data, and prescription data.
These systems offer some protection against pharmaceutical mistakes, treatment repetition, and
drug reactions (Schiff et al., 2018). They often operate independently, with no knowledge of
medications given by other primary care doctors or issued by other pharmacists. For instance, a
recent analysis by Schiff et al. (2018) discovered that nearly one in every five patients receiving
methadone treatment therapy obtained and filled prescriptions for other drugs, mainly from a
separate clinician or drugstore. Therefore, this project proposes a consolidated prescription
database which will be a beneficial tool for reducing incorrect prescriptions administration and
distribution, especially for prohibited medications that are susceptible to abuse or resale,
including opioid analgesics and benzodiazepines.
Goals and Objectives
Prescription opioid misuse affects many people, with approximately 117,000 people in
Minnesota abusing them each year (Treatment Solutions, 2021). Prescription drugs addicts visit
multiple doctors and pharmacies to obtain multiple prescriptions. Many users have mastered the
art of "doctor shopping" to get adequate drugs to fulfill their craving. Therefore, the proposed
prescription database's goal is to provide pharmacists and practitioners with past and current
patient prescription data on drug usage. The anticipated database will compile prescription data
relating to prescription drugs by various people across the state of Minnesota.
A database schema is the conceptual design of an entire or portion of a database system.
It can be represented visually and as a collection of procedures known as integrity constraints
that control a database (Khalfallah et al., 2018). These procedures are written in a data definition
language like SQL. A database schema, part of a data dictionary, describes how the objects that
comprise the database, such as stored procedures, views, tables, and other elements, interact
(Khalfallah et al., 2018). Developers often utilize database schemas in software design to lay out
how a database must be structured. A database schema is used to design the layout of a database
and will assist ensure that data inputs are structured consistently, that each record entry has a
unique primary key, and that no vital data is missing.
A database designer usually builds a database schema to assist developers whose
applications communicate with the database. Data modeling refers to the process of developing a
database schema. This stage would come after establishing a logical model in the three-schema
methodology to database architecture. Conceptual schemas are concerned with the knowledge
demands instead of the architecture of a database. There are two types of database schema (Imam
et al., 2018):
A logical database schema expresses the logical restrictions about the system's data. It
can define referential integrity, tables, and views.
A physical database schema defines how data is kept on a storage system in records and
A database schema describes which columns or relationships comprise the database and
the attributes featured on every table. As a result, schema design and entity-relationship diagram
are sometimes used interchangeably (Imam et al., 2018). The figure below illustrates the schema
(ERD) for the proposed prescription database.
Figure 1 : Prescription Database Schema
Processes Associated with the Business Rules
A business rule is a guideline, technique, or practice that governs an institution. It is vital
to identify and define the business logic when creating a database. These rules allow the database
designer to specify the interaction between the participation limitations and guidelines and the
appropriate data design. A database designer should comprehend procedures and the kind, role,
and scope of the data to create a database that will be useful to the organization. The prescription
database is subject to specific business rules listed below.
A physician can treat one or more patients.
A physician can order one or more prescriptions, including one or more drugs.
A pharmacy can only issue drugs prescribed by a physician.
A patient can only receive drugs issued by the pharmacy and prescribed by a physician.
Entity Relationship Diagram and Data Dictionary
A Data Dictionary collects labels, descriptions, and properties for data components
utilized or recorded in a database, computer system, or research project. It defines the
interpretations and goals of data items in the context of a project and gives direction on
understanding, accepted meanings, and layout (Buchanan et al., 2021). In addition, a Data
Dictionary provides metadata on data items. A Data Dictionary's metadata can help define the
scope and properties of data items and the criteria for their utilization and application. The table
below describes the data dictionary for the proposed prescription database.
NAME TYPE Size
PatientID INTEGER 10 Yes PK
PatientFirstName VARCHAR 30 Yes
PatientLastName VARCHAR 30 Yes
E NA Yes
Gender BOOLEAN 10 Yes
Address VARCHAR 100 Yes
TelephoneNumber VARCHAR 10 Yes
PhysicianID INTERGER 10 Yes PK
PhysicianFirstName VARCHAR 30 Yes
PhysicianLastName CHAR 30 Yes
Specialty VARCHAR 50 Yes
TelephoneNumber CHAR 10 Yes
PrescriptionID INTEGER 10 Yes PK
DrugDosage VARCHAR 20 Yes
IssueDate DATE/TIM NA Yes
DrugID INTEGER 10 Yes PK
DrugName VARCHAR 50 Yes
Manufacturer VARCHAR 50 Yes
PharmacyID INTEGER 10 Yes PK
PharmacyName VARCHAR 20 Yes
Address VARCHAR 100 Yes
FK – Foreign key
PK – Primary Key
Table 1 : Database Data Dictionary
Figure 2 below presents the entity-relationship diagram for the proposed prescription
Figure 2 : Entity Relationship Diagram
Project Limitations and Possible Extensions
One of the database's weaknesses is that people with drug addiction will go to other
jurisdictions to obtain prescriptions. Individuals have also been known to use various identity
documents to get prescription medication. Since the current prescription database does not
contain the other state hospital facilities that a person may visit or the numerous patient
identifiers that the person has, it does not enable uniform access to the patient's prescription
record. This database might be improved to include all healthcare facilities and drug treatment
centers in the United States.
The project has proposed a consolidated prescription database which will be a beneficial
tool for reducing incorrect prescriptions administration and distribution, especially for prohibited
medications that are susceptible to abuse or resale, including opioid analgesics and
benzodiazepines. The proposed database offers some protection against pharmaceutical mistakes,
treatment repetition, and drug reactions. Existing systems often operate independently, with no
knowledge of medications given by other primary care doctors or issued by other pharmacists.
Therefore, the proposed centralized prescription database will bridge this gap.
Buchanan, E. M., Crain, S. E., Cunningham, A. L., Johnson, H. R., Stash, H., Papadatou-Pastou,
M., Isager, P. M., Carlsson, R., & Aczel, B. (2021). Getting started creating data
dictionaries: How to create a shareable data set. Advances in Methods and Practices in
Psychological Science, 4(1), 251524592092800.
Imam, A. A., Basri, S., Ahmad, R., Watada, J., & González-Aparicio, M. T. (2018). Automatic
schema suggestion model for NoSQL document-stores databases. Journal of Big Data,
5(1), 46. https://doi.org/10.1186/s40537-018-0156-1
Khalfallah, N., Ouali, S., & Kraiem, N. (2018). Approach for managing variability in the
database schema. Journal of Asian Scientific Research, 8(6), 221–236.
Schiff, G., Mirica, M. M., Dhavle, A. A., Galanter, W. L., Lambert, B., & Wright, A. (2018). A
prescription for enhancing electronic prescribing safety. Health Affairs, 37(11),
Treatment Solutions. (2021). Patient prescription database. Treatment Solutions.