Page History
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
- 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 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.Gloss t SNOMED CT extension - 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 | ||||||
|---|---|---|---|---|---|---|
| ||||||
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 | ||||
|---|---|---|---|---|
| ||||
Concept File Specification Summary |
Creating a Description Table
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
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 | ||||
|---|---|---|---|---|
| ||||
Description File Specification Summary |
Field | Data type | |||||||||||||
id |
| |||||||||||||
|
| |||||||||||||
|
| |||||||||||||
|
| |||||||||||||
conceptId |
| |||||||||||||
languageCode |
| |||||||||||||
|
| |||||||||||||
| ||||||||||||||
|
| |||||||||||||
Creating a Relationship Table
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
DROP TABLE IF EXISTS `full_relationship`;
CREATE TABLE `full_textDefinition` (
`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 | ||||
|---|---|---|---|---|
| ||||
Relationship File Specification Summary |
Field | Data type | |||||||||||||
id |
| |||||||||||||
|
| |||||||||||||
|
| |||||||||||||
|
| |||||||||||||
|
| |||||||||||||
|
| |||||||||||||
|
| |||||||||||||
|
| |||||||||||||
|
| |||||||||||||
|
| |||||||||||||
Overview
Content Tools
Apps