78 ) Hospital mgt data model project

Hospital Project Data Model & Interview Q&A

Step 1: The Client's Existing System and Tables

In my previous hospital project, the client was using basic operational databases to manage patient registrations, doctor schedules, and hospital admissions:

• Patient & Appointment Database (MySQL): This handled patient profiles and doctor appointments. The main tables were:

  • Patients: Stored patient IDs, names, phone numbers, and home addresses.

  • Doctors: Stored doctor IDs, names, departments (like Cardiology or Orthopedics), and consulting fees.

  • Appointments: Stored appointment IDs, patient IDs, doctor IDs, and appointment timestamps.

• Billing & Room Management Database (SQL Server): This handled room admissions, medical bills, and insurance claims. The main tables were:

  • Rooms: Stored room numbers, room types (General Ward, Semi-Private, ICU), and daily rates.

  • Admissions: Stored admission IDs, patient IDs, assigned room numbers, check-in dates, and discharge dates.

  • Billing_Transactions: Stored service charges, medicine costs, room rent, and total bill amounts paid.

Step 2: The Problems They Faced

Because patient details, appointments, and billing records were stored in two different databases, the hospital administration faced specific operational hurdles:

• Insurance Claim Rejections and Delayed Payouts: Insurance verification data lived in a separate third-party claims portal, while patient treatment logs lived in the internal database. Because these systems were disconnected, front desk staff couldn't verify pre-authorization limits before procedures, leading to frequent claim rejections and delayed hospital reimbursements.

• Pharmacy Inventory and Prescription Mismatches: The pharmacy dispensing database and the doctor's electronic prescription system did not sync in real-time. Nurses often found that medicines prescribed by doctors were out of stock, causing delays in patient treatment and inaccurate end-of-month pharmacy revenue counts.

• Emergency Room (ER) Bottlenecks: The ER triage system tracked patient arrival severity, but bed availability data lived in the room management database. Because the two systems didn't communicate, staff couldn't track how long critical emergency patients waited for an ICU or ward bed to open up.

Step 3: The Client's Expectation and Why

• The Goal: They wanted a single data warehouse where insurance claims, pharmacy inventory, ER wait times, and billing details could live together.

• The Reason: They wanted fast daily reports to track insurance claim success rates, monitor pharmacy stock levels, reduce ER waiting times, and analyze hospital revenue without slowing down the live hospital app.

Step 4: The Solution Design I Proposed (Star Schema)

1. Defined What to Measure

We focused on key areas: Insurance Claims Tracking (approval rates and payouts), Pharmacy Sales (medicine consumption), and Patient Wait Times (ER and admission durations).

2. Designed the Dimension Tables (The Details)

• Dim_Patient: Stored patient names, ages, and addresses.

• Dim_Doctor: Stored doctor names, departments, and specialties.

• Dim_Insurance_Provider: Stored insurance company names, policy types, and network tiers.

• Dim_Medicine: Stored medicine names, categories, and unit prices.

• Dim_Date: Stored calendar dates so reports could be run by day, week, or month.

3. Designed the Fact Tables (The Numbers)

• Fact_Insurance_Claims: Stored claim IDs, patient IDs, requested amounts, approved payout amounts, and claim status (Approved, Rejected, Pending).

• Fact_Pharmacy_Sales: Stored prescription IDs, medicine IDs, quantity dispensed, and total medicine revenue.

• Fact_Patient_Wait_Times: Stored triage arrival timestamps, bed allocation timestamps, and total waiting duration in minutes.

Interview Questions and Answers: Hospital Project

Q1: Can't the hospital staff just run these reports directly on the MySQL database? Why do we need a Data Warehouse?

Answer:

If we run heavy reports on the live MySQL database, it slows down the system for front desk staff trying to register patients or book emergency rooms. The live database is built for quick everyday data entry, not for scanning millions of past admission logs to check monthly patient trends. A Data Warehouse keeps heavy reporting separate so the operational app stays fast.

Q2: What if you have to add a new column or change a dimension later? Is it easy to do?

Answer:

Yes, it is easy. If we want to add a new detail—like adding a "Patient Blood Group" column to the patient table—we just update the table structure and change our daily data loading script for future records. It does not break past data.

Q3: What if the fact table data needs to be loaded weekly or monthly instead of daily? How do you handle that?

Answer:

We handle this by scheduling our data loading jobs. While regular billing logs are loaded every night, monthly summary tables are set to run on the 1st of every month. We also split our large data tables by date so the system only updates the specific week or month needed instead of scanning everything.

Q4: What about patient privacy? How do you handle sensitive personal data in the data warehouse?

Answer:

To protect patient privacy and comply with healthcare data rules (like HIPAA), we mask or exclude sensitive medical diagnostic details and personally identifiable information that is not required for general business analytics. In our data warehouse, we only store high-level demographic keys, operational timestamps, and financial metrics needed for administrative reporting.

Q5: What is a "Factless Fact Table," and did you use one in the hospital project?

Answer:

A factless fact table is a table that has no money values or numbers inside it; it only tracks events or connections. Yes, we used one to track Doctor Consultation Check-ins (Fact_Doctor_Visits). It recorded which patient visited which doctor on a specific day, helping management check doctor attendance and daily appointment counts without tracking medical bills.

Q6: How do you handle changes to a doctor's department or room pricing when past hospital stays are already linked to them?

Answer:

We use Slowly Changing Dimensions (SCD Type 2). If a doctor moves to a different department, we don't overwrite the old department. We close the old row with an end date and add a new row with the new department. This way, old patient records always point to the doctor's department as it was on the day the patient was treated.

Q7: What is the difference between an Additive and Non-Additive fact in our hospital data?

Answer:

• Additive Fact: These can be added up across everything. For example, Total_Bill_Amount is additive—we can add it up by doctor, room type, or month to get total hospital revenue.

• Non-Additive Fact: These cannot be added up. Ratios or percentages fall here, like a Room_Occupancy_Rate_Percentage. You cannot add percentages together; the data warehouse has to calculate them fresh.

Q8: How do you stop duplicate billing records from coming into the data warehouse?

Answer:

We stop duplicates in two ways. First, our daily loading script cleans the data and drops exact technical duplicates. Second, we set unique rules on our fact table using the Transaction_ID and Timestamp, so if the same file is accidentally loaded twice, the system skips the duplicate rows.

Q9: What happens if an admission log comes in for a new doctor whose ID does not exist in the doctor table yet?

Answer:

To prevent the data loading script from crashing, we use a default row called "Unknown Doctor" with an ID of -1. If a missing ID comes in, the script assigns it to -1 so the report doesn't break, and it sends an alert to our team to add the new doctor to the system.

Q10: If the business asks to add a completely new metric that we never tracked in the past, what do you tell them?

Answer:

If we never collected that data in the past, I would explain to the business team that we cannot magically create old historical data. I would update our data collection script starting from today so we can track that new metric moving forward, and note clearly in the reports that older data is not available.