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

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 record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM patients WHERE user_id = (SELECT user_id FROM users WHERE username = 'maja.veljanova'))),
  (SELECT symptom_id FROM symptoms WHERE name = 'Headache'),
  'MODERATE'
);

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

INSERT INTO diagnosis (name, description, patient_id, doctor_id)
VALUES (
  'Essential hypertension',
  'High blood pressure condition',
  (SELECT patient_id FROM patients WHERE user_id = (SELECT user_id FROM users WHERE username = 'maja.veljanova')),
  (SELECT doctor_id FROM doctors WHERE user_id = (SELECT user_id FROM users WHERE username = 'elena.kirova'))
)
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 doc.user_id = u.user_id
WHERE d.diagnosis_id = (
SELECT MAX(diagnosis_id) 
FROM diagnosis);