# 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](https://app.gitbook.com/o/h8Z6qGxuQrzM9vbx5bPT/s/irKbJsZG57nSWZA4GT0M/).

* [File Format Specifications](https://app.gitbook.com/s/irKbJsZG57nSWZA4GT0M/component-release-file-specification/4.2-file-format-specifications)
* [Reference Set Types](https://app.gitbook.com/s/irKbJsZG57nSWZA4GT0M/reference-set-release-file-specification/5.2-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 [data type](#user-content-fn-1)[^1] by consistently by applying defined mappings between data types defined in SNOMED CT specifications [and those available in your database environment](#user-content-fn-2)[^2].

## 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.
  * [Recommended for tables representing data from a snapshot release for overall consistency](#user-content-fn-3)[^3].

## Additional Indexes

Additional indexes are required to support rapid access to interrelated data (for example the descriptions and relationships associated with an identified concept). [The table below outlines the rationale for each of the additional indexes used to improve performance of specific tables in the SNOMED CT example database](#user-content-fn-4)[^4]. 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

<table><thead><tr><th width="252.203125">Database Table</th><th width="243.80078125">Index Name</th><th width="247.4375">Index Columns</th><th width="308.58984375">Rationale for this Index</th></tr></thead><tbody><tr><td>(full or snap)<strong>_description</strong></td><td>description_concept</td><td>conceptId</td><td>Find descriptions for concept.</td></tr><tr><td>(full or snap)<strong>_description</strong></td><td>description_lang</td><td>conceptId,languageCode</td><td>Find descriptions with specific language code for concept.</td></tr><tr><td>(full or snap)<strong>_description</strong></td><td>description_term</td><td><a data-footnote-ref href="#user-content-fn-5">term (fulltext)</a></td><td>Search for terms.</td></tr><tr><td>(full or snap)<strong>_relationship</strong></td><td>relationship_dest</td><td>destinationId,typeId,sourceId</td><td>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.</td></tr><tr><td>(full or snap)<strong>_relationship</strong></td><td>relationship_source</td><td>sourceId,typeId,destinationId</td><td>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.</td></tr><tr><td>(full or snap)<strong>_statedRelationship</strong></td><td>statedRelationship_dest</td><td>destinationId,typeId,sourceId</td><td>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.</td></tr><tr><td>(full or snap)<strong>_statedRelationship</strong></td><td>statedRelationship_source</td><td>sourceId,typeId,destinationId</td><td>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.</td></tr><tr><td>(full or snap)<strong>_textDefinition</strong></td><td>textDefinition_concept</td><td>conceptId</td><td>Find text definitions for concept.</td></tr><tr><td>(full or snap)<strong>_textDefinition</strong></td><td>textDefinition_lang</td><td>conceptId,languageCode</td><td>Find text definitions with specific language code for concept.</td></tr><tr><td>(full or snap)<strong>_textDefinition</strong></td><td>textDefinition_term</td><td>term (fulltext)</td><td>Search for terms in text definitions.</td></tr><tr><td>(full or snap)<em><strong>refset</strong></em> <em>[REFSETTYPE]</em><br>These indexes are applied to all refset tables</td><td>_[REFSETTYPE]<em>c</em></td><td>referencedComponentId</td><td>Find rows in any reference set of type <em>[REFSETTYPE]</em> that refer to a specified referenced component.</td></tr><tr><td>(full or snap)<em><strong>refset</strong></em> <em>[REFSETTYPE]</em><br>These indexes are applied to all refset tables</td><td>_[REFSETTYPE]<em>rc</em></td><td>refsetId,referencedComponentId</td><td>Find rows in an identified reference set of type <em>[REFSETTYPE]</em> that refer to a specified referenced component.</td></tr><tr><td>(full or snap)<strong>_refset_ExtendedMap</strong></td><td>ExtendedMap_map</td><td>refsetId,mapTarget</td><td>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.</td></tr><tr><td>(full or snap)<strong>_refset_SimpleMap</strong></td><td>SimpleMap_map</td><td>refsetId,mapTarget</td><td>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.</td></tr><tr><td>(full or snap)<strong>_refset_MRCMAttributeDomain</strong></td><td>MRCMAttributeDomain_dom</td><td>domainId</td><td>Find attribute domain information for a specified domain.</td></tr></tbody></table>

<a href="https://docs.google.com/forms/d/e/1FAIpQLScTmbZIf0UEQwYDkY27EEWBkaiYkHSbR0_9DmFrMLXoQLyL7Q/viewform?usp=pp_url&#x26;entry.1767247133=SQL+Guide&#x26;entry.670899847=Database%20Table%20Design" class="button primary">Provide Feedback</a>

[^1]: SNOMED CT data types are defined in section [Release File Data Types](https://app.gitbook.com/s/irKbJsZG57nSWZA4GT0M/release-types-packages-and-files/3.1-common-features-of-all-release-files/3.1.2-release-file-data-types) of the [SNOMED CT Release File Specifications](https://app.gitbook.com/o/h8Z6qGxuQrzM9vbx5bPT/s/irKbJsZG57nSWZA4GT0M/).

[^2]: See recommendation on the approach to data type mapping in section [Data Type Options](https://docs.snomed.org/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/database-design/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 MySQL Reference Data](https://docs.snomed.org/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/appendices/appendix-a-building-the-snomed-ct-example-database/a7-mysql-reference-data).