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 patient_id FROM users WHERE username = 'maja.veljanova'
);

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 d.doctor_id = u_d.doctor_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 (record_id, from_doctor_id, to_doctor_id, reason, referral_date, appointment_date, appointment_time)
VALUES (
  (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')),
  (SELECT doctor_id FROM users WHERE username = 'elena.kirova'),
  (SELECT doctor_id FROM users WHERE username = 'nikola.trajkov'),
  'Cardiology consultation for hypertension management',
  '2026-05-30',
  '2026-06-15',
  '14:00'
)
RETURNING referral_id;

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

INSERT INTO appointments (patient_id, doctor_id, appointment_date, appointment_time, status)
VALUES (
  (SELECT patient_id FROM users WHERE username = 'maja.veljanova'),
  (SELECT doctor_id FROM users WHERE username = 'nikola.trajkov'),
  '2026-06-15',
  '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,
  r.appointment_date,
  r.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 d_from.doctor_id = u_from.doctor_id
JOIN doctors d_to ON r.to_doctor_id = d_to.doctor_id
JOIN users u_to ON d_to.doctor_id = u_to.doctor_id
WHERE r.referral_id = (SELECT MAX(referral_id) FROM referrals);

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

SELECT
  r.referral_id,
  r.reason,
  r.referral_date,
  r.appointment_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 p.patient_id = u_p.patient_id
WHERE r.to_doctor_id = (SELECT doctor_id FROM users WHERE username = 'nikola.trajkov')
ORDER BY r.referral_date DESC;