Database Table Design

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.

• File Format Specifications

• Reference Set Types

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 by consistently by applying defined mappings between data types defined in SNOMED CT specifications .

Additional Column Options

In most cases there should be no need to add additional columns to the release tables. However, 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:

1. only be added for technical purposes such as optimizing database performance;

2. only be added after the columns that represent standard SNOMED CT release files data;

3. be given names that clearly distinguish them from the columns that represent data from the release files;

4. 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.

  • .

Additional Indexes

Additional indexes are required to support rapid access to interrelated data (for example the descriptions and relationships associated with an identified concept). . Further indexes or revisions of these indexes may also be useful to further enhance performance. The table below 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: 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.
(full or snap)_description	description_lang	conceptId,languageCode	Find descriptions with specific language code for concept.
(full or snap)_description	description_term		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.
(full or snap)_relationship	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.
(full or snap)_statedRelationship	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.
(full or snap)_textDefinition	textDefinition_lang	conceptId,languageCode	Find text definitions with specific language code for concept.
(full or snap)_textDefinition	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.
(full or snap)refset [REFSETTYPE] These indexes are applied to all refset tables	_[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.