Composite Views

The section Enabling Versioned Views considered the value of views of individual database tables. The objective of these views was to facilitate access to a range of useful snapshot and delta views. This section introduces the idea of defining composite views that access interrelated sets of data from different release files. The objective of composite views is to make it easier to access appropriate sets of data.

Example

Apart from the concept identifier, the concept table contains no useful human-readable data about a concept. Therefore, simply selecting data from a snapshot view of the concept table is unlikely to be useful.

To display appropriate human-readable information about a concept information is required from two other tables:

• The human-readable information about a concept is in the description table or view (e.g. snap_description)

• Information about which descriptions are preferred or acceptable in given language data is in a language refset table or view (e.g. snap_refset_language).

To display human-readable information about the way a concept is defined data is required from three other tables:

• The defining relationships are in the relationships table or view (e.g. snap_relationship)

• Human-readable display of the type and value specified in the relationship requires data from the description table or view (e.g. snap_description)

• Information about which descriptions are preferred or acceptable in given language data is in a language refset table or view (e.g. snap_refset_language).

The following subsections describe some general characteristics of composite views and introduce some of the composite views included in the SNOMED CT example database.

General Characteristics of Composite Views

Data Sources for Composite Views

Composite views need to get data from snapshot views. In most cases the requirements will be met by use of the current of each of the relevant tables. However, when reviewing past data the relevant retrospective views will need to be accessed. .

Most composite views should to gather all the required data from the table views for the same snapshot as illustrated in Table 1.

Table 1: Source Views for Data in Composite Views of Different Snapshots

Composite View	snap_pref	snap1_pref	snap2_pref
Description View	snap_description	snap1_description	snap2_description
Language Refset View	snap_refset_language	snap1_refset_language	snap2_refset_language

Composite views may themselves gather data from other composite views. For example as shown in Table 2 gets preferred term data from the preferred term composite views shown above.

Table 2: Composite View Including Data from Another Composite View

Composite View	snap_rel_pref	snap1_rel_pref	snap2_rel_pref
Concept View	snap_concept	snap1_concept	snap2_concept
Relationship View	snap_relationship	snap1_relationship	snap2_relationship
Preferred Term View	snap_pref	snap1_pref	snap2_pref

Composite views designed to support review of changes may gather data from different views as illustrated in Table 3.

Table 3: Source Views for Data in Historical Composite Views of Different Delta Views

Composite View	delta_inactive_concepts	delta1_inactive_concepts	delta2_inactive_concepts
Concept View	delta_concept	delta1_concept	delta2_concept
Association Refset View	snap_refset_association	snap_refset_association	snap_refset_association
Attribute Value Refset View	snap_refset_attributevalue	snap_refset_attributevalue	snap_refset_attributevalue
Preferred Term View	snap_pref	snap_pref	snap_pref
	snap_fsn	snap_fsn	snap_fsn

Representation of Composite Views

Composite views should be represented as database views rather than a physical database tables. Composite views denormalize data by combining the same data in different views therefore attempts to represent composite views as database tables is likely to rapidly multiply the size of the database. The example below is just one of many cases where creating concrete database tables to accommodate composite views might seem an attractive idea. However, pursuing this would create redundant data with few benefits, a major impact on storage requirements and a significantly more complex maintenance process when reviewing and installing future release packages. In contrast, representing composite views as database views, ensures the data is derived in real-time from tables representing the authoritative content of the full and/or snapshot release files.

Example

Most English language descriptions are either preferred or acceptable in both US and GB english. Therefore instantiating tables that represent the sets of preferred and acceptable terms in either or both dialects would not only duplicate much of the data in that table but would require even more space to duplicate the relevant indexes. In addition to the impact of disk space, data duplicated in these composite tables would need updating to take account of new releases.

Composite Description Views

Composite description views enable access to appropriate individual descriptions or sets of descriptions in a specific language or dialect. There are two sets of composite description views. Views in the first of these sets are designed to facilitate selecting descriptions associated with one or more identified concepts. Views in the other set are designed to enable searching descriptions to find concepts.

Views that Facilitate Selecting Concept Descriptions

For each snapshot view the SNOMED CT example database includes four views designed for selecting specific sets of descriptions for one or more specified concepts. The characteristics of each of these views are shown in Table 4 and a general template for the SQL definitions of these views is shown in Template 1. To create each of the views named in the table, the placeholders for {typeId} and {acceptabilityId} need to be replaced with values in the Specific Settings column of the table.

