MySQL Project: Juls Walk-in Clinic

This project simulates a real-life walk-in clinic management system using MySQL. The goal was to design a scalable, normalized relational database to efficiently handle healthcare operations — such as appointments, lab results, payments, and staff management.

I designed the Conceptual Data Model, implemented the Database Schema, inserted Sample Data, created the ERD, and wrote Optimized SQL Queries to perform meaningful operations and extract valuable insights. The system is also structured to support integration with tools like Power BI or Tabular for advanced reporting and data visualization.

It manages both walk-in and enrolled patients, tracks appointment statuses, records patient vitals and test results, and supports internal workflows like staff scheduling and salary calculation — helping the clinic run smoothly and make informed decisions. 👉 For complete details regarding the project, click here to download.

This is the Conceptual Data Model I designed, illustrating the relationships between tables through primary and foreign keys. It clearly represents the type of associations — including one-to-one, one-to-many, and many-to-many — providing a comprehensive view of the database structure as shown below.

Using the above Conceptual Data Model, I developed a fully normalized and relational Database Schema for the Juls Walk-in Clinic. It supports data integrity through the use of primary and foreign keys, and models real-world operations like staff scheduling, salary calculation, and appointment tracking. This schema includes essential entities like patients, doctors, appointments, visits, vitals, lab tests, payments, and various employee roles such as nurses, secretaries, and admin staff. This schema lays the foundation for executing advanced SQL queries and integrating with visualization tools for healthcare insights. You can have a look at the complete schema below to explore how each table connects and supports the system.

create database juls_walkin_clinic;
use juls_walkin_clinic;

-- Creating Patient Table
create table patients(
    patient_id int primary key auto_increment,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    dob date not null,
    contact varchar(15) not null unique,
    email varchar(100) unique,
    gender enum('name', 'female', 'other') not null
);

-- Creating Doctors Table
create table doctors(
    doctor_id int primary key auto_increment,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    contact varchar(15) not null unique,
    email varchar(100) unique,
    specialization varchar(100) not null,
    shift varchar(100) not null
);

-- Creating Nurses Table
create table nurses(
    nurse_id int primary key auto_increment,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    contact varchar(15) not null unique,
    email varchar(100) unique,
    employee_id int not null,
    foreign key (employee_id) references work_hours(employee_id),
    foreign key (employee_id) references shift_schedule(employee_id)
);

-- Creating Appointments Table
create table appointments(
    appointment_id int primary key auto_increment,
    patient_id int not null,
    doctor_id int not null,
    appointment_date datetime not null,
    foreign key (patient_id) references patients(patient_id),
    foreign key (doctor_id) references doctors(doctor_id),
    appointment_status enum('booked', 'cancelled', 'arrived', 'checked-in', 'checked-out', 'left without treatment')
    not null
);

-- Creating Visits_Recode Table
create table visits_recode(
    visit_id int primary key auto_increment,
    appointment_id int not null,
    patient_id int not null,
    doctor_id int not null,
    visit_date datetime not null,
    foreign key (patient_id) references patients(patient_id),
    foreign key (doctor_id) references doctors(doctor_id),
    foreign key (appointment_id) references appointments(appointment_id)
);

-- Creating Vitals Table
create table vitals(
    vitals_id int primary key auto_increment,
    nurse_id int not null,
    patient_id int not null,
    blood_pressure varchar(20),
    temperature decimal(5, 2),
    height decimal(5, 2),
    weight decimal(5, 2),
    foreign key (nurse_id) references nurses(nurse_id),
    foreign key (patient_id) references patients(patient_id)
);

-- Creating Lab-Tests Table
create table lab_tests(
    test_id int primary key auto_increment,
    doctor_id int not null,
    test_type varchar(100) not null,
    test_results text,
    foreign key (doctor_id) references doctors(doctor_id)
);

-- Creating Payments Table
create table payments(
    payment_id int primary key auto_increment,
    patient_id int not null,
    payment_date datetime not null,
    payment_amount decimal(10, 2) not null,
    payment_method enum('cash', 'credit card', 'debit card', 'insurance') not null,
    payment_status enum('paid', 'pending', 'failed') not null,
    foreign key (patient_id) references patients(patient_id)
);

-- Creating Shift_Schedule Table
create table shift_schedule(
    employee_id int primary key,
    shift_start_time datetime not null,
    shift_end_time datetime not null,
    shift_day enum('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday') not null
);

-- Creating Work_Hours Table
create table work_hours(
    employee_id int primary key,
    total_hours decimal(5, 2) not null,
    pay_per_hour decimal(10, 2) not null,
    week_start_date date not null,
    week_end_date date not null
);

-- Creating Test_Results Table
create table test_results(
    test_id int primary key,
    test_result text not null,
    test_date date not null,
    result_date date not null,
    foreign key (test_id) references lab_tests(test_id)
);

