Scribd
Upload
5 views5 pages

Centralized Healthcare Appointment System

The document outlines a centralized healthcare appointment management system using relational databases to address issues like long patient wait times and overlapping doctor schedules. It details the problem understanding, entity identification, relational schema, and the appointment booking workflow. The system aims to streamline appointment management by linking patients to doctors while ensuring consistent record-keeping and availability tracking.

Uploaded by

arunkumar67278
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views5 pages

Centralized Healthcare Appointment System

The document outlines a centralized healthcare appointment management system using relational databases to address issues like long patient wait times and overlapping doctor schedules. It details the problem understanding, entity identification, relational schema, and the appointment booking workflow. The system aims to streamline appointment management by linking patients to doctors while ensuring consistent record-keeping and availability tracking.

Uploaded by

arunkumar67278
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

DB FOUNDATIONS – HACKATHON

LEVEL -1: IDEATION AND SOLUTION DOCUMENTATION

USE CASE TITLE: CENTRALIZED HEALTHCARE APPOINTMENT MANAGEMENT


USING RELATIONAL DATABASES

STUDENT NAME: DIVYADHARSHINI .R

REGISTER NUMBER: 30323U18017

INSTITUTION: ARCOT SRI MAHAALAKSHMI WOMEN’S COLLEGE

DEPARTMENT: [Link]., COMPUTER SCIENCE

DATE OF SUBMISSION: 30-08-2025

1. PROBLEM UNDERSTANDING & ASSUMPTIONS:

In many hospitals and clinics, appointments are often managed using phone calls or individual
spreadsheets. This leads to issues like:

 Patients waiting for long hours


 Doctors facing overlapping schedules
 Difficulty in tracking availability across multiple branches

To overcome these limitations, a centralized relational database is required to organize information


about doctors, patients, departments, and appointment slots. This ensures smooth booking,
cancellation, and rescheduling while keeping all records consistent and reliable.

Assumptions considered:

 One appointment links exactly one doctor and one patient.


 Time is divided into fixed intervals (15–30 mins).
 A doctor belongs to a single department, but can work in multiple branches through
scheduled shifts.
 Every patient gets a unique system-generated ID. Phone and email details are optional but, if
present, they must be validated.
2. ENTITY IDENTIFICATION & ER MODEL:

Primary Entities:

 Branch – Physical unit of the hospital.


 Department – Specialization area (Cardiology, Pediatrics, Orthopedics, etc.).
 Doctor – Healthcare professional with specific expertise and availability.
 Patient – Individual seeking medical care.
 Timeslot – Standard division of time (e.g., 10:00–10:15 AM).
 Doctor Schedule – Indicates where and when a doctor is available.
 Appointment – Links a patient to a doctor at a given date and timeslot.

Key Relationships:

 A branch can contain many departments.


 Each department can employ multiple doctors.
 A doctor can serve in multiple branches.
 A patient can book many appointments.
 An appointment is always tied to one doctor and one patient.
 Doctor schedule + timeslot determine valid booking windows.

3. RELATIONAL SCHEMA:

Branch:

 Branch(branch_id PK, name, city, address)

Department:

 Department(dept_id PK, branch_id FK→Branch.branch_id, name)


Doctor:

 Doctor(doctor_id PK, dept_id FK→Department.dept_id, full_name, specialization)

Patient:

 Patient(patient_id PK, full_name, dob, gender, phone, email)

Timeslot:

 Timeslot(slot_id PK, start_time, end_time)

DoctorSchedule:

 DoctorSchedule(schedule_id PK, doctor_id FK→Doctor.doctor_id, branch_id


FK→Branch.branch_id, dept_id FK→Department.dept_id, slot_id FK→Timeslot.slot_id,
schedule_date)

Appointment:

 Appointment(appt_id PK, schedule_id FK→DoctorSchedule.schedule_id, patient_id


FK→Patient.patient_id, status)

4. APPOINTMENT BOOKING WORKFLOW:

1. Patient Request:
o The patient chooses a doctor, preferred date, and a specific timeslot.
2. Availability Validation:
o The system checks the appointment records.
o If the doctor already has a booking for that slot, an error is shown and alternatives are
suggested.
o If the slot is free, the process continues.

