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

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 record_id FROM medical_records
  WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')
)
  AND mra.allergy_id = (SELECT allergy_id FROM allergies WHERE name = 'Penicillin');

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

INSERT INTO medical_record_allergies (record_id, allergy_id, reaction, severity)
VALUES (
  (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')),
  (SELECT allergy_id FROM allergies WHERE name = 'Penicillin'),
  'Rash and swelling',
  '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 record_id FROM medical_records
  WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')
);