Designing a Secure Healthcare Database for Efficient Management

Data Management Project Trevor Brown Data Management Professor Pratt October 8 th , 2023

The database that I am going to design is a database for a healthcare facility. The objective of the database is to help the information flow more efficiently and to make the lives easier for the healthcare professionals. There has always been a problem of inefficient data management within the healthcare profession. They rely on some paper records and some digital records which leads to errors, delays, and can reduce the quality of care that their patients receive. The main problem this database is going to solve is the lack of a unified, secure, and efficient database to manage patient records, appointments, prescriptions, and medical history information. There will be a few constraints that this database will have to have so everything is legal and running smoothly. One of the biggest challenges will be with data privacy regulations specifically HIPAA which has strict constraints on the handling and the storage of everyone’s medical information. To help combat that we are going to put constraints on who can all access certain information on the patients at the healthcare facility. To do this we are only going to allow the nurses and the doctors assigned to that patient to be able to access any and all medical information related to that patient. So, if there was a breach of information, we would know out of a handful of people who it could possibly be and it limits the amount of people who can even see that information. We also have to put constraints on the amount of medication that can be prescribed to patients so that patients do not develop addictions to drugs, and this can also prevent future lawsuits against the facility. The overall objective of designing this database is to provide the patients with the best care possible. We also want to design something that will make the transfer of information faster and more efficient so that the healthcare professionals will be able to provide faster and better care. We also want to try and eliminate all errors of the patients records so we can give the best

care possible. We also want to ensure complete data security and privacy to stay up to date with the privacy regulations and to also keep our patient’s information confidential. We also want to make the appointment scheduling fast and easy to help reduce wait times and increase patient satisfaction. We are also going to streamline the billing and payment process making it easier for the patients and the insurance companies reducing the number of financial errors which in the long run can improve the profits. The scope of this database is to design a comprehensive healthcare information system. This will include designing a database that transfers patients’ medical information from the different facilities and to all the approved doctors for that patient. It will also be able to send the information that needs to be sent to insurance companies so that they will then be able to pay for their client’s care. The design of this system is to make thing easier and more efficient for both the patients and the healthcare professionals. The boundaries of the system will be able to help patients with scheduling all the way to get information to insurance companies. The entities that will be in the database are Patient, Medical Staff, Appointments, Medical Records, Prescriptions, Insurance Companies, and Billing and Payments. With these entities and then the attributes that will go in them will be able to have an efficient and well run database made. Project Schedule and cost: Project Initiation (1-2 weeks):  Define project scope and objectives.  Identify stakeholders and establish a project team.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

 Set up project management tools and communication channels. Requirements Gathering and Analysis (2-4 weeks):  Gather user requirements, data requirements, and reporting needs.  Conduct interviews and workshops with end-users. Database Design (4-6 weeks):  Design the database schema, including entity-relationship diagrams (ERD).  Normalize the database to 3rd normal form.  Choose the DBMS and database technology stack.  Define data validation rules, constraints, and indexing strategies. Development (6-10 weeks):  Create the database structure (CREATE TABLE statements).  Develop the user interface for data entry and reporting.  Implement security features.  Test individual database components. Data Population (2-4 weeks):  Prepare and cleanse existing data  Import data into the database.  Validate and reconcile imported data.

 Perform data quality checks. Project costs can vary significantly depending on factors like team size, technology choices, and project duration. Here are some cost components we consider: Personnel Costs: Including salaries for developers, database administrators, project managers, and other team members. Software and Licensing: Costs associated with database management software, development tools, and third-party libraries or frameworks. Hardware and Infrastructure: Costs for servers, storage, and networking equipment, whether on-premises or cloud-based. Data Cleansing and Migration: Costs for data preparation, cleansing, and migration activities. Training: Costs for training end-users and staff on the new system. Project Management: Costs associated with project management tools, communication, and planning. Quality Assurance and Testing: Costs for testing tools, test environments, and QA activities. Documentation and Compliance: Costs for preparing documentation and ensuring compliance with regulatory requirements. Ongoing Maintenance: Consider ongoing operational costs for maintaining and supporting the database.

