SQL Practical Guide

Search

SNOMED CT Document Library

    Loading...

Page tree

  

The previous section described queries that can be applied to database tables to access snapshot and delta views of individual database tables. However, meaningful access to SNOMED CT requires querying of interrelated data in multiple database tables including concepts, descriptions, relationships and reference sets. In most cases, the data from each of those tables needs to be from a snapshot view for the same date. This implies these composite need to derive information from snapshot queries applied to the table rather than directly accessing the table. Consequently, what should be relatively simple queries rapidly become complex, making them hard to understand and prone to errors.

Fortunately, relational databases provide solutions that reduce this complexity by allowing a query to be used to define a database view. These defined views can then be queried in exactly the same way as a database table. The result of this is that if all the required snapshot and delta views are defined for every table in a SNOMED CT database, it is possible to write relatively simple queries that return useful combinations of data from snapshot views of different tables.

Representing Snapshots as Database Views

The examples below two snapshot database views of a full release table. The first is current snapshot view and the second a is snapshot for 2019-01-31.

Creating a Current Snapshot View from the Full Concept Table
CREATE VIEW snap_concept as (select * from full_concept tbl
	where tbl.effectiveTime = (select max(sub.effectiveTime) from full_concept sub 
								where sub.id = tbl.id));
Creating a Retrospective Snapshot View for 2019-01-31 from the Full Concept Table
CREATE VIEW snap20190131_concept as (select * from full_concept tbl
	where tbl.effectiveTime = (select max(sub.effectiveTime) from full_concept sub 
								where sub.id = tbl.id and sub.effectiveTime<='20190131');

Having created those two views it is then possible to write queries like the examples below to display data from these snapshot views.

Creating a Current Snapshot View from the Full Concept Table
select * from snap_concept where id in (3859001,3704008);
Creating a Retrospective Snapshot View for 2019-01-31 from the Full Concept Table
select * from snap20190131_concept where id in (3859001,3704008);

Comparing the results of these two queries shows that in January 2019 both concepts changed in the 2019-07-31 release. The concept with id 3859001 was made inactive and the definitionStatusId of concept with id 3704008 was changed from 900000000000074008 (primitive) to 900000000000073002 (defined).

Creating Configurable Snapshot Views

It is possible to create snapshot views of every release file for every SNOMED CT release of SNOMED CT. However, this would result in over 600 distinct snapshot table views, most of which would rarely be used. A more practical solution is to create views for the current snapshot and two or three retrospective views with a configurable snapshot date. The SNOMED CT example database includes two retrospective snapshot views with a snapshotTime value in an identified row in a configuration table specifying the snapshot date.

The definition of one of these views is shown here. The other view (snap2_concept) has the same definition except that it specifies `cfg`.`id` = 2 rather than `cfg`.`id` = 1 so it refers to a different row in the configuration table.  Having two independently configurable snapshots, allows queries to be written than compare different snapshots. Because the same configurable views are provided for all full release tables, changes to the snapshotTime for a view apply simultaneously to all those components and reference set members.

Example of Configurable Retrospective View Definition
CREATE VIEW `snap1_concept` AS select `tbl`.`id` AS `id`,`tbl`.`effectiveTime` AS `effectiveTime`,`tbl`.`active` AS `active`,`tbl`.`moduleId` AS `moduleId`,`tbl`.`definitionStatusId` AS `definitionStatusId` from `full_concept` `tbl` where (`tbl`.`effectiveTime` = (select max(`sub`.`effectiveTime`) from (`full_concept` `sub` join `config_settings` `cfg`) where ((`sub`.`id` = `tbl`.`id`) and (`cfg`.`id` = 1) and (`sub`.`effectiveTime` <= `cfg`.`snapshotTime`))));

Creating Configurable Delta Views

The SNOMED CT example database also includes two configurable delta views. These are created and configured in the same way as the configurable snapshot views. However in this case, deltaStartTime and deltaEndTime values in the identified configuration table row specify the delta period.

Example of Configurable Retrospective View Definition
CREATE VIEW `delta1_concept` AS select `tbl`.`id` AS `id`,`tbl`.`effectiveTime` AS `effectiveTime`,`tbl`.`active` AS `active`,`tbl`.`moduleId` AS `moduleId`,`tbl`.`definitionStatusId` AS `definitionStatusId` from (`full_concept` `tbl` join `config_settings` `cfg`) where ((`cfg`.`id` = 1) and (`tbl`.`effectiveTime` <= `cfg`.`deltaEndTime`) and (`tbl`.`effectiveTime` > `cfg`.`deltaStartTime`));
Feedback
  • No labels