Definition

The Problem data model captures clinical problems recorded against patients, in the EMIS Web system an observation can be marked as a problem. A supplemental table to the observation table where observations that have been marked as a problem will be present in the problem table with additional fields. It represents long-term conditions, diagnoses, and health issues, including their status, significance, and hierarchical relationships between problems.

Constraints and Notes

• Soft deletion: When is_deleted is true, data fields will be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys to patient data are preserved so deleted records and their relationships can still be identified. Foreign keys to coding tables are nullified as the coded information is no longer valid.
• snomed_concept_id is resolved from code_id and may be NULL if no matching concept exists.
• Problems can be organised hierarchically through the parent_problem_id field, allowing related conditions to be grouped together.

Examples

Active problems for a specific patient

SELECT
  observation_id,
  patient_id,
  consultation_id,
  effective_datetime,
  problem_status_description,
  problem_significance_description,
  snomed_concept_id,
  organisation
FROM explorer_open_safely.problem
WHERE
  patient_id = <patient_id>
  AND organisation = <organisation>
  AND problem_status_description = 'Active'
  AND NOT is_deleted

Major problems opened in the last year

SELECT
  observation_id,
  patient_id,
  effective_datetime,
  problem_status_description,
  problem_significance_description,
  snomed_concept_id,
  organisation
FROM explorer_open_safely.problem
WHERE
  effective_datetime >= CURRENT_TIMESTAMP - INTERVAL '1' YEAR
  AND problem_significance_description = 'Major'
  AND NOT is_deleted