Role of the Example Database

A SNOMED CT example database is used throughout this guide to illustrate ways to meet a various requirements for practical access to SNOMED CT. It is not essential for readers to create or have access to an instance of the example database but for some people access to a practical example of the database will be valuable.

Some readers of the guide may wish to setup and use a working version of this database to explore simply to provide themselves with another way to explore SNOMED CT. Others may use the example database to test out the efficacy and performance of the techniques described in the guide. Those with existing knowledge and experience of SQL may also wish to implement and test alternative approaches while reading this guide. Readers may also prefer to adapt the guidance in this document to another database environment with which they are already more familiar. Most of techniques used in the example database are standard features of SQL and that are supported by many relational database environments. Therefore, it should be fairly straight forward to apply or adapt the SQL examples in the guide to other database environments. On the other hand readers who are familiar with databases with more powerful capabilities than MySQL may see opportunities to use additional features to replace some techniques used in the example database.

Requirements for Creating the Database

• A computer system running Windows or MacOS or a Unix-based environment like Linux or Ubuntu for which MySQL Server 8.0.x if available.

• Installation of MySQL Community Edition Server (which is used to build and provide access to the database) and MySQL Community Edition Workbench (which provides a user interface through which the database can be conveniently explored). Both of these are freely available with standard installation packages for all the most widely used operating system environments include Windows, MacOS and other Unix-based environments such as Linux and Ubuntu.

• Those using Windows will also need to install a Perl processor, but fortunately the Strawberry Perl environment is also freely available, so there is no additional software to be purchased.

• At least 10.5 Gb of disk storage space during the build process. Assuming the release package archive file and the expanded release package folder are deleted the storage requirement reduces to 6.5Gb after the build has completed.

Those readers wishing to create their own instance of the example database should refer to . That appendix provides instructions on the steps required to configure MySql and then to create, populate and experiment with the example SNOMED CT database. These instructions are supported by installation scripts for Windows and MacOS, which have been tested to work with the standard installations of MySQL 8.0.x Community Server for those environments.

The SNOMED CT example database is designed to provide an effective and flexible way to access SNOMED CT content imported from a SNOMED CT release package. Access is provided through running SQL queries against tables containing the data and predefined database views that allow data to be selected in ways that recognize versioning information and where appropriate combine related data from different tables and views.

This playlist brings together all videos on how to import and query SNOMED CT using a MySQL database from the SNOMED International Implementation Course. The videos introduce key concepts, practical implementation considerations, and real-world examples to help you understand how SNOMED CT can be imported into a MySQL database.

Open playlist on YouTube:

👉 Click here to view the full playlist on YouTube

How to navigate the playlist in GitBook

When a YouTube playlist is embedded in GitBook, only one video is shown on the page at a time, but all playlist videos are still available.

• Use the forward () and back () arrows on the video player to move between videos in the playlist

• A.1 Download the SNOMED CT Example Database Package

• A.2 Download the Release File Package

• A.3 Instructions for Mac OS Users

• The project can be downloaded as a zip archive file .

• Unzip this file and rename the folder "mysql-loader-with-optimized-views" to "SnomedRfsMySql".

This section discusses some of the points that should be considered when designing a relational database to provide access to SNOMED CT. It explains the rationale for decisions taken while designing the SNOMED CT example database. It also identifies some other options which, while not implemented in the example database, are worth considering.

Before designing a database to accommodate SNOMED CT, you should refer to the . This provides the authoritative documentation about the way that SNOMED CT content is distributed to licensees. The list below picks out the key sections of this guide for those developing solutions that provide access to SNOMED CT.

The section contains the follow chapters that provide essentially information about SNOMED CT Release Packages

SNOMED CT Release Packages can be obtained from the Member Licensing & Distribution Service (MLDS).

• Download the latest SNOMED CT International Release Package (a zip archive) into your SnomedCT_ReleaseFiles folder.

  • On MacOS or other Unix based systems: /Users/your-username/SnomedCT_ReleaseFiles

  • On Windows systems: C:\SnomedCT_ReleaseFiles

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.

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.

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.

By using the transitive closure table very simple queries can:

Required MySQL Configuration Settings

This page describes and explains the required additional MySQL configuration settings for the SNOMED CT example database. For instructions on how to apply these settings see A.3 Set Required MySQL Configuration (MacOS) or A.4 Set Required MySQL Configuration (Windows).

The following MySQL settings are required for loading and using the SNOMED CT MySQL database.

The additional configuration settings required are specified in the following way in a file provided in the SnomedRfsMySql/cnf folder.

