UseCase15 - Create Prescription

Initiating Actor - Doctor

Description

A doctor prescribes medication for a patient, specifying dosage, frequency, and duration. The system creates a prescription record and links it to the patient's medical record.

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 displays the patient's known allergies as a safety reference.

SELECT
  a.allergy_id,
  a.name,
  a.allergy_severity,
  mra.reaction,
  mra.severity
FROM medical_record_allergies mra
JOIN allergies a ON mra.allergy_id = a.allergy_id
WHERE mra.record_id = (
  SELECT record_id FROM medical_records
  WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')
);

3. Doctor enters the medication name and submits.

INSERT INTO prescriptions (medication_name)
VALUES ('Lisinopril')
RETURNING prescription_id;

4. System links the prescription to the patient's medical record with full dosage details.

INSERT INTO prescription_medical_records (record_id, prescription_id, dosage, frequency, duration, notes)
VALUES (
  (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')),
  (SELECT MAX(prescription_id) FROM prescriptions),
  '10mg',
  'Once daily in the morning',
  '30 days',
  'Take with meals'
);

5. System displays the updated prescription list for the patient's medical record.

SELECT
  p.prescription_id,
  p.medication_name,
  pmr.dosage,
  pmr.frequency,
  pmr.duration,
  pmr.notes
FROM prescription_medical_records pmr
JOIN prescriptions p ON pmr.prescription_id = p.prescription_id
WHERE pmr.record_id = (
  SELECT record_id FROM medical_records
  WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')
);