Search



Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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


Code Block
languagesql
themeConfluence
titleCreate 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;





Caption label
CapIdconcept-file-specification-summary
CapTypeTable
Concept File Specification Summary




Field

Data type

id

Specref
RefTypedata type
tSCTID

Specref
RefType(field)
teffectiveTime

Specref
RefTypedata type
tTime

Specref
RefType(field)
tactive

Specref
RefTypedata type
tBoolean

Specref
RefType(field)
tmoduleId

Specref
RefTypedata type
tSCTID

Specref
RefType(field)
tdefinitionStatusId

Specref
RefTypedata type
tSCTID




Creating a Description Table


Code Block
languagesql
themeConfluence
titleCreate 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;



Caption label
CapIddescription-file-specification-summary
CapTypeTable
Description File Specification Summary


Field

Data type

id

Specref
RefTypedata type
tSCTID

Specref
RefType(field)
teffectiveTime

Specref
RefTypedata type
tTime

Specref
RefType(field)
tactive

Specref
RefTypedata type
tBoolean

Specref
RefType(field)
tmoduleId

Specref
RefTypedata type
tSCTID

conceptId

Specref
RefTypedata type
tSCTID

languageCode

Specref
RefType(data type)
tString

Specref
RefType(field)
ttypeId

Specref
RefTypedata type
tSCTID

term

Specref
RefTypedata type
tString

Specref
RefType(field)
tcaseSignificanceId

Specref
RefTypedata type
tSCTID





Creating a Relationship Table


Code Block
languagesql
themeConfluence
titleCreate Relationship Table
DROP TABLE IF EXISTS `full_relationship`;

CREATE TABLE `full_textDefinition`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,
    `conceptId` 	`sourceId` BIGINT NOT NULL DEFAULT  0,
	`destinationId` BIGINT NOT NULL `languageCode` VARCHAR (3)DEFAULT  0,
	`relationshipGroup` INT NOT NULL DEFAULT ''0,
    	`typeId` BIGINT NOT NULL DEFAULT  0,
	`characteristicTypeId` BIGINT NOT NULL `term` TEXTDEFAULT NOT NULL0,
	`modifierId`    `caseSignificanceId` BIGINT NOT NULL DEFAULT  0,
    	PRIMARY KEY (`id`,`effectiveTime`))
    	ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;



Caption label
CapIdrelationship-file-specification-summary
CapTypeTable
Relationship File Specification Summary


Field

Data type

id

Specref
RefTypedata type
tSCTID

Specref
RefType(field)
teffectiveTime

Specref
RefTypedata type
tTime

Specref
RefType(field)
tactive

Specref
RefTypedata type
tBoolean

Specref
RefType(field)
tmoduleId

Specref
RefTypedata type
tSCTID

Specref
RefTypefield
tsourceId

Specref
RefTypedata type
tSCTID

Specref
RefTypefield
tdestinationId

Specref
RefTypedata type
tSCTID

Specref
RefTypefield
trelationshipGroup

Specref
RefTypedata type
tInteger

Specref
RefTypefield
ttypeId

Specref
RefTypedata type
tSCTID

Specref
RefTypefield
tcharacteristicTypeId

Specref
RefTypedata type
tSCTID

Specref
RefTypefield
tmodifierId

Specref
RefTypedata type
tSCTID