Content of additional configuration file: my_snomedserver.cnf

The following dialog should be displayed.

• Click the spanner symbol to the right of the MySQL Connections prompt.

When the dialog below opens:

Summary

This practical guide outlines key requirements for enabling effective access to SNOMED CT and illustrates some of the options for meeting these requirements using a freely available SQL database. The rationale for using SQL as an illustrative example is that this is a widely understood way to access structured data that can be readily applied to SNOMED CT release files.

The approaches and options documented in the guide provide working examples of some essential SNOMED CT terminology services. However, the primary purpose of these examples is to enhance understanding and not to recommend SQL as a way to deliver large scale versions of these services. A range of more advanced technologies, including those used in SNOMED International Tools, are able to deliver more scalable implementations of the required services.

Background

SNOMED CT is made available to licensees as a package of tab-delimited text files. The format of these files is specified in the Release File Specification (Sections and ). These release files provide a standard way to distribute SNOMED CT content and derivatives but they do not offer a direct way to provide user-friendly access to the terminology.

This guide documents a worked SQL example that loads the SNOMED CT release files into a database designed to enable practical access to terminology content. This worked example enables exploration of the types of terminology required for effective delivery of terminology services.

Purpose

The primary purpose of this document is to enhance understanding of the logical design and release file structure of SNOMED CT and to provide practical ways to access the key features of SNOMED CT in a widely understood programming language. For more scalable and performant approaches to implementing SNOMED CT please visit SNOMED International's Github repository - e.g. .

For more details on the purpose of the document see .

All data in SNOMED CT release files conforms to one of a set of data types defined in the Release File Specification (). The database tables that will hold release file data should be designed in ways that consistently map the general data type characteristics in the SNOMED CT specification to appropriate data types in the should be represented consistently using an appropriate data type supported by the database.

The table shows how each release file data type is represented in the MySQL example database.

This guide provides a simple and practical example of how a relational database can be used to enable effective access to the content and features of SNOMED CT. The guide includes tested SQL scripts for loading release files into a relational database, searching the terminology and querying the terminology for concepts that meet simple but useful constraints.

The primary purpose of this document is to enhance understanding of the logical design of SNOMED CT and to provide practical ways to access the key features of SNOMED CT in a widely understood programming language. For more scalable and performant approaches to implementing SNOMED CT please visit SNOMED International's Github repository - e.g. .

Objectives

The key objectives of this guide are to:

1. Enhance understanding of the logical design of SNOMED CT;

2. Demonstrate the feasibility of effective terminology access;

3. Enable terminology access for practical exercises;

4. Provide a starting point for further development.

There are three distinct target audiences for this guide:

1. Designers and developers

   • The guide provides practical examples of the ways in which features in SNOMED CT can be accessed using a widely understood technology;

   • It demonstrates services that designers and developers should consider including in their terminology server developments;

Each section of the guide includes an indication of its applicability to each of these audiences.

The guide is designed to be used in any of the following ways to suit the needs of different audiences.

1. As a set of step-by-step instructions for a practical demonstration project

2. As a source of ideas and advice to inform other development approaches

3. As a source of practical examples that illustrate and validate general guidance

To download SNOMED CT release files you must have a SNOMED CT Affiliate License. The table below provides step-by-step advice on how to obtain a license and register your license to enable you to download SNOMED CT release files.

Using Full and Snapshot Releases

Meeting Version Access Requirements

A key decision when designing any solution intended to provide to SNOMED CT is whether it should support import files from the full release, the current snapshot release or both. The determining factor when considering these options is the range of SNOMED CT versions that to which the solution needs to provide access. The table below identifies the release type import options that can be used to meet particular requirements for access to different sets of SNOMED CT versions. The release type options are summarized in the sections following this table.

Table: Release Type Import Options to Support Version Access Requirements

• A single set of tables is created for all the release files that need to be imported.

• Data is imported into these table from the current snapshot release.

• The database is optimized by appropriate additional indexes.

• Simple solution which performs well.

• Supports to access the current snapshot release and current delta view.

• No access to previous snapshot views.

• Cannot access to the previous state of components in the current delta view.

• Two sets of tables are created for all the release files that need to be imported.

• Data from the current snapshot release is imported into one of these sets of tables

• Data from the previous snapshot release is imported into the other set of tables.

• Simple duplication of the single snapshot view which performs well.

• Supports to access the current and previous snapshot releases and current and previous delta views.

• Supports access to previous state of components in the current delta view.

