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.

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:

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 SNOMED CT Release File Specification. 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

The section contains the authoritative documentation about the structure of release files containing data that represents SNOMED CT components (concepts, descriptions and relationships).

The section contains the authoritative documentation about the structure of release files that represent SNOMED CT reference sets.

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

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. Possible alternative data type mapping are shown where appropriate with notes explaining the rationale for the preferred choice.

Table: Mapping from Release Files Data Types to MySQL Data Types

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

• Are not specific to a particular SNOMED CT release or edition.

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

• A.1 Download the SNOMED CT Example Database Package

• A.2 Download the Release File Package

• A.3 Instructions for Mac OS Users

• A.4 Instructions for Windows Users

Download and Unzip the snomed-database-loader project

• The project can be downloaded as a zip archive file snomed-database-loader.

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

Move the SnomedRfsMySql Folder to Your Home Folder

• Use Finder (or File Explorer) to move the SnomedRfsMySql folder so it becomes a subfolder of you home folder.

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

  • On Windows systems:C:\SnomedRfsMySql

Zip archive

Creating and Configuring a SNOMED CT Connection

Open MySQL Workbench Application

The following dialog should be displayed.

Create a SNOMEDCT Connection

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

When the dialog below opens:

• Select the default MySQL connection

• Then click the Duplicate button

With the newly created connection selected:

• Change the Connection Name to SNOMEDCT

• Enter a Default Schema name as snomedct (i.e. the name of the newly created database)

• Click the button:

• If the connection is successful you will see the following dialog.

In future when you open MySQL Workbench you will see the option to open the SNOMEDCT connection.

To avoid future prompts for the MySQL Password you can save the password in the Keychain.

• Click the button

• Enter the MySQL password to be stored.

• Open MySQL Workbench

• Select the SNOMED CT Connection

To test the loaded SNOMED CT database open one of the example queries in the SnomedCtRfsMySql/mysql_examples folder.

• Click open SQL button in the toolbar

• Find the mysql_examples folder in the SnomedCtRfsMySql subfolder of your home directory.

• Select and open one of the queries

The query should be display as shown below

• Click the lightning icon to run the query

The results of running the query should be displayed as below

The SNOMED CT database can be used in different ways:

• Running example SQL queries in the SnomedRfsMySql package.

• Running queries provided as part of a SNOMED CT E-Learning assignment.

• Using example SQL queries as templates for your own queries.

• Running SQL queries you have written yourself from scratch.

You can run your SQL queries in MySQL Workbench as described in the previous section. Additionally these queries can be run using the MySQL command line interface or through a MySQL connector for one of the programming languages listed at: .

The "root" user account is typically used for importing the SNOMED CT release files. However, you should create additional MySQL accounts for database users that do not have administrator rights. Ideally the name of these MySQL accounts should match the Mac username of the user as this simplifies call the mysql command line tool and the use of MySQL connectors.

To create user accounts in the MySQL Workbench select the Administration tab (see below) and select Users and Privileges (see below).

• In this example an account with the username newuser account is being created as shown below:

• In the schema privileges the newuser is given access to the snomedct database schema:

• Within the snomedct database the rights of this newuser are limited to the following actions.

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;

   • It can also be used to inform terminology server requirements for system access to SNOMED CT.

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

4. As background reading when exploring technical options

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

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

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.

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 , are able to deliver more scalable implementations of the required services.

