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.
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.
Having created those two views it is then possible to write queries like the examples below to display data from these snapshot views.
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).
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.
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.