• No access to snapshot views prior to the previous version.

• Heavy use of disk space as a result of duplication of rows that are identical in both snapshots. Each snapshot takes more than 80% of the space required by the full release.

• Not extensible because this approach is not realistic for multiple versions due to a linear increase in redundant use of disk space.

• A single set of tables is created for all the release files that need to be imported.

• Data is imported into these table from the full release.

• The database is optimized by appropriate additional indexes.

• Access to the complete release history of all versions of a SNOMED CT Edition.

• Efficient use of disk space requiring only 20% more disk space than importing a single snapshot release.

• Able to access snapshot views for any date.

• Virtual views performs less well than a native database tables because the content of a view is the result of query on a database table.

• Two set of tables are created for all the release files that need to be imported.

• Data is imported into these table from the full release and current snapshot release.

• The database is optimized by appropriate additional indexes.

• Access to the complete release history of all versions of a SNOMED CT Edition.

• High performance access to the current snapshot view using the snapshot release table rather than a virtual snapshot view.

  • This is a significant advantage as the current snapshot is the most commonly used view.

• Requires approximately 80% more disk space than only importing the full release.

  • However, unlike the use of multiple snapshot tables. this disk space increase with each new release is determined by numbers of additions and changes rather and the only redundancy is a single version snapshot.

There is no need to import the delta release when importing either the full or snapshot releases. This is because rows that have changed in a release can be identified using their effectiveTimes. So it is possible to extract the most recent delta view from a snapshot release and all delta views can be extracted from a full release.

There is one potential use case for importing a delta release and that is to update a database containing the previous release to current release. A full release table for a specified previous release date can be updated by simply inserting all the rows from a the relevant delta release file for the period starting immediately after the previous release date up to and including a new release date.

In practice, it may be more efficient to start with an empty database and import the latest full release. However, the delta release update option may be useful where there is requirement to retain the operational integrity of the database during the update process. Future plans for more frequent or "continuous" updates to the SNOMED CT International Edition may also benefit from updates using delta releases of small number of changes or additions.

Every SNOMED CT release file conforms to a formal specification which defines the names and data types of each of the columns in the file. These specifications are subsections of the .

The preceding chapters addressed the representation of SNOMED CT data types in a database and naming the individual tables into which release data will be loaded. This section considers two remaining design decisions related to database table design, names to be applied to the columns in the tables and keys and indexes that should be added to support speedy access to the data.

Introduction

The International Edition of SNOMED CT contains three release types (Full, Snapshot and Delta) and each of these release types include 20 files (2019-07-31 release). Each of those files has a distinct structure representing either a type of component (e.g. concept, relationship or description) or a type of reference set. SNOMED CT Extensions contain additional files and most of these conform to the same structure as one of the International Edition release files.

When designing a database to accommodate SNOMED CT release files, decisions need to be made about the names to give to each of the database tables. One option is to give the tables exactly the same names as the release files they represent. However, analysis of the release file naming conventions indicates that these conventions are not directly applicable to table names.

SNOMED CT release file naming conventions include some elements that represent information about the provenance, language and release date of a specific file. This information is useful and in some cases essential as a way of distinguishing releases files. However, this information is neither essential nor helpful when naming tables that may contain data from different SNOMED CT versions, editions and extensions.

The release file naming conventions do however include some essential elements that relate directly to the specification of the nature and structure of the data they contain. The following sections provide a summary of the release file naming conventions, identify the elements in release file names that are relevant to database table naming and describe a set rules that can be applied to derive consistent table names from release file names.

All SNOMED CT release file are named in accordance with the . The naming conventions result in names that can be decomposed into parts as illustrated by examples with color coding in the table below.

Table 1: Illustrations of the Release File Naming Conventions

The table below identifies the elements of the release file naming pattern that are relevant to the naming of the database tables containing content from those files. It also outlines the reasons why some elements that form an important part of the release file names can or should be omitted from the relevant database table names.

Table 2: Relevance of File Name Pattern Elements to Database Table Names

The analysis in Table 2, identifies three elements in the release file name that are relevant to table names. There are various ways in which table names could be derived by combining these elements and one of these is shown in Table 3. The end result (shown in Table 4) is a set of table names that:

• Are as short as possible while clearly identifying:

  • The release type from which they are derived

  • The component or reference set type specification to which they conform

Table 3: Rules Applied to Release File Names to Generate Table Name for the Example Database

Table 4: Results of Mapping Release File Names to Example Database Table Names

SNOMED CT release packages include the following distinct representations of the terminology content.

