Patient Database System

Role: Database Developer | Tools: Microsoft Azure, SQL, Java (JDBC)

Quick Links: Github Repo

Executive Summary:

Designed and implemented a database management system for the Patient Assistance Network (PAN), a non-profit supporting patients through personalized care and resources. The solution streamlined operations by tracking clients, personnel, donors, and volunteers, while enabling advanced reporting through SQL queries and a Java-based application.

Business Problem:

PAN needed a scalable and centralized system to manage diverse data from patient profiles and volunteer information to donations and expenses, and they needed to generate actionable reports for operational and strategic decisions. Key questions included:

  1. How can PAN efficiently manage and query client, personnel, and donor data?

  2. What reports will provide actionable insights, such as total expenses by employee or identifying donors who are also employees?

  3. How can the system be designed for scalability to meet future growth?

Approach:

  • Database Design: Built normalized tables with constraints and indexing for performance, supporting entities like clients, employees, volunteers, donors, and team memberships.

  • SQL Development: Created 15+ stored procedures and queries for operations including adding personnel, calculating total expenses by employee, and generating cross-role reports.

  • Application Integration: Developed a Java application with a user-friendly menu, enabling query execution, data management, and import/export functions via JDBC to Azure SQL Database.

  • Project Management: Broke down development into milestones with time estimates to ensure on-time delivery, testing, and debugging.

Entity-Relationship diagram outlining the database structure. Designed for scalability and optimized query performance.

Entity-Relationship diagram outlining the database structure. Designed for scalability and optimized query performance.

Example SQL stored procedure used to enter volunteering hours.

Example SQL stored procedure used to enter volunteering hours.

Java-based user interface allowing staff to run SQL queries, manage personnel records, and generate custom reports without directly accessing the database.

Java-based user interface allowing staff to run SQL queries, manage personnel records, and generate custom reports without directly accessing the database.

Findings & Outcomes:

  • Operational Efficiency: Allowed quick retrieval and updates of key data, reducing manual record-keeping.

  • Advanced Reporting: Enabled complex queries (identifying dual-role individuals, donor trends, expense summaries).

  • User Accessibility: Delivered a Java-based interface to make database functions accessible to non-technical staff.

Impact:

  • Improved PAN’s ability to track and analyze critical organizational metrics in real time.

  • Created a scalable system designed for future data growth without sacrificing usability.

  • Demonstrated the value of integrated tech solutions for non-profits with limited IT resources.