Example 1 demonstrates the use of these views to show all the active descriptions of a specified concept that are acceptable or preferred according to the language reference set referenced by the configuration file.

Table 4: Composite Description Views for Selecting Concept Descriptions

	Description	Specific Setting - {typeId}	Specific Setting - {acceptabilityId}
snap_fsn	This view selects the fully specified name of a concept (identified by conceptId)	= 900000000000003001	= 900000000000548007
snap_pref	This view selects the preferred synonym of a concept (identified by conceptId)	= 900000000000013009	= 900000000000548007
snap_syn	This view selects other acceptable synonyms of a concept (identified by conceptId)	= 900000000000013009	= 900000000000549004
snap_synall	This view selects all synonyms of a concept (identified by conceptId)	= 900000000000013009

Template 1: Description Selection Composite View Template

CREATE VIEW `snap_{name}` AS
(SELECT `d`.* FROM `snap_description` `d`
    JOIN `snap_refset_Language` `rs` ON `d`.`id` = `rs`.`referencedComponentId`
    JOIN `config_settings` `cfg` ON `rs`.`refSetId` = `cfg`.`languageId`
    WHERE `d`.`active` = 1 AND `d`.`typeId` {typeId}
    AND `rs`.`active` = 1 AND `rs`.`acceptabilityId` {acceptabilityId}
    AND `cfg`.`id`=0);

Example 1: Selecting Description Data for a Concept

SQL Query

Select conceptId,'FSN',id,term from snap_fsn where conceptId=95570007
UNION
Select conceptId,'Pref',id,term from snap_pref where conceptId=95570007
UNION
Select conceptId,'Syn',id,term from snap_syn where conceptId=95570007;

Results

conceptId	FSN	id	term
95570007	FSN	839752010	Kidney stone (disorder)
95570007	Pref	158296018	Kidney stone
95570007	Syn	158297010	Renal stone
95570007	Syn	158298017	Nephrolith
95570007	Syn	158299013	Renal calculus
95570007	Syn	512193015	Calculus of kidney
95570007	Syn	512194014	Nephrolithiasis
95570007	Syn	512195010	Kidney calculus

Views that Facilitate Searching for Concepts

For each snapshot view the SNOMED CT example database includes two views that are designed to facilitate searches for concepts associated with terms that match specified criteria. These views require both the description and the associated concept to be active in the chosen snapshot. . The specific characteristics of these views is shown in Table 5 and a general template for the SQL definitions of these views is shown in Template 2. To create the each of the views named in the table, the placeholder for {typeId} needs to be replaced with values in the Specific Settings column of the table.

Example 2 shows a query that searches the snap_syn_search_active view using MySQL's boolean full text search. This search method requires all the words preceded by + (plus) to be included and excludes all words preceded by - (minus). The results are shown together with the fully specified name of the concept (looked up using the snap_fsn view). Although this is not a user-friendly way to specify a searches, the example SQL code illustrates the technical power of this search technique. Other search techniques can also be applied to the search views and additional options for enhancing searches are discussed in Search Procedures.

Table 5: Composite Description Views that Facilitate Searching for Concepts

	Description	Specific Settings - {typeId}
snap_syn_search_active	This view includes active preferred and acceptable synonyms of active concepts. It excludes fully specified names and also excludes all descriptions associated with concepts that are inactive in the specified snapshot.	= 900000000000013009
snap_term_search_active	This view includes active preferred and acceptable synonyms of active concepts.	I

Template 2: Description Search Composite View Template

CREATE VIEW `snap_syn_search_active` AS
(SELECT `d`.*,`rs`.`acceptabilityId` FROM `snap_description` `d`
    JOIN `snap_refset_Language` `rs` ON `d`.`id` = `rs`.`referencedComponentId`
    JOIN `snap_concept` `c` ON `c`.`id` = `d`.`conceptId`
    JOIN `config_settings` `cfg` ON `rs`.`refSetId` = `cfg`.`languageId`
    WHERE `d`.`active` = 1 AND `d`.`typeId` {typeId}
    AND `rs`.`active` = 1
    AND `c`.`active` = 1
    AND `cfg`.`id`=0);

Usage Example