3. Conflict Prevention:
o Prevents double bookings by verifying that the doctor and timeslot combination is unique.
o Verifies doctor schedules and holidays before confirming.

4. Confirmation & Notification:


o Once validated, the booking is saved with status = “Booked”.
o Patients and doctors receive confirmation (system message, SMS, or email).

5. Updating & Canceling:


o Patients can reschedule or cancel appointments.
o Once canceled, the slot becomes available for others.

Common questions

Powered by AI

Clinics and hospitals often manage appointments using manual methods like phone calls or spreadsheets, leading to issues such as long patient wait times, doctor schedule overlaps, and difficulty tracking availability across multiple branches . The centralized system aims to resolve these issues by organizing information in a relational database, facilitating smoother booking, cancellation, and rescheduling while ensuring records remain consistent and reliable .

The Doctor Schedule and Timeslot entities play a crucial role in preventing booking conflicts. The Doctor Schedule indicates the availability of doctors across different branches and departments, while the Timeslot ensures that appointments are booked only in predefined intervals . Together, they allow the system to verify the availability of a doctor at a specific time and location, thus preventing double bookings and scheduling conflicts by ensuring that the doctor-timeslot combination remains unique .

The primary entities in the system include Branch, Department, Doctor, Patient, Timeslot, DoctorSchedule, and Appointment . Each entity plays a specific role: Branch represents physical locations; Department dictates medical specializations; Doctor denotes medical staff with specific availability; Patient accounts for individuals seeking care; Timeslot defines standard time intervals for appointments; DoctorSchedule ensures proper allocation of doctors to times and locations; Appointment links patients to doctors, enabling the booking, confirmation, and management of consultations .

Using a centralized relational database improves the efficiency of a healthcare appointment management system by consolidating data from various branches, departments, doctors, and patients into an organized structure . This eliminates redundancy, allows real-time updates, and facilitates easy access to appointment information, leading to reduced wait times, minimized schedule conflicts, and overall more efficient management compared to traditional methods like phone bookings and spreadsheets .

The design manages rescheduling or cancellation by allowing patients to reschedule or cancel their appointments through the system, thus automatically freeing up the doctor and timeslot for other bookings . By updating the status of appointments and using validation checks to confirm availability before scheduling new appointments, the system prevents data inconsistencies and ensures that all records in the database are up-to-date and accurate .

Validating patient phone and email information is significant because it ensures reliable communication and accurate record-keeping. Though optional, valid contact details allow the system to send necessary confirmations and updates regarding appointments, which aids in maintaining a high level of service and preventing potential miscommunications or missed appointments . Moreover, data validation helps prevent the entry of erroneous or incomplete data, maintaining the database's overall integrity .

The relational schema uses primary and foreign keys to establish and maintain data integrity and relationships among entities. For example, the Department table's branch_id acts as a foreign key referencing the Branch table, connecting departments to specific branches . Similarly, associations between doctors and departments, as well as between appointments and doctor schedules, ensure that relevant data is linked reliably, preventing issues like orphaned records and maintaining consistency across the database .

The relational schema standardizes and coordinates branches by structuring data to cross-reference multiple entities, such as branches, departments, doctors, and schedules . Each branch can house multiple departments, with doctors tied to departments and able to operate across different branches via their schedules . By integrating branch_id and related foreign keys within entities like Department and DoctorSchedule, the schema enables consistent management and coordination of information across all branches, facilitating efficient, centralized control of operations .

Time divisions into fixed intervals allow precise scheduling, which prevents overlapping appointments and ensures efficient time management . In the system's design, this is modeled through a Timeslot entity that defines specific start and end times. This standardizes the scheduling process, allowing the system to check availability and book appointments without conflicts, thereby improving the overall management of doctors' schedules and patient appointments .

The system assumes that a doctor belongs to only one department but can work in multiple branches through scheduled shifts . This assumption simplifies the management of doctors' availability and ensures clear lines of expertise and departmental organization. It benefits the system's functionality by structuring schedules around departments, reducing complexity when coordinating doctor availability across branches, and minimizing overlap in appointments .

You might also like