UseCase10 - Schedule Appointment

Initiating Actor - Doctor or System Administrator

Description

An appointment is scheduled for a patient with a specific doctor on a specific date and time. The system checks for conflicts, creates the appointment record, and makes it visible to both the patient and doctor.

Scenario

1. User selects a patient and a doctor to schedule an appointment for.

SELECT
  p.patient_id,
  u_p.first_name,
  u_p.last_name,
  p.embg
FROM patients p
JOIN users u_p ON u_p.user_id = p.user_id
WHERE u_p.username = '1402994123456';

2. System retrieves the selected doctor details.

SELECT
  d.doctor_id,
  u_d.first_name,
  u_d.last_name,
  ds.specialization_name,
  d.department_id
FROM doctors d
JOIN doctor_specialization ds
ON d.specialization_id = ds.specialization_id
JOIN users u_d
ON u_d.user_id = d.user_id
WHERE u_d.username = 'elena.kirova@medora.com';

3. System checks which time slots are already taken for the doctor on the selected date.

SELECT a.appointment_time
FROM appointments a
WHERE a.doctor_id = (
    SELECT d.doctor_id
    FROM doctors d
    JOIN users u ON u.user_id = d.user_id
    WHERE u.username = 'elena.kirova@medora.com')
  AND a.appointment_date = '2026-06-12'
  AND a.status != 'CANCELLED';

4. System verifies the patient does not already have an appointment with this doctor at the chosen time.

SELECT 1
FROM appointments a
WHERE a.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456')
  AND a.doctor_id = (
    SELECT d.doctor_id
    FROM doctors d
    JOIN users u ON u.user_id = d.user_id
    WHERE u.username = 'elena.kirova@medora.com')
  AND a.appointment_date = '2026-06-12'
  AND a.appointment_time = '10:00'
  AND a.status != 'CANCELLED';

5. System creates the appointment record.

INSERT INTO appointments (appointment_id, patient_id, doctor_id, appointment_date, appointment_time, status)
VALUES (
  (SELECT COALESCE(MAX(appointment_id), 0) + 1 FROM appointments),
  (SELECT p.patient_id
   FROM patients p
   JOIN users u ON u.user_id = p.user_id
   WHERE u.username = '1402994123456'),
  (SELECT d.doctor_id
   FROM doctors d
   JOIN users u ON u.user_id = d.user_id
   WHERE u.username = 'elena.kirova@medora.com'),
  '2026-06-12',
  '10:00',
  'SCHEDULED'
)
RETURNING appointment_id;

6. System displays the confirmation with full appointment details.

SELECT
  a.appointment_id,
  a.appointment_date,
  a.appointment_time,
  a.status,
  u_d.first_name AS doctor_first_name,
  u_d.last_name AS doctor_last_name,
  u_p.first_name AS patient_first_name,
  u_p.last_name AS patient_last_name
FROM appointments a
JOIN doctors d ON a.doctor_id = d.doctor_id
JOIN users u_d ON u_d.user_id = d.user_id
JOIN patients p ON a.patient_id = p.patient_id
JOIN users u_p ON u_p.user_id = p.user_id
WHERE a.appointment_id = (SELECT MAX(appointment_id) FROM appointments);

7. System displays all upcoming appointments for the patient.

SELECT
  a.appointment_id,
  a.appointment_date,
  a.appointment_time,
  a.status,
  u_d.first_name AS doctor_first_name,
  u_d.last_name AS doctor_last_name
FROM appointments a
JOIN doctors d ON a.doctor_id = d.doctor_id
JOIN users u_d ON u_d.user_id = d.user_id
WHERE a.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456')
  AND a.appointment_date >= CURRENT_DATE
ORDER BY a.appointment_date ASC, a.appointment_time ASC;