Example 2: Searching Terms to Find Concepts

SQL Query

SELECT `s`.`conceptId`,`s`.`term` 'matching term',`f`.`term` `FSN` FROM `snap_syn_search_active` `s`
    JOIN `snap_fsn` `f` ON `f`.`conceptId`=`s`.`conceptId`
    WHERE MATCH (`s`.`term`)
            AGAINST ('+acute +anterior +myocardial +infarction -ecg -old -ekg' IN BOOLEAN MODE) ORDER BY length(`f`.`term`),length(`s`.`term`);

Result

conceptId	matching term	FSN
54329005	Acute anterior myocardial infarction	Acute myocardial infarction of anterior wall (disorder)
54329005	Acute myocardial infarction of anterior wall	Acute myocardial infarction of anterior wall (disorder)
703164000	Acute anterior ST segment elevation myocardial infarction	Acute ST segment elevation myocardial infarction of anterior wall (disorder)
703164000	Acute STEMI (ST elevation myocardial infarction) of anterior wall	Acute ST segment elevation myocardial infarction of anterior wall (disorder)
703164000	Acute ST segment elevation myocardial infarction of anterior wall	Acute ST segment elevation myocardial infarction of anterior wall (disorder)
703252002	Acute myocardial infarction of anterior wall involving right ventricle	Acute myocardial infarction of anterior wall involving right ventricle (disorder)
703252002	Acute myocardial infarction of anterior wall with right ventricular involvement	Acute myocardial infarction of anterior wall involving right ventricle (disorder)
703165004	Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle	Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle (disorder)
703165004	Acute anterior ST segment elevation myocardial infarction with right ventricular involvement	Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle (disorder)
703165004	Acute STEMI (ST elevation myocardial infarction) of anterior wall with right ventricular involvement	Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle (disorder)
285981000119103	Acute ST segment elevation myocardial infarction involving left anterior descending coronary artery	Acute ST segment elevation myocardial infarction involving left anterior descending coronary artery (disorder)

Composite Subtype Hierarchy Views

Composite subtype hierarchy views enable selection of supertypes and subtypes of specified concepts accompanied by human-readable terms for each the selected concepts. The hierarchy views include supertype parents, subtype children, supertype ancestors and subtype descendants of specified concepts. More specialized views are also included to list the proximal primitive parents of a specified concepts and to list concepts that share a specified proximal primitive parent.

Supertype Parent and Subtype Child Views

For each snapshot view the SNOMED CT example database includes two views that select the supertype parents of a specified concept and two views that select the subtype children of a specified concept. These views select the id and either the fully specified name or preferred synonym of each parent or child concept. The characteristics of each of these views are shown in Table 6 and a general template for the SQL definitions of these views is shown in Template 3. To create each of the views named in the table, the placeholders for {termtype} need to be replaced with values in the Specific Settings column of the table.

Example 3 demonstrates the use of these views to select the id and preferred term for a specified concept and all of its supertype parents and subtype children.

Table 6: Composite Views of Supertype Parents and Subtype Children

	Description	Specific Settings - {termtype}
snap_rel_parent_fsn	Selects the id and fully specified name of each supertype parent of a concept specified by conceptId.	fsn
snap_rel_parent_pref	Selects the id and preferred synonym of each supertype parent of a concept specified by conceptId.	pref
snap_rel_child_fsn	Selects the id and fully specified name of each subtype child of a concept specified by conceptId.	fsn
snap_rel_child_pref	Selects the id and preferred synonym of each subtype child of a concept specified by conceptId.	pref

Template 3: SQL Templates for Composite Views of Supertype Parents and Subtype Children

-- Supertype Parent View
CREATE VIEW `snap_rel_parent_{termtype}` AS
    SELECT `r`.`targetId` AS `id`, `d`.`term` AS `term`, `r`.`sourceId` AS `conceptId`
    FROM `snap_relationship` `r` JOIN `snap_{termtype}` `d` ON `r`.`targetId` = `d`.`conceptId`
    WHERE `r`.`active` = 1  AND `r`.`typeId` = 116680003;
