UseCase11 - Create Referral

Initiating Actor - Doctor

Description

A doctor creates a referral for a patient to another doctor for specialist opinion or a consultation. The system creates the referral record and automatically schedules an appointment with the referred doctor.

Scenario

1. Doctor opens a patient's medical record.

SELECT
  mr.record_id,
  mr.patient_id
FROM medical_records mr
WHERE mr.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);

2. System retrieves the list of available specialist doctors.

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
ORDER BY ds.specialization_name, u_d.last_name;

3. Doctor selects the receiving doctor, enters the referral reason, and specifies the appointment date and time.

INSERT INTO referrals (referral_id, record_id, from_doctor_id, to_doctor_id, reason, referral_date)
VALUES (
  (SELECT COALESCE(MAX(referral_id), 0) + 1 FROM referrals),
  (SELECT mr.record_id
   FROM medical_records mr
   JOIN patients p ON mr.patient_id = p.patient_id
   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'),
  (SELECT d.doctor_id
   FROM doctors d
   JOIN users u ON u.user_id = d.user_id
   WHERE u.username = 'nikola.trajkov@medora.com'),
  'Cardiology consultation for hypertension management',
  '2026-05-30'
)
RETURNING referral_id;

4. System automatically creates an appointment with the receiving doctor.

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 = 'nikola.trajkov@medora.com'),
  '2026-10-20',
  '14:00',
  'SCHEDULED'
)
RETURNING appointment_id;

5. System displays the confirmation with full referral and appointment details.

SELECT
  r.referral_id,
  r.reason,
  r.referral_date,
  a.appointment_date,
  a.appointment_time,
  u_from.first_name AS from_doctor_first_name,
  u_from.last_name AS from_doctor_last_name,
  u_to.first_name AS to_doctor_first_name,
  u_to.last_name AS to_doctor_last_name
FROM referrals r
JOIN doctors d_from ON r.from_doctor_id = d_from.doctor_id
JOIN users u_from ON u_from.user_id = d_from.user_id
JOIN doctors d_to ON r.to_doctor_id = d_to.doctor_id
JOIN users u_to ON u_to.user_id = d_to.user_id
JOIN medical_records mr2 ON mr2.record_id = r.record_id
JOIN appointments a ON a.patient_id = mr2.patient_id AND a.doctor_id = r.to_doctor_id
WHERE r.referral_id = (SELECT MAX(referral_id) FROM referrals)
ORDER BY a.appointment_date DESC
LIMIT 1;

6. System notifies the receiving doctor by displaying their incoming referrals.

SELECT
  r.referral_id,
  r.reason,
  r.referral_date,
  u_p.first_name AS patient_first_name,
  u_p.last_name AS patient_last_name
FROM referrals r
JOIN medical_records mr ON r.record_id = mr.record_id
JOIN patients p ON mr.patient_id = p.patient_id
JOIN users u_p ON u_p.user_id = p.user_id
WHERE r.to_doctor_id = (
    SELECT d.doctor_id
    FROM doctors d
    JOIN users u ON u.user_id = d.user_id
    WHERE u.username = 'nikola.trajkov@medora.com')
ORDER BY r.referral_date DESC;