-- Creating Secretaries Table
create table secretaries(
    employee_id int primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    phone varchar(15) not null,
    email varchar(100) unique,
    foreign key (employee_id) references work_hours(employee_id),
    foreign key (employee_id) references shift_schedule(employee_id)
);

-- Creating Admin_Staff Table
create table admin_staff(
    employee_id int primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    phone varchar(15) not null,
    email varchar(100) unique,
    foreign key (employee_id) references work_hours(employee_id),
    foreign key (employee_id) references shift_schedule(employee_id)
);

Using the structure of the schema provided above, I have inserted sample data, where primary keys are set to auto-increment, so there' s no need to manually assign them as they are automatically generated as unique for each record. You can view the sample data below, which represents only a portion of the data inserted into the database for demonstration purposes.

-- Inserting data in patient table
insert into juls_walkin_clinic.patients (first_name, last_name, dob, contact, email, gender) values
('John', 'Doe', '1985-02-19', '4161234567', 'john.doe@gmail.com', 'Male'),
('Jane', 'Smith', '1980-06-18', '4162345678', 'jane.smith@gmail.com', 'Female'),
('Emily', 'Johnson', '1992-03-25', '4163456789', 'emily.johnson@gmail.com', 'Female'),
('Michael', 'Brown', '1995-09-30', '4164567890', 'michael.brown@gmail.com', 'Male'),
('David', 'Williams', '1987-08-15', '4165678901', 'david.williams@gmail.com', 'Male'),
('Sarah', 'Davis', '1986-11-11', '4166789012', 'sarah.davis@gmail.com', 'Female'),
('James', 'Miller', '1993-12-01', '4167890123', 'james.miller@gmail.com', 'Male'),
('Sophia', 'Garcia', '1988-07-07', '4168901234', 'sophia.garcia@gmail.com', 'Female'),
('Olivia', 'Martinez', '1990-04-14', '4169012345', 'olivia.martinez@gmail.com', 'Female'),
('Ethan', 'Rodriguez', '2000-05-10', '4160123456', 'ethan.rodriguez@gmail.com', 'Male');

-- Inserting data in secretaries table
insert into juls_walkin_clinic.secretaries (employee_id, first_name, last_name, phone, email) values
(106, 'Linda', 'Park', '4370001001', 'linda.park@clinic.com'),
(107, 'Lois', 'Lane', '4370001002', 'lois.lane@clinic.com');

Based on the data and schema, I identified and resolved several issues by performing queries that demonstrate my proficiency in database management. I applied various operations like ALTER, UPDATE,DELETE, and INSERT to enhance data integrity. This included adding missing columns, removing unnecessary data, and transforming data formats, such as splitting a combined date-time column into separate date and time columns. Additionally, I added new doctors and staff members to the system. Below are examples of these queries for you to review.

-- Performing ALTER, UPDATE, DELETE, and INSERT

-- Altering table Patient (Adding Address Column to send lab-results throuogh Mail)
alter table juls_walkin_clinic.patients
add address varchar(100);

-- Altering payment_date in payments
alter table payments
add column payment_only_date date,
add column payment_only_time time;
-- Now Updating the columns
update payments
set 
    payment_only_date = date(payment_date),
    payment_only_time = time(payment_date);

-- Updating Contact in nurse table
update juls_walkin_clinic.nurses
set contact = '4370001111'
where nurse_id = 1;

-- Deleting an appoinment from Appoinment Table where appoinment id is foreign key in visits record
delete from visits_recode
where appointment_id in (
    select appointment_id from appointments where appointment_status = 'Cancelled'
);
delete from appointments
where appointment_status = 'Cancelled';

-- Inserting A new Doctor to the Doctors Table
insert into juls_walkin_clinic.doctors (first_name, last_name, specialization, contact, email, shift)
values ('Mark', 'Addision', 'Neurology', '6471117777', 'stephen.strange@clinic.com', 'Afternoon');

After designing the schema, inserting data, and executing various modification queries, I created an EER diagram that visualizes the relationships between tables. This diagram not only highlights the number of rows in each table but also provides a clear representation of the entities and their interconnections. The EER diagram serves as a comprehensive guide to understanding the database structure, ensuring data integrity and fostering efficient query formulation. It is an essential tool for illustrating complex relationships and optimizing future database operations. Below, you can view the EER diagram for reference.

After completing all the necessary steps, I am now showcasing a selection of queries that are commonly used in real-world industry scenarios. These queries represent tasks frequently encountered when working with actual data in various fields. Some of the more complex queries include the use of joins,union, views, and procedure creation.

UNION is used to combine the results of two or more select queries into a single result set, ensuring that only distinct rows are included in the final output.

JOINs are essential for combining rows from two or more tables based on a related column. There are different types of joins:

(Inner) Join - returns rows when there is a match in both tables.
Left (Outer) Join - returns all rows from the left table and the matched rows from the right table, or NULL if there' s no match.
Right (Outer) Join - works similarly to Left (Outer) Join but returns all rows from the right table and the matched rows from the left table.
Full (Outer) Join - returns rows when there is a match in either left or right table.

These operations allow for the extraction and manipulation of data across multiple tables efficiently. Below, you can review examples of joins and union queries along with their output.

-- Creating Queries for joins and union:

-- List all patients with the names of the nurses who took their vitals:
select p.first_name as patient_first_name, n.first_name as nurse_first_name, v.blood_pressure
from juls_walkin_clinic.vitals v
inner join juls_walkin_clinic.patients p on v.patient_id = p.patient_id
inner join juls_walkin_clinic.nurses n on v.nurse_id = n.nurse_id;

-- Selecting full name , date, time and payment amount of patients:
select concat(p.first_name, " ", p.last_name) as 'Name',
round(pay.payment_amount,0) as 'Amount',
date(pay.payment_date) as 'Date',
time(pay.payment_date) as 'Time'
from juls_walkin_clinic.patients p
left join juls_walkin_clinic.payments pay on p.patient_id = pay.patient_id;

-- Selecting all employees full name and employee id using union:
select nur.employee_id, concat(nur.first_name, ", ", nur.last_name) as eployees_full_name
from juls_walkin_clinic.nurses as nur
union
select sec.employee_id, concat(sec.first_name, ", ", sec.last_name)
from juls_walkin_clinic.secretaries as sec
union
select adm.employee_id, concat(adm.first_name, ", ", adm.last_name)
from juls_walkin_clinic.admin_staff as adm;

Queries Result:

In my project, I utilized views and stored procedures to simplify and automate tasks, making the database operations more efficient and streamlined, which are crucial tools for working with complex data in real-world applications.

View: A view in SQL is used to present data in a specific way, without modifying theunderlying data. In my project, I created a view called PatientVitals, which combines patient information and their vital signs (like blood pressure and temperature) from the patients and vitals tables. This view makes it easier to access relevant patient data quickly, and it can be reused for different queries. For example, the PatientVitals view simplifies retrieving patient vitals data with a single query.
Procedure: A stored procedure is a set of SQL statements that can be executed together as a unit. I created two procedures in my project:
1. GetHighPayments to retrieve payments that are above the average payment made by patients.
2. GetCertainAmount to get payments above a certain amount, allowing for more flexible and reusable queries.
These procedures automate the querying process, improving efficiency and reducing repetitive tasks in the system.

Both views and procedures provide powerful ways to organize and optimize database operations, improving efficiency and scalability. Below are examples of these queries and their corresponding outputs.

-- Creating view and procedure

-- Creating a view of patients with their lood pressure and temperature:
create view PatientVitals as
select p.patient_id, p.last_name, v.blood_pressure, v.temperature
from juls_walkin_clinic.patients as p
join juls_walkin_clinic.vitals as v
on p.patient_id = v.patient_id;
-- Checking view
select *
from juls_walkin_clinic.patientvitals;

-- Create a procedure to get payments above average payments done by patients:
delimiter //
create procedure GetHighPayments()
begin
    select payment_id, payment_amount, payment_method from juls_walkin_clinic.payments
    where payment_amount > (select avg(payment_amount) from juls_walkin_clinic.payments);
end //
delimiter ;
call GetHighPayments();

-- Create a procedure to get payments above a certain amount:
delimiter $$
create procedure GetCertainAmount(in min_amount decimal(10,2))
begin
    select patient_id, payment_amount, payment_status from juls_walkin_clinic.payments
    where payment_amount > min_amount;
end $$
delimiter ;
call GetCertainAmount(200);

Result:

In conclusion, this project successfully demonstrates the design and implementation of a relational database management system for a walk-in clinic. The project included:

Designing the database schema based on the conceptual data model.
Inserting sample data for various entities like patients, doctors, appointments, and payments.
Writing optimized SQL queries for data manipulation, including INSERT, UPDATE, DELETE, and complex queries using JOIN and UNION.
Creating useful views and stored procedures to streamline querying and automate repetitive tasks.
Visualizing the relationships between tables using an EER diagram to better understand the structure.

This project helped me strengthen my database design skills and apply advanced SQL techniques like joins, subqueries, and aggregation. It also gave me exposure to real-world use cases like healthcare data systems and made me confident in integrating SQL. The schema is extensible and designed to support additional features like appointment reminders, payment histories, and predictive analytics through future integrations.

All the resources, including the full schema, queries, and additional documentation, are available on my GitHub repository. You can access the project and explore further by clicking here.

Go to Data Cleaning Project in MySQL →