-- Subtype Child View: Differences are `sourceId` changed to `targetId` and `targetId` changed to `sourceId` as shown below
CREATE VIEW `snap_rel_child_{termtype}` AS
    SELECT `r`.`sourceId` AS `id`, `d`.`term` AS `term`, `r`.`destinationId` AS `conceptId`
    FROM `snap_relationship` `r` JOIN `snap_{termtype}` `d` ON `r`.`sourceId` = `d`.`conceptId`
    WHERE `r`.`active` = 1  AND `r`.`typeId` = 116680003;

Example 3: Selecting Supertype Parents and Subtype Children

SQL Query

Select "Concept", conceptid, term from snap_pref where conceptId=6025007
UNION
Select "Supertype Parent", id, term from snap_rel_parent_pref where conceptId=6025007
UNION
Select "Subtype Child", id, term from snap_rel_child_pref where conceptId=6025007;

Result

Concept	id	term
Concept	6025007	Laparoscopic appendectomy
Supertype Parent	51316009	Laparoscopic procedure
Supertype Parent	80146002	Appendectomy
Supertype Parent	264274002	Endoscopic operation
Supertype Parent	440588003	Endoscopic procedure on appendix
Subtype Child	174041007	Laparoscopic emergency appendectomy
Subtype Child	307581005	Laparoscopic interval appendectomy
Subtype Child	708876004	Robot assisted laparoscopic appendectomy

Transitive Closure Views of Supertype Ancestors and Subtype Descendants

For each snapshot view the SNOMED CT example database includes two views that select the supertype ancestors of a specified concept and two views that select the subtype descendants of a specified concept. These views select the id and either the fully specified name or preferred synonym of each ancestor or descendant concept. The characteristics of each of these views are shown in Table 7 and a general template for the SQL definitions of these views is shown in Template 4. To create each of the views named in the table, the placeholders for {termtype} need to be replaced with values in the Specific Settings column of the table.

Example 4 demonstrates the use of these views to select the id and preferred term for a specified concept and all of its supertype ancestors and subtype descendants.

Table 7: Transitive Closure Views of Supertype Ancestors and Subtype Descendants

	Description	Specific Settings - {termtype}
snap_tc_ancestor_fsn	Selects the id and fully specified name of each supertype ancestor of a concept specified by conceptId.	fsn
snap_tc_ancestor_pref	Selects the id and preferred synonym of each supertype ancestor of a concept specified by conceptId.	pref
snap_tc_descendant_fsn	Selects the id and fully specified name of each subtype descendant of a concept specified by conceptId.	fsn
snap_tc_descendant_pref	Selects the id and preferred synonym of each subtype descendant of a concept specified by conceptId.	pref

Template 4: SQL Templates for Composite Views of Supertype Ancestors and Subtype Descendants

-- Supertype Ancestor View: Differences are `sourceId` changed to `targetId` and `targetId` changed to `sourceId` as shown below
CREATE VIEW `snap_tc_ancestor_{termtype}` AS
(SELECT `r`.`supertypeId` `id`,`d`.`term` `term`,`r`.`subtypeId` `conceptId`
    FROM  `snap_transclose` `r`
    JOIN `snap_{termtype}` `d` ON (`r`.`supertypeId` = `d`.`conceptId`));
 
 
--- Subtype Descendant View
CREATE VIEW `snap_tc_descendant_{termtype}` AS
(SELECT `r`.`subtypeId` `id`,`d`.`term` `term`,`r`.`supertypeId` `conceptId`
    FROM  `snap_transclose` `r`
    JOIN `snap_{termtype}` `d` ON (`r`.`subtypeId` = `d`.`conceptId`));

Example 4: Selecting Supertype Ancestors and Subtype Descendants

SQL Query

Select "Concept", conceptid, term from snap_pref where conceptId=16001004
UNION
Select "Ancestor", id, term from snap_tc_ancestor_pref where conceptId=16001004
UNION
Select "Descendant", id, term from snap_tc_descendant_pref where conceptId=16001004;

Result

