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

  1. The symbol in the top right of each 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.
  4. 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.
  5. 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  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


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;




{




Simple Refset File Specification Summary


Field

Data type

Full file specification

id



Creating a 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;




Language Refset File Specification Summary


Field

Data type

Full file specification

id




Creating an 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;



Extended Map Refset File Specification Summary


Field

Data type

Full file specification

id





Creating a 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;



Module Dependency Refset File Specification Summary


Field

Data type

Full file specification

id