🏥 EHIAS

Hospital Database Migration & Automation

A spreadsheet of patient records, rebuilt as a database that enforces its own rules.

Every rule the hospital needed — no past appointments, no double-booked doctors, no patient data leaking outside a doctor's own department — is enforced by the database itself, not by whoever happens to be running the query.

Before this, the hospital ran on Excel: one workbook, one sheet per entity, and nothing stopping a department from being deleted while doctors still pointed at it, or a doctor being booked twice in the same slot. This project takes that spreadsheet and turns it into a proper relational database — schema, migration, and two pieces of real automation (a validation trigger and role-based stored procedures) that make the bad states structurally impossible instead of just "please don't do that."

---

📖 Table of Contents

• Why I Built This
• What This Solves
• Project Preview
• The Database in Action
• Data Flow
• Tech Stack
• Project Structure
• Running Locally
• Design Notes
• Reflection
• About the Author

---

🤔 Why I Built This

The brief was a hospital that had outgrown Excel. Patient details, doctor rosters, appointments, prescriptions, lab reports, and billing all lived in one workbook, and every problem that comes with that setup was already showing up: no guaranteed unique IDs, appointments with no enforceable link to a real patient or doctor, gender and status fields with whatever text someone typed, doctors getting double-booked, every doctor able to see every patient regardless of department, and no way to pull a revenue summary without doing it by hand.

None of those are UI problems — they're integrity problems. So instead of building a front end to paper over them, I built the schema and automation layer that makes each one impossible at the database level, then migrated the existing Excel data into it.

✅ What This Solves

Problem in the brief	How the database solves it
No guaranteed unique IDs	Every table's ID is a PRIMARY KEY (backed by AUTO_INCREMENT for future inserts) — duplicates are rejected, not just discouraged
Disconnected relationships	FOREIGN KEY constraints tie appointments to real patients and doctors; nothing can reference a row that doesn't exist
Invalid/ambiguous data entries	CHECK constraints restrict gender to M/F/O and appointment_status to Scheduled/Completed/Cancelled
Unregulated scheduling	A BEFORE INSERT trigger blocks past-dated appointments and rejects a doctor being double-booked in the same slot
Open access to sensitive patient data	VIEW_DOCTOR_DATA authenticates against doctor_credentials, then returns department-wide data to Senior doctors and only-own-patients data to everyone else
Disconnected reporting	sp_monthly_revenue(year, month) returns department-wise revenue on demand instead of a manual roll-up

---

Project Preview

7 tables, all reachable from appointments — the record that everything else (prescriptions, bills, lab reports) hangs off of.

---

🎬 The Database in Action

Migration

hospital_data is the flattened staging table the raw Excel export lands in — every original sheet name became a column prefix (Departments.DepartmentID, Patients.DateOfBirth, etc). The migration script reads out of that one table and populates all 7 normalized tables.

Scheduling automation

Two rules, enforced the same way: try to violate them and the trigger stops the insert before it happens.

Booking in the past

Double-booking a doctor

Role-based patient access

Same procedure, same login flow — the result set changes shape entirely based on the calling doctor's role.

Senior doctor — department-wide

Regular doctor — own patients only

Department revenue, on demand

CALL sp_monthly_revenue(2025, 4) — no manual roll-up required.

---

🧠 Data Flow

Excel workbook (Departments, Doctors, Patients, Appointments, ...)
        │  exported as flat CSV
        ▼
hospital_data  (staging table, one column per "Sheet.Field")
        │
        ▼
01_tables.sql          ──►  normalized schema (7 tables + doctor_credentials)
        │
        ▼
02_data_migration.sql  ──►  INSERT ... SELECT per table, STR_TO_DATE for
                             every dd-mm-yyyy text field Excel produced
        │
        ▼
03_triggers.sql         ──►  BEFORE INSERT validation on appointments
04_procedures.sql       ──►  VIEW_DOCTOR_DATA (RBAC) + sp_monthly_revenue

The staging table only ever gets read from, never written to — everything downstream of it is the real, constrained schema. That boundary is deliberate: it's the one place "whatever Excel happened to contain" is allowed to be messy.

---

🛠️ Tech Stack

Technology	Role
MySQL	Schema, constraints, trigger, stored procedures — where every rule in this project actually lives
MySQL Workbench	Running the migration, seeding hospital_data from CSV, and capturing the screenshots above

---

🗂️ Project Structure

EHIAS/
├── 01_tables.sql            # schema: 7 core tables + doctor_credentials
├── 02_data_migration.sql    # ETL from hospital_data into the normalized schema
├── 03_triggers.sql          # appointment validation trigger + demo inserts
├── 04_procedures.sql        # VIEW_DOCTOR_DATA (RBAC) + sp_monthly_revenue
├── schema/
│   └── schema_design.webp   # ERD
├── Dataset/
│   ├── hospital_data_10000_rows.csv   # raw flattened Excel export
│   └── doctor_credentials.csv         # seed logins for VIEW_DOCTOR_DATA
├── screenshots/              # images used in this README
└── README.md

Before pushing: the screenshots folder in this project is currently named screencshots (typo) — rename it to screenshots so the image links above resolve correctly.

---

🚀 Running Locally

Prerequisites

• MySQL Server + MySQL Workbench

Steps

-- 1. Create and select a database
CREATE DATABASE ehias;
USE ehias;

-- 2. Run the schema
SOURCE 01_tables.sql;

3. Import the raw data via Workbench's Table Data Import Wizard:
   - Dataset/hospital_data_10000_rows.csv  ->  new table "hospital_data"
   - Dataset/doctor_credentials.csv        ->  existing table "doctor_credentials"

-- 4. Migrate hospital_data into the normalized tables
SOURCE 02_data_migration.sql;

-- 5. Add the scheduling trigger
SOURCE 03_triggers.sql;

-- 6. Add the stored procedures
SOURCE 04_procedures.sql;

-- 7. Try it
CALL VIEW_DOCTOR_DATA('doctor1', '<password from doctor_credentials.csv>');
CALL sp_monthly_revenue(2025, 4);

---

📝 Design Notes

• doctor_credentials isn't in the original ERD. VIEW_DOCTOR_DATA needs somewhere to authenticate against, so it's part of the schema now — kept as its own table rather than bolted onto doctors, since login data and clinical data have no reason to live together.
• IDs are inserted explicitly during migration, not left to AUTO_INCREMENT. The source Excel data already carries its own IDs, so the migration preserves them exactly rather than letting MySQL reassign new ones — AUTO_INCREMENT is there as a safety net for any row added after migration, not the primary ID source.
• Passwords in doctor_credentials are plaintext. Fine for 200 synthetic demo logins in a coursework project; a real system would store a hash, never the raw value.

---

💭 Reflection

The interesting part of this project wasn't the schema — that's mostly translating an Excel workbook into tables. It was realizing how many of the brief's "problems" weren't data problems at all, they were timing problems: nothing stopped a bad row from being written in the first place. A CHECK constraint or a trigger fixes that permanently, at the one point that actually matters — the INSERT — instead of relying on whoever's writing the query to remember the rule.

---

About the Author

Rushit Tholiya LinkedIn Profile

GitHub Profile