Definition

The MKB Mapping Attributes model provides consolidated information about mappings between local and national codes and SNOMED concepts, specifically for language codes and sexual orientation codes. Can be joined to the patient table to identify language and sexual orientation demographics for individual patients.

Constraints and Notes

• Soft deletion: When is_deleted is true, data fields may be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys are preserved so deleted linked records can be identified.

Examples

Active language mappings

SELECT
  code_id,
  snomed_concept_id,
  data_dictionary_national_code,
  attribute_type,
  transform_datetime
FROM explorer_open_safely.mkb_mapping_attributes
WHERE
  attribute_type = 'language'
ORDER BY
  data_dictionary_national_code

Sexual orientation mappings

SELECT
  code_id,
  snomed_concept_id,
  data_dictionary_national_code,
  attribute_type,
  transform_datetime
FROM explorer_open_safely.mkb_mapping_attributes
WHERE
  attribute_type = 'sex_orientation'
ORDER BY
  data_dictionary_national_code

Mapping statistics by type

SELECT
  attribute_type,
  COUNT(*) AS total_mappings,
  SUM(CASE WHEN snomed_concept_id IS NOT NULL THEN 1 ELSE 0 END) AS mappings_with_snomed,
  MIN(transform_datetime) AS earliest_update,
  MAX(transform_datetime) AS latest_update
FROM explorer_open_safely.mkb_mapping_attributes
GROUP BY
  attribute_type
ORDER BY
  attribute_type