Vaccination Scheduler

Completeted June 2023

Technologies Used: Python , SQL Server , Azure

I created a full-stack application to schedule vaccination appointments, with the goal of gaining hands-on experience developing an application from start to finish and integrating a backend database.

The main program is written in Python, taking user input via text commands. User data is stored in an SQL Server database, and queries are executed using the pymssql library. The project is configured to connect to a database hosted on Azure, with future plans to add support for locally hosted solutions.

Upon startup, users are presented with a list of available commands:

Two types of users are supported: Caregivers and Patients. Caregivers are medical professionals who administer vaccinations to Patients. These user types are represented as custom classes.

New users must first create an account, while returning users can log back in. User credentials are stored securely using SHA256 encryption.

Caregivers are able to upload their schedule for appointments and update available vaccine doses. Patients are able to schedule appointments, provided that a caregiver is available and there is sufficient vaccine inventory. Patients are also able to cancel a previously-scheduled appointment, freeing up the timeslot for another user. All users can view appointment availability on a given date and logout.

Each command communicates with the SQL Server DB with a custom SQL query. Data is updated in real-time, allowing for multiple simultaneous users. A ConnectionManager class handles initializing and closing connections.

I designed the database schema from scratch, with a focus on storage optimization. A query is provided here to create the database from scratch in SQL Server.

CREATE TABLE Vaccines (
    Name VARCHAR(255) PRIMARY KEY,
    Doses INT CHECK (Doses >=0)
);

CREATE TABLE Caregivers (
    Username VARCHAR(255) PRIMARY KEY,
    Salt BINARY(16),
    Hash BINARY(16)
);

CREATE TABLE Patients (
    Username VARCHAR(255) PRIMARY KEY,
    Salt BINARY(16),
    Hash BINARY(16)
);

CREATE TABLE Availabilities (
    CaregiverID VARCHAR(255) REFERENCES Caregivers NOT NULL,
    Date DATE NOT NULL,
    AppointmentID VARCHAR(36) PRIMARY KEY,
    CONSTRAINT scheduled UNIQUE(CaregiverID, DATE)
);

CREATE TABLE Appointments (
    AppointmentID VARCHAR(36) REFERENCES Availabilities NOT NULL PRIMARY KEY,
    VaccineType VARCHAR(255) REFERENCES Vaccines,
    PatientID VARCHAR(255) REFERENCES Patients,
);