UseCase14 - View and Record Patient Allergies

Initiating Actor - Doctor / Patient / Lab Technician

Description

Medical professionals and patients view a patient's recorded allergies. A doctor can additionally record a newly identified allergy during consultation. The system checks for duplicates and adds it to the patient's medical record.

Scenario

1. User navigates to the patient's allergy profile.

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

2. System displays all recorded allergies with severity levels.

3. Doctor clicks "Add Allergy" and system displays all available allergies to choose from.

SELECT
  a.allergy_id,
  a.name,
  a.allergy_severity
FROM allergies a
ORDER BY a.name;

4. System checks the allergy is not already recorded for this patient.

SELECT 1
FROM medical_record_allergies mra
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'
)
  AND mra.allergy_id = (SELECT allergy_id FROM allergies WHERE name = 'IBUPROFEN');

5. Doctor selects the allergy, enters reaction and severity, and submits.

INSERT INTO medical_record_allergies (record_id, allergy_id, reaction, severity)
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 allergy_id FROM allergies WHERE name = 'IBUPROFEN'),
  'Stomach upset and mild hives',
  'MODERATE'
);

6. System displays the updated allergy profile.

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