SNOMED CT is made available to licensees as a package of tab-delimited text files. The format of these files is specified in the (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.

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.

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.

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

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!

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

Introduction

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 Release File Specification.

• File Format Specifications

• Reference Set Types

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.

Column Names

The column names used in the release files are the formally specified names and these should be used as the column names in the relevant database tables.

Column Data Types

Each column in a database table should be assigned a data type by consistently by applying defined mappings between data types defined in SNOMED CT specifications and those available in your database environment.

In most cases there should be no need to add additional columns to the release tables. However, some approaches to optimization of snapshot views may require an additional column. If any additional columns are added they should comply with the following good practice guidelines.

Additional columns must:

1. only be added for technical purposes such as optimizing database performance;

2. only be added after the columns that represent standard SNOMED CT release files data;

3. be given names that clearly distinguish them from the columns that represent data from the release files;

4. not be presented to a user of the database in ways that suggest they are part of the terminology.

All database tables representing SNOMED CT release data should use a primary key that combines id and effectiveTime.

• This combined primary key is:

  • Essential for full release tables as id alone is not unique.

  • Recommended for tables representing data from a snapshot release for overall consistency.

Additional indexes are required to support rapid access to interrelated data (for example the descriptions and relationships associated with an identified concept). The table below outlines the rationale for each of the additional indexes used to improve performance of specific tables in the SNOMED CT example database. Further indexes or revisions of these indexes may also be useful to further enhance performance. The table below is also intended as a starting point for the developing SNOMED CT solutions in other database environments. However, the benefits of adding particular indexes will depend on the characteristics of the database server. Therefore, some of these indexes may not be required and other indexing strategies may be more effective at improving performance.

Table: Additional Indexes for Specific Tables

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

Users of Mac OS systems should refer A.3 Instructions for Mac OS Users.

MySQL Installation (Windows)

Install the MySQL Windows MSI installer

This is available as a free download from: https://dev.mysql.com/downloads/installer/

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.

• The second installer downloads all the packages and can then continue the installation without an internet connection.

During the installation process you will be prompted to choose the setup type. The minimum recommended installation for the SNOMED CT database only required MySQL Server and MySQL Workbench so (unless you have additional requirements) select the Custom option as shown below.

Continue the process by clicking the next button.

• You will then be prompted to specify the products and features to be installed.

• Expand the nested list under MySQL Servers

• Select the MySQL Server 8.0.17 (or a higher version if one is shown)

• Click the arrow pointing to the right

• This will add the MySQL Server to the list of Products/Features To Be Installed.

• When both Server and Workbench are in the right-hand list, click the Next button.

At this point you may see a message indicating a requirement for a Visual C++ Redistributable package as shown below.

If you see this message you should download and install the required package from before proceeding.

• Further notes on this process are provided on .

• Select the Standalone MySQL Server option.

• Select the Development Computer option

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

• Accept or apply the following Server Connection settings

During the installation of MySQL 8.0.x on Windows you may see a message indicating that you need to install or update to the Microsoft Visual C++ 2015 Redistributable package.

• If required this can be obtained as a free download from .

• Click the Download button.

• Choose the x64 version if you have a 64-bit computer or the x86 if you have an older 32-bit computer.

• Continue an install the package.

• The return to the MySQL installation process.

• In the main Window menu locate the Command Prompt Desktop app.

• Right-click on this item to show the drop down menu

• Select the Run as administrator option.

  • This is necessary as some steps below require administrator status.

• Adjust the path as necessary if you have not installed the SnomedRfsMySql folder in the root folder on drive C.

• Type the following command to stop the MySQL server (if it is running).

• Type the following command to run the configuration process. This command runs a script that updates the MySQL server configuration.

• Type the following command to restart the MySQL server

• Go to

• Download either the appropriate version for your system (i.e. either 32-bit or 64-bit)

• Open and run the downloaded file

  • This will have a name like "strawberry-perl-5.30.0.1-64bit.msi" (version numbers may differ).

• Go through the installation process

  • Read and accept the license.

• When the installation is complete, you can test the installation by running the following command in a newly opened Command Prompt.

• If Strawberry Perl is correctly installed the result should the following message.

• In the main Window menu locate the Command Prompt Desktop app.

• Right-click on this item to show the drop down menu

• Select the Run as administrator option.

  • This is necessary as some steps below require adminstrator status.

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 (win) 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, you will be offered the option to 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 90 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!

This issue usually results from an incorrect service name reference. This arises when the MySQL Workbench contains a reference to an older service name (e.g. mysql), whereas MySQL Server 8.0.x uses the service name MySQL80.

• Open the Services Desktop app from the Windows menu.

• Then search the list of services to identify the name of the MySQL service

• Open MySQL Workbench.

• In the Server menu select Management Access Settings.

• In the Manage Server Connections dialog update the Window Service Name to match the service name identified earlier.

Creating the Database

The first step is to create the database schema. The code below is an excerpt from the SNOMED CT example database MySQL import script which carries out the following steps:

• Creates a database called "snomedct"

• Sets the default character set of the database as "utf8mb4"

• Sets snomedct as the default database for the following script.

• Sets timeouts appropriate to the import process.

• Clears sql_mode settings some of which, if present, may cause import issues.

Create SNOMED CT Database

This section contains SQL statements that create database tables to accommodate the data in each of the main component files. Each table creation is accompanied by a summary of the relevant release file specification.

1. The title of the file specification summary table is a link to the full file specification.

2. The table names used on this page are prefixed with full_ as these are the tables into which the full SNOMED CT release will be imported. The loader script also create identically structured tables with the prefix snap_ and the latest snapshot view is loaded into those tables.

3. The SQL code on this page creates the primary keys for each table (id, effective time) but omits creation of any other indexes. The loader script creates additional indexes after importing data into the table. This enables faster importing of data from the text files as the additional indexes do not need to be updated while importing.

Create Concept Table

Create Description Table

Create Relationship Table

This section contains SQL statements that create database tables to accommodate the data in some of the reference set release files. Each table creation is accompanied by a summary of the relevant release file specification. The selection of reference set types shown on this page is incomplete but includes at least one example reference set that includes an additional column of each of the permitted types (componentId, string and integer).

1. The title of the file specification summary table is a link to the full file specification.

2. The table names used on this page are prefixed with full_ as these are the tables into which the full SNOMED CT release will be imported. The loader script also create identically structured tables with the prefix snap_ and the latest snapshot view is loaded into those tables.

3. The SQL code on this page creates the primary keys for each table (id, effective time) but omits creation of any other indexes. The loader script creates additional indexes after importing data into the table. This enables faster importing of data from the text files as the additional indexes do not need to be updated while importing.

Create Simple Refset Table

Create Language Refset Table

Create Extended Map Refset Table

Create Module Dependency Refset Table

This section contains examples of the SQL statements used to imports data from a component release files in the appropriate database table. Before importing the tables must be created (see and ).