After taking all these factors into consideration we estimate the project to take us 14-28 weeks to have the DBMS system build and populated with data and we estimate this to cost the healthcare facility around $20,000.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Patients Table: This table contains the information of all the patients at the healthcare facility Primary Key Attribute Data Type Size Description Y PatientID Integer - A unique number identifier that each patient has PatientFName Varchar 25 The first name of the patient PatientLName Varchar 25 The last name of the patient DateOfBirth Date - The date the patient was born PhoneNumber Integer - The number to contact the patient at InsuranceCompanyID Integer - The unique number to Identify each insurance company StaffID Integer - The unique number to Identify each person on the medical staff Medical Staff Table: This Table contains the information of doctors and nurses that work at the healthcare Facility Primary Key Attribute Data Type Size Description Y StaffID Integer - The unique number to identify each staff member StaffFName Varchar 25 The first name of the staff member StaffLName Varchar 25 The last name of the staff member JobTitle Varchar 25 This describes the job which the person does at the facility. You are either a Doctor, Nurse.

Medical Records Table: This table contains the medical history and past appointments for the patients at the facility Primary Key Attribute Data Type Size Description Y RecordID Integer - The unique number to identify the medical history for each patient PatientID Integer - The unique number given to each patient to identify them VisitDate Date - This is going to tell us every day the patients came in for treatment MedicalHistory Varchar 255 This will tell us all the medical history information that the patient has PrescriptionID Integer - This is the unique identifier that will tell us what medications the patient has been taking Appointments Table: This table contains the information for patients appointments and also tells you what doctor you will be seeing. Primary Key Attribute Data Type Size Description Y AppiontmentID Integer - This is the unique identifying number giving to every appointment PatientID Integer - This is the unique identifier for each patient and will link them to their

appointments StaffID Integer - This is the unique identifier for the medical staff and this will link them to the appointments DateAndDate DateTime - This will tell you what day and what time the appointment is Prescriptions Table: This table contains the information of all the medication and dosage that each patient is taking Primary Key Attribute Data Type Size Description Y PrescriptionID Integer - The unique identifier for each prescription PatientID Integer - The unique Identifier for each patient and will link them to their prescriptions MedicationName Varchar 50 The name of the mediation that was prescribed by the doctor Dosage Varchar 50 This will give the patients instruction on how much to take and how often you should take the medication StaffID Integer - This is the unique identifier for the medical staff and this will link what doctor prescribed what medication and to who

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Insurance Company Table: This table contain on the different insurance companies, and will tell you how to contact them and bills and claims are sent to each company Primary Key Attribute Data Type Size Description Y InsuranceCompanyID Integer - This is the unique identifier for each insurance company CompanyName Varchar 50 The name of the insurance company PhoneNumber Integer 10 The phone number to contact the insurance company BillID Integer - The unique Identifier for each bill that is sent out from the healthcare facility, and this will be linked to which insurance company it was sent to Claims Varchar 255 This will tell us the claim that is put in by the insurance company and tell us how much of the bill they are going to play StreetAddress VarChar 50 This is the street address of the insurance company City Varchar 25 This is the city the insurance company is in State Varchar 25 This is the state the insurance

