Introduction

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

Notes

The symbol in the top right of each file specification summary table is a link to the full file specification.
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.
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.
The SQL code used on this page does not include any additional optimizations for generating alternative snapshot views. Optimizations discussed in this guide can be added to the tables if required. However, this loader script creates and populates tables for both the full release and the current snapshot views. Therefore, additional optimizations would only deliver performance benefits when querying retrospective snapshot views. Even in this case the performance benefits for most types of query are often limited when compared to the use of unoptimized dynamic views.
The effectiveTime is set as a DATETIME data type. This supports a specific time in hours, minutes or seconds. In practice, effectiveTime values are formally restricted to YYYYMMDD but we are aware of at least one SNOMED CT extension that includes time units in the effectiveTime field or its release files. The effectiveTime is set by default to a 2000-01-31, a date which predates any SNOMED CT effectiveTime value. In previous versions of the script defaults were set to 0000-00-00 but some SQL settings treat these as invalid dates. In practice, the effectiveTime will always be set by the imported data so the default has no material effect.

Creating a Simple Refset Table

Create Simple Refset Table

DROP TABLE IF EXISTS `full_refset_Simple`;

CREATE TABLE `full_refset_Simple` (
  `id` char(36) NOT NULL DEFAULT '',
  `effectiveTime` DATETIME NOT NULL 
		DEFAULT  '2000-01-31 00:00:00',
  `active` TINYINT NOT NULL DEFAULT  0,
  `moduleId` BIGINT NOT NULL DEFAULT  0,
  `refsetId` BIGINT NOT NULL DEFAULT  0,
  `referencedComponentId` BIGINT NOT NULL DEFAULT  0,
  PRIMARY KEY (`id`,`effectiveTime`))
  ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

{

Table 5.3-1: Simple Refset File Specification Summary

Field	Data type
id	UUID
effectiveTime	Time
active	Boolean
moduleId	SCTID
refsetId	SCTID
referencedComponentId	SCTID

Creating a Language Refset Table

Create Language Refset Table

DROP TABLE IF EXISTS `full_refset_Language`;

CREATE TABLE `full_refset_Language` (
  `id` char(36) NOT NULL DEFAULT '',
  `effectiveTime` DATETIME NOT NULL 
		DEFAULT  '2000-01-31 00:00:00',
  `active` TINYINT NOT NULL DEFAULT  0,
  `moduleId` BIGINT NOT NULL DEFAULT  0,
  `refsetId` BIGINT NOT NULL DEFAULT  0,
  `referencedComponentId` BIGINT NOT NULL DEFAULT  0,
  `acceptabilityId` BIGINT NOT NULL DEFAULT  0,
  PRIMARY KEY (`id`,`effectiveTime`))
  ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

Table 5.3-2: Language Refset File Specification Summary

Field	Data type
id	UUID
effectiveTime	Time
active	Boolean
moduleId	SCTID
refsetId	SCTID
referencedComponentId	SCTID
acceptabilityId	SCTID

Creating an Extended Map Refset Table

Create Extended Map Refset Table

DROP TABLE IF EXISTS `full_refset_ExtendedMap`;

CREATE TABLE `full_refset_ExtendedMap` (
  `id` char(36) NOT NULL DEFAULT '',
  `effectiveTime` DATETIME NOT NULL 
		DEFAULT  '2000-01-31 00:00:00',
  `active` TINYINT NOT NULL DEFAULT  0,
  `moduleId` BIGINT NOT NULL DEFAULT  0,
  `refsetId` BIGINT NOT NULL DEFAULT  0,
  `referencedComponentId` BIGINT NOT NULL DEFAULT  0,
  `mapGroup` INT NOT NULL DEFAULT 0,
  `mapPriority` INT NOT NULL DEFAULT 0,
  `mapRule` TEXT NOT NULL,
  `mapAdvice` TEXT NOT NULL,
  `mapTarget` VARCHAR (200) NOT NULL DEFAULT '',
  `correlationId` BIGINT NOT NULL DEFAULT  0,
  `mapCategoryId` BIGINT NOT NULL DEFAULT  0,
  PRIMARY KEY (`id`,`effectiveTime`))
  ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

Table 5.3-3: Extended Map Refset File Specification Summary

Field	Data type
id	UUID
effectiveTime	Time
active	Boolean
moduleId	SCTID
refsetId	SCTID
referencedComponentId	SCTID
mapGroup	Integer
mapPriority	Integer
mapRule	String
mapAdvice	String
mapTarget	String
correlationId	SCTID
mapCategoryId	SCTID

Creating a Module Dependency Refset Table

Create Module Dependency Refset Table

DROP TABLE IF EXISTS `full_refset_ModuleDependency`;

CREATE TABLE `full_refset_ModuleDependency` (
  `id` char(36) NOT NULL DEFAULT '',
  `effectiveTime` DATETIME NOT NULL 
		DEFAULT  '2000-01-31 00:00:00',
  `active` TINYINT NOT NULL DEFAULT  0,
  `moduleId` BIGINT NOT NULL DEFAULT  0,
  `refsetId` BIGINT NOT NULL DEFAULT  0,
  `referencedComponentId` BIGINT NOT NULL DEFAULT  0,
  `sourceEffectiveTime` DATETIME NOT NULL 
		DEFAULT  '2000-01-31 00:00:00',
  `targetEffectiveTime` DATETIME NOT NULL 
		DEFAULT  '2000-01-31 00:00:00',
  PRIMARY KEY (`id`,`effectiveTime`))
  ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

Table 5.3-4: Module Dependency Refset File Specification Summary

Field	Data type
id	UUID
effectiveTime	Time
active	Boolean
moduleId	SCTID
refsetId	SCTID
referencedComponentId	SCTID
sourceEffectiveTime	Time
targetEffectiveTime	Time

Search

Page tree

Introduction

Notes

Creating a Simple Refset Table

Creating a Language Refset Table

Creating an Extended Map Refset Table

Creating a Module Dependency Refset Table

Search

Page tree

5.3. Creating Tables for Reference Sets

Introduction

Notes

Creating a Simple Refset Table

Creating a Language Refset Table

Creating an Extended Map Refset Table

Creating a Module Dependency Refset Table