• A full release, which is a release type in which the release files contain every version of every component and reference set member ever released.

• A snapshot release, which is a release type in which the release files contain only the most recent version of every component and reference set member released, as at the release date.

The instructions in this section are specific to user of Mac OS systems.

Users of Window systems should skip to .

MySQL Installation (MacOS)

Install MySQL Community Server

• Download and install the DMG Archive version of the MySQL Community Server.

• During the installation process you will be prompted to provide a password for the MySQL server root account.

  • Make a note of the root password - you will need to use it to load the SNOMED CT release package.

• Select the Legacy Authentication Method unless you will be using the database for other purposes that require greater security.

• Download and install the DMG Archive version of the .

Type the command lines shown below into the terminal window.

• You have now completed the configuration process.

If MySQL is running the dots are green (as shown above).

• Click Stop MySQL Server and wait for the dots to turn red (as shown below).

  • You will be prompted for your Mac password to confirm this action.

When the dots are red (as above) MySQL is not running

• Click Start MySQL Server and wait for the dots to turn green.

  • You will be prompted for your Mac password to confirm this action.

You must change directories to the SnomedRfsMySql folder before starting the loader script.

As shown above you must be in the SnomedRfsMySql folder to run the loader script. Additionally as shown below you must include the name of the subfolder (bash) when running the loader script. The script may not run correctly if called from a different current folder or without the including the subfolder name.

You will be prompted to enter the following data when the script is run.

Before the creating the database the script generates an additional release file containing the transitive closure (this is used to optimize testing and listing of subtypes). This may take between 2 and 5 minutes to complete.

• If you rerun the loader script again on the same release, the script will reuse the existing transitive closure. If you accept the options, there will be no delay while the rebuild occurs.

When the script starts to access MySQL you will then be prompted for your database password.

• Note that the required password here is the password associated with your MySQL account.

• As noted earlier the account used for the SNOMED CT load process must have appropriate access permissions and its username should match your Mac login name.

• Depending on system performance the process may take between 20 and 45 minutes to complete. It may take longer with National Editions that contain additional content.

  • As the MySQL script runs it will report progress on the screen. Some steps take much longer than others. For example, loading data into the database tables and adding or building indexes take much longer than any of the other steps. So if the message about these steps are showing for a long time don't worry. Let the process continue.

• When the script completes, scroll back up the command window to check for any ERROR reports for MySQL ... there should not be any!

As noted in Release Type Options, SNOMED CT data can be imported into database tables from snapshot release files, from full release files or from both these sets of release files. The objective of importing the data into the database is to provide effective access to useful views of that data. In practical terms this means facilitating access to some or all of the views summarized in Table 1 below.

Table 1: Summary of Versioned Views of SNOMED CT Components and Reference Set Members

Table 2 summarizes the way in which different release type options affect the ability to access particular snapshot and delta views of SNOMED CT data. Importing the snapshot release supports direct access to the current snapshot view and query access to the most recent delta view. The full release provides access to all snapshot and delta views but is likely to perform slightly less well with the current snapshot view. Importing both full and current snapshot releases offers all the advantages of importing the full release and also provides direct access to the current snapshot view. This combined option requires more storage capacity but may be worthwhile because the current snapshot is likely to be the most commonly used view.

Table 2: Summary of Versioned View Access Capabilities Depending on Release Types Imported

The following subsections explore specific mechanisms that can be used to deliver these views in a relational database.

• Versioned View Queries

• Versioned Database Table Views

• Optimizing Versioned Table Views

See also

Extracting the current snapshot from a full release table requires a query that can identify the rows with the most recent effectiveTime for each uniquely identified component or reference set member. The general form of a current snapshot view query is shown below.

General Snapshot Query - Replace full_tableName with Name of a Full Release Table

It may be tempting to write queries that add criteria specific to a particular query within the structure of a general snapshot query. However if this is done, it must be done with care because additional conditions may cause incorrect results.\

Additional criteria to be applied to snapshot view must be added to the outer query to deliver the expected results. Otherwise they may inadvertently exclude the most recent component from the snapshot and thus leading to a misleading result.

Queries for earlier snapshot views require an additional condition so that only versions with an effectiveTime that is equal to or earlier than (i.e. less than) the date of the snapshot. In this case, it is correct to include this condition in the nested query because the objective is to constrain the maximum effectiveTime to that the subquery returns. This ensures that the outer query does not return component versions added after the specified snapshot time.

Retrospective Snapshot Query for Snapshot as at 2019-01-31

