Clinic Database management system
Designed, developed, and implemented a relational database in SQL to keep track of patient’s information, as well as day-to-day operational procedures such as appointment scheduling and billing.
The database created is for a small-sized dentist office, with 3 staff members: a dentist and 2 hygienist specialists.
PArt 1 - Tables creation
Five tables were created: patient, insurance, appointment, staff, and visit. Below find a detailed representation of each table and the information they hold.
PArt 2 - Views
Five views were created to store important information from both the patient and staff tables. In specific:
patient_bill: shows the patients’ information for all unpaid visits and the coverage status to reflect whether the payment needs to be due by the patient or billed to the insurance;
patient_visit: shows all patients that require a follow up visit, as well as other information pertinent to their visit such as the reason, the risk, and the medication that was prescribed;
Simon_schedule: shows all appointments scheduled for the dentist, ordered by date and time in order for him to see what’s in their agenda;
Linda_schedule: shows all appointments scheduled for the hygiene specialist, ordered by date and time in order for her to see what’s in their agenda;
Chloe_schedule: shows all appointments scheduled for the hygiene specialist, ordered by date and time in order for her to see what’s in their agenda.
PArt 3 - Procedures
Next, six different procedures were created for the user to easily update or view important information within the database. In specific:
insert_new_patient: asks the user to input all patient’s information in order to add a new record to the patient’s table;
delete_patient: asks the user to insert a patient ID in order to update the patient’s table and remove the record associated with them;
update_patient_address: asks the user to insert the patient ID and the new address in order to update record in the patient’s table;
new_appointment: asks the user to insert all appointment’s information including date, time, and reason for the visit. If time matches an already scheduled appointment for that same date, the user won’t be allowed to insert the new record in the appointment’s table;
update_payment: asks the user to insert the visit ID and will update payment status from “Unpaid” to “Paid”;
search_visit: asks the user to insert a patient’s first and last name, and will return a list of all visits pertinent to them, with detailed information for each.
Finally, throughout this project a basic database for a dentist office was implemented. As of now, there are certain limitations related to updating the size of the records contained, as it would require the user to manually enter each information. However, this could be fixed by simply creating more advanced procedures.
Full SQL code can be found on my GitHub profile linked below.