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 p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);

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 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'
);

3. Doctor enters the medication name and submits.

INSERT INTO prescriptions (prescription_id, medication_name)
VALUES (
  (SELECT COALESCE(MAX(prescription_id), 0) + 1 FROM prescriptions),
  '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 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 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 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'
);