Concept	conceptId	term
Concept	16001004	Otalgia
Ancestor	22253000	Pain
Ancestor	102957003	Neurological finding
Ancestor	106147001	Sensory nervous system finding
Ancestor	118234003	Finding by site
Ancestor	118236001	Ear and auditory finding
Ancestor	118254002	Finding of head and neck region
Ancestor	138875005	SNOMED CT Concept
Ancestor	247234006	Ear finding
Ancestor	276435006	Pain / sensation finding
Ancestor	279001004	Pain finding at anatomical site
Ancestor	297268004	Ear, nose and throat finding
Ancestor	301354004	Pain of ear structure
Ancestor	301857004	Finding of body region
Ancestor	404684003	Clinical finding
Ancestor	406122000	Head finding
Ancestor	699697007	Finding of sensation by site
Descendant	12336008	Referred otalgia
Descendant	74123003	Otogenic otalgia
Descendant	162356005	Earache symptoms
Descendant	162359003	Bilateral earache
Descendant	430879002	Posterior auricular pain
Descendant	1084561000119106	Bilateral referred otalgia of ears
Descendant	1089561000119107	Referred otalgia of left ear
Descendant	1092171000119100	Referred otalgia of right ear

Proximal Primitive Parent Views

For each snapshot view the SNOMED CT example database includes two views that select the proximal primitive parents of a specified concept and two views that select the subtype children of a specified concept. These views select the id and either the fully specified name or preferred synonym of each parent or child concept. The characteristics of each of these views are shown in Table 8 and a general template for the SQL definitions of these views is shown in Template 5. To create each of the views named in the table, the placeholders for {viewtype} need to be replaced with values in the Specific Settings column of the table.

Example 5 demonstrates the use of these views to select the id and preferred term for a specified concept and its proximal primitive parents. Example 6 demonstrates the use of these views to select the id and preferred term for a specified concept and all the concepts that have this concept as a proximal primitive parent.

Table 8: Views of Proximal Primitive Supertype Ancestors and Concepts with a Specific Proximal Primitive Ancestor

	Description	Specific Settings - {viewtype}
snap_pp_parent_fsn	Selects the id and fully specified name of each proximal primitive parent of a concept specified by conceptId.	fsn
snap_pp_parent_pref	Selects the id and preferred synonym of each proximal primitive parent of a concept specified by conceptId.	pref
snap_pp_child_fsn	Selects the id and fully specified name of each concept with a proximal primitive parent specified by conceptId.	fsn
snap_pp_child_pref	Selects the id and preferred synonym of each concept with a proximal primitive parent specified by conceptId.	pref

Template 5: SQL Templates for Proximal Primitive Supertype Views

-- Proximal primitive parents of a specified concept
CREATE VIEW `snap_pp_parent_{viewtype}` AS
(SELECT `r`.`supertypeId` `id`,`d`.`term` `term`,`r`.`subtypeId` `conceptId`
    FROM  `snap_proximal_primitives` `r`
    JOIN `snap_{viewtype}` `d` ON (`r`.`supertypeId` = `d`.`conceptId`));
 
 
-- Concepts with a specified proximal primitive parent concept
CREATE VIEW `snap_pp_child_{viewtype}` AS
(SELECT `r`.`subtypeId` `id`,`d`.`term` `term`,`r`.`supertypeId` `conceptId`
    FROM  `snap_proximal_primitives` `r`
    JOIN `snap_{viewtype}` `d` ON (`r`.`subtypeId` = `d`.`conceptId`));

Example 5: Selecting Proximal Primitive Parents of a Concept

SQL Query

--
Select "Concept", conceptid, term from snap_pref where conceptId=21522001
UNION
Select "Proximal Primitive Parent", id, term from snap_pp_parent_pref where conceptId=21522001;

Result

Concept	conceptId	term
Concept	21522001	Abdominal pain
Proximal Primitive Parent	22253000	Pain

Example 6: Selecting Concepts with a Specified Proximal Primitive Parent

SQL Query

Select "Concept", conceptid, term from snap_pref where conceptId=22253000
UNION
Select "Concept with PP-Parent: 21522001|Pain|", id, term from snap_pp_child_pref where conceptId=22253000;

Result

Concept	conceptId	term
Concept	22253000	Pain
Concept with PP-Parent: 21522001 |Pain|	4448006	Allergic headache
Concept with PP-Parent: 21522001 |Pain|	4568003	Retrosternal pain
Concept with PP-Parent: 21522001 |Pain|	6561007	Pain in urethra
Concept with PP-Parent: 21522001 |Pain|	10601006	Pain in lower limb
Concept with PP-Parent: 21522001 |Pain|	12584003	Bone pain
Concept with PP-Parent: 21522001 |Pain|	15803009	Bladder pain
Concept with PP-Parent: 21522001 |Pain|	16513000	Postcordotomy pain
Concept with PP-Parent: 21522001 |Pain|	18876004	Pain in finger
Concept with PP-Parent: 21522001 |Pain|	20793008	Scapulalgia
Concept with PP-Parent: 21522001 |Pain|	21522001	Abdominal pain
Concept with PP-Parent: 21522001 |Pain|	21545007	Tenalgia
Concept with PP-Parent: 21522001 |Pain|	29857009	Chest pain
Concept with PP-Parent: 21522001 |Pain|	30473006	Pain in pelvis
Concept with PP-Parent: 21522001 |Pain|	30989003	Knee pain

