Introduction
Every SNOMED CT release file conforms to a formal specification which defines the names and data types of each of the columns in the file. These specifications are subsections of the Release File Specification.
The preceding chapters addressed the representation of SNOMED CT data types in a database and naming the individual tables into which release data will be loaded. This section considers two remaining design decisions related to database table design, names to be applied to the columns in the tables and keys and indexes that should be added to support speedy access to the data.
Column Names
The column names used in the release files are the formally specified names and these should be used as the column names in the relevant database tables.
Column Data Types
Each column in a database table should be assigned a data type by consistently by applying defined mappings between data types defined in SNOMED CT specifications 1 and those available in your database environment2 .
Additional Column Options
In most cases there should be no need to add additional columns to the release tables. However, as discussed in 4.4.3. Snapshot View Options, some approaches to optimization of snapshot views may require an additional column. If any additional columns are added they should comply with the following good practice guidelines.
Additional columns must:
- only be added for technical purposes such as optimizing database performance;
- only be added after the columns that represent standard SNOMED CT release files data;
- be given names that clearly distinguish them from the columns that represent data from the release files;
- not be presented to a user of the database in ways that suggest they are part of the terminology.
Primary Keys
All database tables representing SNOMED CT release data should use a primary key that combines id and effectiveTime.
- This combined primary key is:
- Essential for full release tables as id alone is not unique.
Recommended for tables representing data from a snapshot release for overall consistency3 .
Additional Indexes
Additional indexes are required to support rapid access to interrelated data (for example the descriptions and relationships associated with an identified concept). outlines the rationale for each of the additional indexes used to improve performance of specific tables in the SNOMED CT example database4
. Further indexes or revisions of these indexes may also be useful to further enhance performance. Table 4.5-1 is also intended as a starting point for the developing SNOMED CT solutions in other database environments. However, the benefits of adding particular indexes will depend on the characteristics of the database server. Therefore, some of these indexes may not be required and other indexing strategies may be more effective at improving performance.
Table 4.5-1: Additional Indexes for Specific Tables
| Database Table | Index Name | Index Columns | Rationale for this Index |
| (full or snap)_description | description_concept | conceptId | Find descriptions for concept. |
| description_lang | conceptId,languageCode | Find descriptions with specific language code for concept. | |
| description_term | term (fulltext)5 | Search for terms. | |
| (full or snap)_relationship | relationship_dest | destinationId,typeId,sourceId | Find concepts with relationships of a specified type of which a specified concept is the destinationId (value or supertype) or find relationships with a specific combination of destination, type and source. |
| relationship_source | sourceId,typeId,destinationId | Find concepts with relationships of a specified type of which a specified concept is the destinationId (defined concept or subtype) or find relationships with a specific combination of source, type and destination. | |
| (full or snap)_statedRelationship | statedRelationship_dest | destinationId,typeId,sourceId | Find concepts with stated relationships of a specified type of which a specified concept is the destinationId (value or supertype) or find relationships with a specific combination of destination, type and source. |
| statedRelationship_source | sourceId,typeId,destinationId | Find concepts with relationships of a specified type of which a specified concept is the destinationId (defined concept or subtype) or find relationships with a specific combination of source, type and destination. | |
| (full or snap)_textDefinition | textDefinition_concept | conceptId | Find text definitions for concept. |
| textDefinition_lang | conceptId,languageCode | Find text definitions with specific language code for concept. | |
| textDefinition_term | term (fulltext) | Search for terms in text definitions. | |
(full or snap)_refset_ [REFSETTYPE] These indexes are applied to all refset tables | [REFSETTYPE]_c | referencedComponentId | Find rows in any reference set of type [REFSETTYPE] that refer to a specified referenced component. |
| [REFSETTYPE]_rc | refsetId,referencedComponentId | Find rows in an identified reference set of type [REFSETTYPE] that refer to a specified referenced component. | |
| (full or snap)_refset_ExtendedMap | ExtendedMap_map | refsetId,mapTarget | Find map records in a specified mapping reference set for a particular mapTarget. Find all concepts that have a map to a particular mapTarget in a specified mapping reference set. |
| (full or snap)_refset_SimpleMap | SimpleMap_map | refsetId,mapTarget | Find map records in a specified mapping reference set for a particular mapTarget. Find all concepts that have a map to a particular mapTarget in a specified mapping reference set. |
| (full or snap)_refset_MRCMAttributeDomain | MRCMAttributeDomain_dom | domainId | Find attribute domain information for a specified domain. |
| Ref | Notes |
|---|---|
| 1 | SNOMED CT data types are defined in section 3.1.2 Release File Data Types of the SNOMED CT Release File Specifications. |
| 2 | See recommendation on the approach to data type mapping in section 4.3. Data Type Options. |
| 3 | A primary key consisting only of the id is a potential alternative for tables representing data from a snapshot release. |
| 4 | To avoid slowing the data import process additional indexes are not added to the database tables until after all text files have been imported. |
| 5 | Full text indexes for terms allow effective searching. However, unless the database is correctly configured, short words, abbreviations and stop words may prevent effective indexing of common clinical terms. For further details refer to A.7.1 Required MySQL Configuration Settings. |
Feedback
Overview
Content Tools
Apps