company is in ZipCode Char 10 The zip code the insurance company is in Billing Table: This table will contain all the information about the bills sent to the patients and the insurance companies and will keep track of the balances Primary Key Attribute Data Type Size Descriptions Y BillID Integer - The unique identifier to differentiate each bill PatientID Integer - The unique identifier for each patient and this will link the patient to their bills BillDate Date - The date of when the bill is sent out InsuranceCompanyID Integer - The unique identifier for each insurance company and this will link the company with the bills their clients were billed for OutstandingBalance Numeric (7,2) This number will tell how much money the patient still owes on all their bills Charges Numeric (7,2) This will show you how much you were charged by the facility CREATE TABLE Patients ( PatientID INT PRIMARY KEY ,

PatientFName VARCHAR(25 , PatientLName VARCHAR(25) , DateOfBirth DATE , PhoneNumber INT , InsuranceCompanyID INT , StaffID INT , FOREIGN KEY (InsuranceCompanyID) REFERENCES Insurance Companies(InsuranceCompanyID) , FOREIGN KEY (StaffID) REFERENCES Medical Staff(StaffID) ); CREATE TABLE Insurance Companies ( InsuranceCompanyID INT PRIMARY KEY , CompanyName VARCHAR(50) , PhoneNumber INT , BillID INT , Claims VARCHAR(255) , StreetAddress VARCHAR(100) , City VARCHAR(25) , State VARCHAR(25) , ZipCode INT , FOREIGN KEY (BillID) REFERENCES Billing(BillID) ); CREATE TABLE Appointments ( AppiontmentID INT PRIMARY KEY , PatientID INT , StaffID INT , DateAndTime DATETIME ,

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

FOREIGN KEY (PatientID) REFERENCES Patients(PatientID) , FOREIGN KEY (StaffID) REFERENCES Medical Staff(StaffID) ); CREATE TABLE Medical Records ( RecordID INT PRIMARY KEY , PatientID INT , VisitDate DATE , MedicalHistory VARCHAR(255) , PrescriptionID INT , FOREIGN KEY (PatientID) REFERENCES Patients(PatientID) , FOREIGN KEY (PrescriptionID) REFERENCES Prescriptions(PrescriptionID) ); CREATE TABLE Billing ( BillID INT PRIMARY KEY , BillDate DATE , InsuranceCompanyID INT , Charges MONEY , OutstandingBalance MONEY , FOREIGN KEY (InsuranceCompanyID) REFERENCES Insurance Companies(InsuranceCompanyID) ); CREATE TABLE Medical Staff ( StaffID INT PRIMARY KEY , StaffFName VARCHAR(25) , StaffLName VARCHAR(25) , JobTitle VARCHAR(25)

); CREATE TABLE Prescriptions ( PrescriptionID INT PRIMARY KEY , PatientID INT , MedicationName VARCHAR(100) , Dosage VARCHAR(100) , StaffID INT , FOREIGN KEY (PatientID) REFERENCES Patients(PatientID) , FOREIGN KEY (StaffID) REFERENCES Medical Staff(StaffID) ); INSERT INTO Medical Staff VALUES (1, ‘John’, ‘Stark’, ‘doctor’), (2, ‘Todd’, ‘Garcia’, ‘nurse’), (3, ‘Kelly’, ‘Hardin’, ‘nurse’), (4, ‘Tianna’, ‘Schmidt’, ‘receptionist’), (5, ‘Ben’, ‘fisher’, ‘nurse’), (6, ‘Hazel’, ‘Vaughan’, ‘doctor’), (7, ‘Dean’, ‘Hooper’, ‘nurse’), (8, ‘Colby’, ‘Massey’, ‘doctor’), (9, ‘Kelsey’, ‘Seifring’, ‘receptionist’), (10, ‘Robert’, ‘Hyndman’, ‘nurse’); SELECT Medical Staff.FirstName, Medical Staff.LastName, COUNT(Appointments.AppointmentID) AS TotalAppointments FROM Medical Staff LEFT JOIN Appointments ON Medical Staff.StaffID = Appointments.StaffID GROUP BY Medical Staff.StaffID, StaffFName, StaffLName;

SELECT Patients.PatientFName, Patients.PatientLName, Medical Records.MedicalHistory From Patients INNER JOIN Medical Records ON Patients.PatientID = MedicalRecords.PatientID WHERE Medical Records.MedicalHistory = ‘Diabetes’; The database will be centralized. Centralized databases are suitable for healthcare systems, ensuring data consistency and easier management. We will use a hybrid approach, combining both cloud computing and private servers. This hybrid approach offers flexibility and security. AWS will be the cloud provider for hosting certain components of the system. AWS offers scalability, reliability, and a wide range of database services. The DBMS software chosen for this project is Microsoft SQL Server. SQL Server is known for its wide variety of security features and is compatible with the healthcare industry. The database hardware will consist of high-performance servers with sufficient CPU, RAM, and storage resources to handle the database workload efficiently. Backup And Recovery: Types of Backups: Full backups will be taken weekly, and incremental backups will be done daily Individual Transactions: In case of accidental data loss or errors, individual transactions can be recovered using transaction logs and point-in-time recovery Database Recovery: In the event we need a full database recovery will be performed using the latest full backup and incremental backups Regular testing of backup and recovery procedures will be conducted to ensure their effectiveness and reliability. Access controls will be in place to restrict who can perform

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

recovery operations, and detailed documentation of procedures will be maintained to facilitate the recovery process.

Data Management Project

Related Documents