... total of 240 rows returned ...

Composite Relationship Views

Defining Relationship View

For each snapshot view the SNOMED CT example database there are two view of the active, inferred defining relationships. The views select the id and either the fully specified name or preferred synonym for the concept identified in each of the defining columns (sourceId, typeId and destinationId). The characteristics of these views are shown in Table 9 and a general template for the SQL definitions of these views is shown in Template 6. To create each of the views named in the table, the placeholders for {termtype} need to be replaced with values in the Specific Settings column of the table.

Example 7 demonstrates the use of these views to select the active defining relationships of a specified concept with the id and preferred term for each of the referenced concepts.

Table 9: Composite Views of Supertype Parents and Subtype Children

	Description	Specific Settings - {termtype}
snap_rel_def_fsn	. It selects the id and fully specified name for each of the concept identifiers (sourceId, typeId and destinationId) and the relationshipGroup number.	fsn
snap_rel_def_pref	. It selects the id and fully specified name for each of the concept identifiers (sourceId, typeId and destinationId) and the relationshipGroup number.	pref

Template 6: SQL Templates for Composite Views of Defining Relationships

CREATE VIEW `snap_rel_def_{viewtype}` AS
(SELECT `r`.`sourceId` `sourceId`,`src`.`Term` `sourceTerm`,`r`.`typeId` `typeId`,`typ`.`Term` `typeTerm`,`r`.`destinationId` `destinationId`,`dest`.`Term` `destinationTerm`,`r`.`relationshipGroup` `relationshipGroup`
    FROM (((`snap_relationship` `r`
    JOIN `snap_{viewtype}` `src` ON ((`r`.`sourceId` = `src`.`conceptId`))) JOIN `snap_{viewtype}` `typ` ON ((`r`.`typeId` = `typ`.`conceptId`))) JOIN `snap_{viewtype}` `dest` ON ((`r`.`destinationId` = `dest`.`conceptId`))) WHERE ((`r`.`active` = 1) AND (`r`.`characteristicTypeId` = 900000000000011006)));

Example 7: Selecting Supertype Parents and Subtype Children

SQL Query

Select * from snap_rel_def_pref where sourceId=6025007;

Result

sourceId	sourceTerm	typeId	typeTerm	destinationId	destinationTerm	relationshipGroup
6025007	Laparoscopic appendectomy	116680003	Is a	51316009	Laparoscopic procedure	0
6025007	Laparoscopic appendectomy	116680003	Is a	80146002	Appendectomy	0
6025007	Laparoscopic appendectomy	116680003	Is a	264274002	Endoscopic operation	0
6025007	Laparoscopic appendectomy	116680003	Is a	440588003	Endoscopic procedure on appendix	0
6025007	Laparoscopic appendectomy	260686004	Method	129304002	Excision - action	1
6025007	Laparoscopic appendectomy	405813007	Procedure site - Direct	66754008	Appendix structure	1
6025007	Laparoscopic appendectomy	425391005	Using access device	86174004	Laparoscope	1

Composite Historical Views

Inactive Concept Views

For each delta and snapshot view the SNOMED CT example database includes a view of inactive concepts. The characteristics of each of these views are shown in Table 10 and a general template for the SQL definitions of these views is shown in Template 7.

Example 8 demonstrates the use of these views to show all the active descriptions of a specified concept that are acceptable or preferred according to the language reference set referenced by the configuration file.

Table 10: Composite Views of Inactive Concepts with Related Concept Inactivation and Historical Association Refset Data

Name

Description

delta_inactive_concepts

