A comprehensive database design project for a multi-branch hospital network system. Built with MySQL, covering ER modeling, normalization to BCNF, DDL, DML, and DQL — addressing real-world challenges in healthcare data integration.

In today’s healthcare landscape, patient information is often fragmented across multiple hospitals, clinics, and departments. When a patient visits different branches of a hospital network, their medical history, prescriptions, and test results may not follow them — leading to gaps in care, medication errors, and duplicated tests.

This project was developed as part of my Master of Data Analytics program at the University of Niagara Falls, where my team and I designed a centralized database system for multi-branch hospital networks. The goal was to create a unified data platform that enhances continuity of care, reduces administrative burden, and provides actionable insights for healthcare administrators.

While this is a hypothetical system built for academic purposes, the problem it addresses is very real. Ontario hospitals are actively investing in network-wide electronic patient record systems — including Unity Health Toronto’s EPR rollout, the Central East Ontario Hospital Partnership’s Clinical Information System, and the Atlas Alliance’s Epic digital health records platform serving 16 hospitals across Eastern Ontario.

Hospital networks face significant challenges in maintaining continuity of care due to fragmented data management systems. When patient information is siloed within individual hospital branches, it leads to:

• Incomplete medical histories when patients seek treatment at different locations
• Medication errors due to lack of unified prescription histories
• Administrative inefficiencies — staff waste time retrieving and reconciling data from disparate systems
• Duplicated tests and procedures, increasing healthcare costs and delaying treatment

These issues are not hypothetical. They are documented challenges that Ontario’s healthcare system is actively working to address through provincewide digital health initiatives.

Project Objectives

Our database design aimed to achieve four key objectives:

1. Centralized Data Management — Unify patient records, medical histories, and prescriptions across all hospital branches, eliminating duplication and ensuring consistency.
2. Improved Interdepartmental Communication — Facilitate seamless data sharing between departments and branches to reduce redundancies and improve care coordination.
3. Enhanced Resource Utilization — Provide hospital administrators with data-driven insights into staff, department, and hospital performance for optimized resource allocation.
4. Scalable Architecture — Design a system that can be expanded to include predictive analytics, supporting proactive decision-making and trend analysis.

Tools Used

• MySQL — Chosen for its reliability, open-source nature, and widespread use in healthcare applications.
• Microsoft Visio — Used for ERD visualization.
• Google Docs — For collaborative documentation.
• PowerPoint — For the final project presentation.

Entity-Relationship Diagram (ERD)

Figure 1: HealthcareDB Entity-Relationship Diagram showing 10 normalized tables and their relationships.

The database schema consists of 10 interconnected tables, designed to capture the complete patient care journey across a multi-branch hospital network:

Table	Description
Hospital	Stores information about each hospital in the network
Department	Defines hospital departments and their specializations
Doctor	Stores information about healthcare providers
Staff	Stores information about non-doctor staff members
Patient	Stores patient demographic and contact information
Appointment	Contains information on patient appointments
Prescription	Details prescriptions provided during consultations
Billing	Tracks patient billing and payment details
Medical Record	Stores patient medical history, diagnoses, and treatments
Insurance	Manages patient insurance coverage details

Normalization Analysis

The database was designed through all stages of normalization to ensure data integrity and eliminate redundancy:

• First Normal Form (1NF) — Eliminated repeating groups by ensuring each attribute contains atomic values. Each table has a primary key, and all columns contain single, indivisible values.
• Second Normal Form (2NF) — Removed partial dependencies. All non-key attributes are fully dependent on the entire primary key. For example, in the department table, attributes like department_name and services_offered depend fully on department_id, not just on hospital_id.
• Third Normal Form (3NF) — Eliminated transitive dependencies. No non-key attribute depends on another non-key attribute. For instance, hospital_name and hospital_address are stored in the hospital table, not duplicated in the department table.
• Boyce-Codd Normal Form (BCNF) — Ensured that for every functional dependency, the determinant is a candidate key. This final step guarantees the highest level of data integrity for our schema.

1. Referential Integrity with Foreign Keys

Every relationship between tables is enforced through foreign key constraints with ON DELETE CASCADE rules, ensuring data consistency when records are removed.

2. Indexing for Performance

Strategic indexes were created on frequently queried columns (such as patient_id, doctor_id, appointment_date) to optimize query performance in a production environment.

3. Data Types Aligned with Business Requirements

• ENUM types for standardized values (e.g., appointment status: Scheduled, Completed, Cancelled)
• BOOLEAN flags for binary attributes (e.g., emergency_services, is_active)
• DECIMAL(10,2) for precise billing amounts
• DATE and TIME types for accurate temporal data

4. ML-Ready Query Design

The schema includes queries specifically designed to prepare datasets for machine learning applications — enabling future predictive analytics such as patient read mission risk, appointment no-show prediction, and resource demand forecasting.

Business Impact & Benefits

A centralized healthcare database like the one we designed delivers tangible benefits:

• Improved Continuity of Care — Patients receive consistent, coordinated treatment regardless of which hospital branch they visit.
• Reduced Administrative Burden — Staff can retrieve complete and accurate information quickly, enabling them to focus on patient care rather than data reconciliation.
• Operational Insights — Hospital administrators can use the data to monitor performance metrics, track department utilization, and allocate resources more effectively.
• Scalability for Advanced Analytics — The system architecture supports future integration with predictive analytics and business intelligence tools.

Tags

SQL · Database Design · Healthcare · MySQL · ER Modeling · Data Analytics · Business Analysis