# Enabling Subtype Testing

## Requirement

The SNOMED CT subtype polyhierarchy is an important resource which allows retrieval of all the concepts that are subtypes of a specified concept. The full hierarchy is represented by subtype (is a) relationships between each concept and its proximal supertypes. Therefore, to determine is whether a concept is a subtype of another concept a chain of these subtypes must be followed. It is possible to follow these chain in a relational database environment but the queries to achieve this are complex and often perform poorly.

## Solution

Fortunately, there is a well-understood way to simplify and speed up the testing process. This requires the creation of a resource known as a transitive closure table. A transitive closure table includes direct relationships between every concept and all of it subtypes and supertypes. This makes it possible to test whether a concept is a subtype of another concept looking for a single row in that table. Similarly it makes it easy to access all the subtypes (or supertypes) of concept with a query on a single table.

## Creating a Transitive Closure File

SNOMED International provides a Perl script that reads the snapshot relationships file. By processing this file, it rapidly generates the transitive closure of all the subtype relationships and then saves this as a file. The result a two column file (subtypeId and supertypeId) containing more than 6.5 million rows. This transitive closure file can then be read into a database table in the same way as the release files.

## Using a Transitive Closure File

By using the transitive closure table very simple queries can:

* Test is concept A is a subtype of concept B
* List all supertypes of concept A
* List all subtypes of concept A

## Computing Proximal Primitive Supertypes

The transitive closure can also be used to determine which supertype concept or concepts are essential to the definition of a concept. These concepts are known as the proximal primitive supertypes of a concept. *Proximal primitive supertypes* can be computed as follows.

1. Use the transitive closure table to get the set of all concepts that are supertypes of concept **A**
   * Call this set: Set-B
2. Create a subset of Set-B containing only those concepts with definitionStatusId= 900000000000074008 <mark style="color:blue;">|</mark> Primitive<mark style="color:blue;">|</mark>
   * Call this set: Set-C
3. [Create a subset of Set-C containing only those concepts that have no *subtypes* that are also in Set-C](#user-content-fn-1)[^1]
   * The resulting set represents the primitive supertypes of concept **A**.

***

<a href="https://docs.google.com/forms/d/e/1FAIpQLScTmbZIf0UEQwYDkY27EEWBkaiYkHSbR0_9DmFrMLXoQLyL7Q/viewform?usp=pp_url&#x26;entry.1767247133=SQL+Guide&#x26;entry.670899847=Enabling%20Subtype%20Testing" class="button primary">Provide Feedback</a>

[^1]: Concepts in Set-C that also have subtypes in Set-C are primitive supertypes of concept **A** but they are not ***proximal** primitive supertypes* because these subtype(s) are more specific concepts that are also primitive supertypes of concept **A**.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.snomed.org/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/database-design/4.7-enabling-subtype-testing.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.