The most recent delta view is usually considered to be all the rows in a table with an effectiveTime equal to the release date. The general form of a query for the most recent delta view is shown below. Note that this query can be applied either to full release tables or the current snapshot release tables.

Most Recent Delta View Query for 2019-07-31 release. Replace tableName with Name of a Full or Snapshot Release Table for 2019-07-31 Release

A more general purpose approach to delta views is to include all changes between two specified dates (or times). This can be applied to the period between two releases or to a defined period during which multiple changes may have occurred to the same component. The general form of a delta view query for a specified period is shown below.

General Most Recent Delta View Query - for Change After 2019-01-31 and On or Before 2019-07-31

It is also possible to create an enhanced delta view that not only shows which components have changed but allows the pre-change state of that component to be seen. From a practical perspective this simply combines a delta view for a range of dates with a retrospective snapshot view for the delta view start date.

Delta View with Details of the Component Prior to the Changes

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.

Creating a Current Snapshot View from the Full Concept Table

Creating a Retrospective Snapshot View for 2019-01-31 from the Full Concept Table

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

Creating a Retrospective Snapshot View for 2019-01-31 from the Full Concept Table

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 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

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

One of the points identified in the previous section is that snapshot queries and database views are less likely to perform as well as direct access to database tables. As shown in Table 3 on the SNOMED CT example database confirm that there is a substantial performance difference between these two approaches.

Table 3: Testing Performance of Queries on Snapshot Tables and Snapshot Views

Based on these findings the most effective way to optimize access to a snapshot view, is to replace the use of database views with snapshot tables. Representing the a snapshot with tables, rather than using a database view, adds roughly 2.6 Gb to the storage requirements for the example database.

The current snapshot view is essential and is used for most interactions with the database. Therefore, the performance enhancements justify use of the additional disk space required to store the current snapshot in separate tables. If there are specific reasons for extensive access to one or two retrospective snapshots, it might also be worthwhile representing those snapshots in separate tables. However, it would not be worthwhile to apply the same approach to the full range of less frequently used retrospective snapshots. Therefore, if the snapshot views defined in do not perform sufficiently well, it is worth considering ways to optimize snapshot access.

Unlike the views described earlier, the optimization methods described below require additional columns to be added to each of the full release tables. After importing release files, the tables are processed to generate values for the additional columns and this data is used to simplify the snapshot view queries by avoiding the need for nested queries.

A column is added to each full release table. This column is used to represent flags that indicate which snapshot view each row is included in.

1. A single 64-bit integer column called flag is added to all full release tables with a default value of 0 (zero).

2. A distinct number which is a power of 2 between 2⁰ and 2⁶³ is assigned to each required retrospective snapshot time.

3. The flag column in each row is set to the sum of the values of all the snapshots in which that row appears.

The example SQL below illustrates the process of flag setting. In practice, while this query works it is not very efficient for several reasons. A more efficient approach would be to use a stored procedure that computes the full set of flags applicable to each row. This approach would allow the flag column in each row to be updated once rather than requiring a separate update for each snapshot view.

Illustrative Example of a Flag Setting Query

Once the flags are set, a query such as the one below can be used to return component versions that are part of a particular snapshot view. The example query returns row in which the flag the second bit (value 2) is set. Based on the settings in the query above this means it would include rows that are part of the 2019-01-31 snapshot view of this table

Example of a Snapshot Query Using the Flag Method

Limited testing of this optimization approach indicates that it is between 2 and 3 times faster than the unoptimized snapshot views. Direct access to a snapshot table is still twice as fast as this optimized approach.

The full release files in the 2019-07-31 release contain a total of approximately 16 million rows. If flags are added each of these rows will require a further 8 bytes of storage. No additional indexes are required to support this optimization. As a result the overall increase in storage requirements to support this optimization is less than 150 Mb.

As described here the approach is limited to 64 snapshot times. This is probably more than sufficient for most practical requirements. However, it and could be extended by adding an another flag column or by changing the data type of the flag column to binary.

• The process of setting the flags required for this approach adds significantly to the time taken to build the database.

• Adding an additional column to every table means that queries using "SELECT * FROM ... " will return a flag column that is not part of the original SNOMED CT data.

• The flag values are technically essential to the process but this may not be apparent to anyone exploring the database.

• A significant improvement in retrospective snapshot performance compared with unoptimized tables.

• Minimal impact on disk capacity (adds less that 5% to the size of the full release tables).

• Provides a fallback option for the current snapshot view if storage capacity is limited.

