Terminology Architecture | Medplum

Table Schema

Medplum uses a handful of dedicated tables to store terminology data. The foundation of these is Coding, which stores individual codes related to CodeSystem resources by their ID:

Coding

The Coding table has a unique index on (system, code) — there can only be one row for a given code per CodeSystem.

Codings can also have properties, which define additional metadata about the codes. These are defined in the CodeSystem_Property table, and then referenced in Coding_Property alongside the property values:

CodeSystem_Property

The CodeSystem_Property table has a unique index on (system, code) — there can only be a single property for a given code per CodeSystem.

Coding_Property

There is a covering unique index on Coding_Property; each row must be fully unique. It is valid for a Coding to have multiple values for a given property.

CodeSystem Hierarchies

Many code systems define parent-child relationships between their codes, to denote that one code is a more general category and the other a more specific instance of that category. These relationships are stored as properties of the related codes: the properties are marked with a special uri (e.g. http://hl7.org/fhir/concept-properties#parent or http://hl7.org/fhir/concept-properties#child), and the target of the Coding_Property row points to the other Coding.

For example, two codes might be related as follows:

• Coding 1: id: 1, code: '78012-2'
• Coding 2: id: 2, code: 'LP376020-6'
• CodeSystem_Property: id: 12, code: 'parent', uri: 'http://...#parent'

Note: While FHIR supports specifying hierarchical relationships with either parent or child properties, Medplum currently only supports parent for simplicity, since the two are inversely equivalent.

Terminology Operations

FHIR specifies a suite of Operation endpoints to interact with terminology information, which Medplum implements on top of the tables described above.

CodeSystem/$validate-code

Validating whether a CodeSystem contains any of a set of codes is a simple query:

-- Get CodeSystem by URL

SELECT id, content FROM "CodeSystem" WHERE url = ?;

-- Check whether codes exist

SELECT id, code, display FROM "Coding"
WHERE code IN (?, ?) AND system = ?;

CodeSystem/$lookup

Looking up a given code in a CodeSystem is similar to the $validate-code operation above, but also looks up any properties of the given code:

-- Get CodeSystem by URL

SELECT id, content FROM "CodeSystem" WHERE url = ?;

-- Look up code and attached properties

SELECT
  "Coding".display,
  property.code,
  property.type,
  property.description,
  property.value
FROM "Coding"
  LEFT JOIN "Coding_Property" AS cp ON "Coding".id = cp.coding
  LEFT JOIN "CodeSystem_Property" AS property ON cp.property = property.id
WHERE "Coding".code = ? AND "Coding".system = ?;

CodeSystem/$subsumes

Subsumption testing involves checking whether one code is related to another through a hierarchy defined by the code system. For example, one might want to check whether the code for "amoxicillin" descends from the code for "antibiotics".

The parent properties from each code are recursively traversed to check if either is an ancestor of the other:

-- Get CodeSystem by URL

SELECT id, content FROM "CodeSystem" WHERE url = ?;

-- Check if codeA is an ancestor of codeB; this query is performed once in each direction

WITH RECURSIVE "cte_ancestors" AS (
    SELECT id, code, display FROM "Coding"
    WHERE system = ? AND code = ?
  UNION
      SELECT c.id, c.code, c.display FROM "Coding" c
        INNER JOIN "Coding_Property" AS cp ON c.id = cp.target
        INNER JOIN "CodeSystem_Property" AS property ON (
          cp.property = property.id
          AND property.code = ?
        )
        INNER JOIN "cte_ancestors" AS ancestor ON cp.coding = ancestor.id
      WHERE
        c.system = ?
)

SELECT code, display FROM "cte_ancestors"
WHERE code = ?
LIMIT 1;

ValueSet/$validate-code

FHIR ValueSets define which codes should be used from specific code systems for some use case. Determining if a given code is in the ValueSet is one of the core FHIR terminology operations, and is designed to be used in resource validation.

SELECT  c.id, c.code, c.display FROM "Coding" c
  LEFT JOIN "Coding_Property" AS cp ON (
    c.id = cp.coding
    AND cp.value = ?
  )
  LEFT JOIN "CodeSystem_Property" AS property ON (
    c.system = property.system
    AND property.id = cp.property
    AND property.code = ?
  )
WHERE (
  c.system = ?
  AND c.code = ?
  AND cp.value IS NOT NULL
  AND property.system IS NOT NULL
);

ValueSet/$expand

ValueSet expansion is similar to the $validate-code operation, but rather than checking a single code it returns an (optionally-filtered) list of codes that are contained in the ValueSet. This is often used to support typeahead queries in user interfaces.

-- Expand a hierarchy, starting from the parent code

WITH RECURSIVE "cte_descendants" AS (
    SELECT id, code, display FROM "Coding"
    WHERE system = ? AND code = ?
  UNION
      SELECT c.id, c.code, c.display FROM "Coding" c
        INNER JOIN "Coding_Property" AS cp ON c.id = cp.coding AND cp.property = ?
        INNER JOIN "cte_descendants" AS descendant ON cp.target = descendant.id
)
SELECT id, code, display FROM "cte_descendants"
LIMIT 101;

CodeSystem/$import

Medplum supports a non-standard Operation to load codes into the database for large code systems. It generates bulk inserts for codes and their properties:

-- Get CodeSystem by URL

SELECT id, content FROM "CodeSystem" WHERE url = ?;

-- Import codes

INSERT INTO "Coding" (system, code, display) VALUES
  (?, '8867-4', 'Heart rate'),
  (?, '8302-2', 'Body height')
  ON CONFLICT (system, code) DO UPDATE
  SET display = EXCLUDED.display;

-- Get property by system and code

SELECT id FROM "CodeSystem_Property" WHERE system = ? AND code = ?;

-- Import properties

INSERT INTO "Coding_Property" (coding, property, value, target) VALUES
  (1, 12, 'LP415671-9', 2),
  (1, 21, 'Qn', null)
  ON CONFLICT DO NOTHING;

• Table Schema
• CodeSystem Hierarchies
• Terminology Operations
  • CodeSystem/$validate-code
  • CodeSystem/$lookup
  • CodeSystem/$subsumes
  • ValueSet/$validate-code
  • ValueSet/$expand
  • CodeSystem/$import