This view selects details of concepts that are inactive in the chosen delta or snapshot view. In addition to the concept id the fully specified name of the inactive concept is selected. The output of this view also includes the reason for activation and any historical associations between this inactive concept and an active concept. The reason for inactivation is shown as the preferred synonym for the concept representing the reason for inactivation in the concept inactivation reference set. The historical association is represented by the preferred synonym of the association reference set(s) and the fully specified name of the associated target concept. Where a concept has multiple active associations each of these reported as a separate row (the inactive concept and inactivation reason data is duplicated on each of these rows).

Template 7: SQL Definition of the Inactive Concepts View

CREATE VIEW delta_inactive_concepts AS
select `c`.`id`, `c`.`effectiveTime`, `c`.`active`, `c`.`definitionStatusId`, `cf`.`term` 'FSN',
    `vp`.`term` 'reason', `arp`.`term` 'assoc_type', `atf`.`id` 'ref_conceptId', `atf`.`term` 'ref_concept_FSN'
from `delta_concept` `c`
left join `snap_fsn` `cf` ON `cf`.`conceptid`=`c`.`id`
left outer join `snap_refset_attributevalue` `v` on `v`.`referencedComponentId`=`c`.`id`
     and `v`.`refsetId`=900000000000489007 and `v`.`active`=1
left outer join `snap_pref` `vp` on `vp`.`conceptid`=`v`.`valueid`
left outer join `snap_refset_association` `a` on `a`.`referencedComponentId`=`c`.`id` and `a`.`refsetId` IN
    (900000000000528000, 900000000000523009, 900000000000527005, 900000000000526001,
     900000000000525002, 900000000000531004, 900000000000524003, 900000000000530003) and `a`.`active`=1
left outer join `snap_pref` `arp` on `arp`.`conceptid`=`a`.`refsetId`
left outer join `snap_fsn` `atf` on `atf`.`conceptid`=`a`.`targetComponentId`
where `c`.`active`=0
order by `c`.`id`;

Example 8: Selecting Inactive Concepts with Related Concepts Inactivation and Historical Association Refset Data

SQL Query

SELECT * FROM delta_inactive_concepts;

Result (example rows only)

id	effectiveTime	active	definitionStatusId	FSN	reason	assoc_type	ref_conceptId	ref_concept_FSN
1192004	20190731	0	900000000000074008	Familial amyloid neuropathy, Finnish type (disorder)	Outdated	REPLACED BY	3757892013	Hereditary gelsolin amyloidosis (disorder)
1230003	20190731	0	900000000000074008	No diagnosis on Axis I (finding)	Outdated	REPLACED BY	677781011	Psychological finding (finding)
1427008	20190731	0	900000000000074008	Intraspinal abscess (disorder)	Duplicate	SAME AS	743297013	Spinal cord abscess (disorder)
2461007	20190731	0	900000000000074008	Tennis elbow test (procedure)	Ambiguous	POSSIBLY EQUIVALENT TO	3777085015	Lateral epicondylitis test (procedure)
2900003	20190731	0	900000000000074008	Hyperplasia of renal artery (disorder)	Ambiguous	POSSIBLY EQUIVALENT TO	3760067011	Fibromuscular dysplasia of wall of renal artery (disorder)
3105002	20190731	0	900000000000074008	Intron (finding)	Outdated	REPLACED BY	697643016	Finding related to molecular sequence data (finding)
3221003	20190731	0	900000000000074008	Ringer's solution (product)	Nonconformance to editorial policy component
3734003	20190731	0	900000000000074008	Split thickness skin graft (procedure)	Ambiguous	POSSIBLY EQUIVALENT TO	3758568011	Split thickness graft of skin to skin (procedure)
4101004	20190731	0	900000000000074008	Revision of spinal pleurothecal shunt (procedure)	Ambiguous	POSSIBLY EQUIVALENT TO	618942015	Revision of spinal subarachnoid shunt (procedure)
4101004	20190731	0	900000000000074008	Revision of spinal pleurothecal shunt (procedure)	Ambiguous	POSSIBLY EQUIVALENT TO	618681017	Revision of subdural-pleural shunt (procedure)
4131005	20190731	0	900000000000074008	Implantation into pelvic region (procedure)	Ambiguous	POSSIBLY EQUIVALENT TO	2968044014	Procedure on pelvic region of trunk (procedure)
4131005	20190731	0	900000000000074008	Implantation into pelvic region (procedure)	Ambiguous	POSSIBLY EQUIVALENT TO	3756616019	Implantation procedure (procedure)
4518006	20190731	0	900000000000074008	Buthenal (substance)	Ambiguous	POSSIBLY EQUIVALENT TO	796984014	Crotonaldehyde (substance)
4919007	20190731	0	900000000000074008	Congenital protrusion (morphologic abnormality)	Duplicate	SAME AS	642112018	Protrusion (morphologic abnormality)
5034009	20190731	0	900000000000074008	Graft to hair-bearing skin (procedure)	Duplicate	SAME AS	3757739014	Hair bearing graft of skin to skin (procedure)

