Introduction

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.

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 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 practice, the effectiveTime will always be set by the imported data so the default has no material effect.
  5. Tables are also created for the full_textDefinition table and its snapshot version. As these tables have the same structure as the description tables, the data from the textDefinition release files could be imported into those tables instead. The text definitions would still be distinguishable from the descriptions as they have a different typeId.



Creating a Concept Table


DROP TABLE IF EXISTS `full_concept`;

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





Concept File Specification Summary




Field

Data type

Full file specification

id



Creating a Description Table


DROP TABLE IF EXISTS `full_description`;

CREATE TABLE `full_description` (
    `id` BIGINT NOT NULL DEFAULT  0,
    `effectiveTime` DATETIME NOT NULL DEFAULT  '2000-01-31 00:00:00',
    `active` TINYINT NOT NULL DEFAULT  0,
    `moduleId` BIGINT NOT NULL DEFAULT  0,
    `conceptId` BIGINT NOT NULL DEFAULT  0,
    `languageCode` VARCHAR (3) NOT NULL DEFAULT '',
    `typeId` BIGINT NOT NULL DEFAULT  0,
    `term` TEXT NOT NULL,
    `caseSignificanceId` BIGINT NOT NULL DEFAULT  0,
    PRIMARY KEY (`id`,`effectiveTime`))
    ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;




Description File Specification Summary


Field

Data type

Full file specification

id

conceptId

languageCode

term




Creating a Relationship Table


DROP TABLE IF EXISTS `full_relationship`;

CREATE TABLE `full_relationship` (
	`id` BIGINT NOT NULL DEFAULT  0,
	`effectiveTime` DATETIME NOT NULL DEFAULT  '2000-01-31 00:00:00',
	`active` TINYINT NOT NULL DEFAULT  0,
	`moduleId` BIGINT NOT NULL DEFAULT  0,
	`sourceId` BIGINT NOT NULL DEFAULT  0,
	`destinationId` BIGINT NOT NULL DEFAULT  0,
	`relationshipGroup` INT NOT NULL DEFAULT 0,
	`typeId` BIGINT NOT NULL DEFAULT  0,
	`characteristicTypeId` BIGINT NOT NULL DEFAULT  0,
	`modifierId` BIGINT NOT NULL DEFAULT  0,
	PRIMARY KEY (`id`,`effectiveTime`))
	ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;




Relationship File Specification Summary


Field

Data type

Full file specification

id