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.

• 4.2 File Format Specifications
• 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 by consistently by applying defined mappings between data types defined in SNOMED CT specifications 

 and those available in your database environment

.

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:

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

    Footnote Macro
    A primary key consisting only of the id is a potential alternative for tables representing data from a snapshot release.

    .

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 database

. Further indexes or revisions of these indexes may also be useful to further enhance performance. 

Additional Indexes for Specific Tables