An additional datetime column is added to each row. This column is used to represent the time at when a row was replaced by the next version of that component or reference set member.

1. A single datetime column called supersededTime is added to all full release tables with a default value of a long distant future date (e.g. 9999-12-31).

2. Each full table is queried to establish the sequence of versions of each component or reference set member in effectiveTime order.

3. The supersededTime of a component that have been updated is set to the effectiveTime of the immediately following version of that component

The example SQL below illustrates the process of flag setting. In practice, while this query works it is not very efficient for several reasons. A more efficient approach would be to use a stored procedure that computes the full set of flags applicable to each row. This approach would allow the flag column in each row to be updated once rather than requiring a separate update for each snapshot view.

Illustrative Example of Queries Setting the supersededTime Value

Once the superseded time values are set, a query such as the one below can be used to return component versions that are part of a particular snapshot view. The example query returns row in which the flag the second bit (value 2) is set. Based on the settings in the query above this means it would include rows that are part of the 2019-01-31 snapshot view of this table

Example of a Current Snapshot Query Using the supersededTime Method

Example of a Retrospective Snapshot Query Using the supersededTime Method

Past experience indicates that this approach is 2 times faster than the unoptimized snapshot views. However, this figure varies depending on the complexity of the queries it is used in. Direct access to a snapshot table is between 2 and 3 times as fast as this optimized approach.

The full release files in the 2019-07-31 release contain a total of approximately 16 million rows. If a datetime column is added, each of these rows will require a further 5 bytes of storage. Database designs using this additional column also included additional indexes including supersededTime. As a result, the storage to fully support this approach required an additional 750 Mb.

• The process of setting the supersededTime required for this approach adds significantly to the time taken to build the database.

• Adding an additional column to every table means that queries using "SELECT * FROM ... " will return the supersededTime column that is not part of the original SNOMED CT data.

• The supersededTime values are technically essential to the process but this may not be apparent to anyone exploring the database.

• An improvement in retrospective snapshot performance compared with unoptimized tables but is out-performed by the snapshot flagging method.

• A fallback option for the current snapshot view if storage capacity is limited but requires more storage than the snapshot flagging method.

• Supports an unlimited number of snapshot times.

The instructions in this section are specific to users of Windows systems.

Users of Mac OS systems should refer .

This is available as a free download from:

You can use either of the installers listed here:

• The first installer option downloads a small package and then gets the other packages during the installation process.

Open the MySQL Workbench Schema Tab

Open MySQL Workbench and then select the Schema Tab.

You should see something like the image below.

• If you gave your database a name other than snomedct you will see the name you chose listed as a schema.

Make SNOMED CT the Default Database Schema

If the name of you SNOMED CT database schema is displayed in bold this means it is the default database.

• If your SNOMED CT database schema name is not shown in bold, double-click on the schema name. It will become the default and will be displayed in bold.

Expand the Tables Item under the SNOMED CT Database Schema name. This will reveal a list of table names.

All the listed table names have an initial prefix followed by an underscore character. The meaning of the prefixes used and the names and content of specific tables with these prefixes are summarized in the table below.

SQL database views are in effect virtual tables. They can be queried in the same way as a table but they do not store data. The data that appears to be stored in a view is in fact defined by a stored query applied to the data stored in one or more tables.

It is important to be aware of performance issues related to the use of database views. For example, views that refer to other views can result in queries that take longer to process. Although, a detailed discussion on this topic is outside the scope of this guide, please keep this in mind when using the views and composite views that were created as part of this SNOMED CT example MySQL database.

The SNOMED CT import process creates two distinct types of views. Filtered views of a single table and composite views that bring together related data from different tables.

The import process creates six distinct sets of table views. Five of these are applied to every Full release table. The naming conventions and characteristics of each of these filtered views summarized in the table below.

An additional table view (with the prefix snapasview) provides a current snapshot view derived from the Full release. This is redundant in this database, because the import process imports the Snapshot release files as well as the Full release files. However, a few snapasview examples are included to provide examples of a views that could be used to avoid the need to import the Snapshot tables.

The table below summarizes the composite views supported by the database. Many of these composite views have variants that access specific snapshot views. These variants are indicated by the view prefixes snap ,snap1 andsnap2. Note that the snap variants use the snap tables, while snap1 and snap2 variants use the relevant snapshot table views. Composite views that require access to the transitive closure table can only access the current snapshot (i.e. the snap tables). A few specific composite views are also relevant to the delta views and these have delta , delta1 and delta2 variants.