UseCase05 - Doctor Records Symptoms and Creates Diagnosis

Initiating Actor - Doctor

Description

During a patient consultation, a doctor records symptoms for the patient's medical record and creates a diagnosis. The system stores the symptoms and diagnosis linked to both the patient and the 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 p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);

2. System displays all available symptoms for the doctor to choose from.

SELECT
  s.symptom_id,
  s.name,
  s.description
FROM symptoms s
ORDER BY s.name;

3. Doctor selects symptoms and the system records them in the patient's medical record.

INSERT INTO medical_record_symptoms (record_id, symptom_id, 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 symptom_id FROM symptoms WHERE name = 'HEADACHE'),
  'MODERATE'
);

4. Doctor enters a diagnosis name and description and submits it.

INSERT INTO diagnosis (diagnosis_id, name, description, patient_id, doctor_id)
VALUES (
  (SELECT COALESCE(MAX(diagnosis_id), 0) + 1 FROM diagnosis),
  'Essential hypertension',
  'High blood pressure condition',
  (SELECT p.patient_id
   FROM patients p
   JOIN users u ON u.user_id = p.user_id
   WHERE u.username = '1402994123456'),
  (SELECT d.doctor_id
   FROM doctors d
   JOIN users u ON u.user_id = d.user_id
   WHERE u.username = 'elena.kirova@medora.com')
)
RETURNING diagnosis_id;

5. System confirms the diagnosis was created and displays the record.

SELECT
  d.diagnosis_id,
  d.name,
  d.description,
  u.first_name AS doctor_first_name,
  u.last_name AS doctor_last_name
FROM diagnosis d
JOIN doctors doc ON d.doctor_id = doc.doctor_id
JOIN users u ON u.user_id = doc.user_id
WHERE d.diagnosis_id = (SELECT MAX(diagnosis_id) FROM diagnosis);