Inactive Descriptions

For each delta and snapshot view the SNOMED CT example database includes a view of inactive descriptions. The characteristics of each of these views are shown in Table 11 and a general template for the SQL definitions of these views is shown in Template 8.

Example 9 demonstrates the use of these views to show all the active descriptions of a specified concept that are acceptable or preferred according to the language reference set referenced by the configuration file.

Table 11: Composite Views of Inactive Descriptions with Related Description Inactivation and Historical Association Refset Data

Name

Description

delta_inactive_descriptions

This view selects details of all descriptions that are inactive in the chosen delta or snapshot view. In addition to selecting the description data it also includes the active fully specified name of the related concept and the reason for activation. The reason for inactivation is shown as the preferred synonym for the concept representing the reason for inactivation in the description inactivation reference set.

Template 8: SQL Definition of the Inactive Descriptions View

CREATE VIEW delta_inactive_descriptions AS
select `d`.`id`, `d`.`effectiveTime`, `d`.`active`, `d`.`conceptid`, `d`.`term` 'term',
    `df`.`term` 'concept_fsn', `c`.`active` 'concept_active' ,`vp`.`term` 'reason'
from `delta_description` `d`
left outer join `snap_fsn` `df` ON `df`.`conceptid`=`d`.`conceptid`
join `snap_concept` `c` ON `c`.`id`=`d`.`conceptid`
left outer join `snap_refset_attributevalue` `v` on `v`.`referencedComponentId`=`d`.`id`
    and `v`.`refsetId`=900000000000490003 and `v`.`active`=1
left outer join `snap_pref` `vp` on `vp`.`conceptid`=`v`.`valueid`
where `d`.`active`=0
order by `d`.`id`;

Example 9: Selecting Inactive Descriptions with Related Description Inactivation Refset Data

SQL Query

SELECT * FROM delta_inactive_descriptions;

Result

id	effectiveTime	active	conceptid	term	concept_fsn	concept_active	reason
14132019	20190731	0	7938006	D-Arabinitol dehydrogenase	D-arabinitol 4-dehydrogenase (substance)	1	Nonconformance to editorial policy component
16101018	20190731	0	9156001	Embryo stage 1	Structure of embryo at stage 1 (body structure)	1	Nonconformance to editorial policy component
16837014	20190731	0	9631008	Rheumatoid spondylitis	Ankylosing spondylitis (disorder)	1	Not semantically equivalent component
17234017	20190731	0	9871000	D-Amino-acid acetyltransferase	D-amino-acid N-acetyltransferase (substance)	1	Nonconformance to editorial policy component
17525014	20190731	0	10043003	D-Alanine-alanyl-poly(glycerolphosphate) ligase	D-alanine-alanyl-poly(glycerolphosphate) ligase (substance)	1	Nonconformance to editorial policy component
17526010	20190731	0	10043003	D-Alanyl-alanyl-poly(glycerolphosphate)synthetase	D-alanine-alanyl-poly(glycerolphosphate) ligase (substance)	1	Nonconformance to editorial policy component
17527018	20190731	0	10043003	D-Alanine:membrane-acceptor ligase	D-alanine-alanyl-poly(glycerolphosphate) ligase (substance)	1	Nonconformance to editorial policy component
17615010	20190731	0	10093004	Anisakiasis due to Anisakis simplex	Anisakiasis caused by larva of Anisakis simplex (disorder)	1	Erroneous
20220015	20190731	0	11702002	bis-(p-Chlorophenyl) ethanol	Bis-(p-chlorophenyl) ethanol (substance)	1	Nonconformance to editorial policy component
20469015	20190731	0	11860003	Nannizzia	Genus Arthroderma (organism)	1	Not semantically equivalent component