# Database Table Naming

## Introduction

The International Edition of SNOMED CT contains three release types (Full, Snapshot and Delta) and each of these release types include 20 files (2019-07-31 release).[ Each of those files has a distinct structure representing either a type of component (e.g. concept, relationship or description) or a type of reference set. SNOMED CT Extensions contain additional files and most of these conform to the same structure as one of the International Edition release files](#user-content-fn-1)[^1].

When designing a database to accommodate SNOMED CT release files, decisions need to be made about the names to give to each of the database tables. One option is to give the tables exactly the same names as the release files they represent. However, analysis of the release file naming conventions indicates that these conventions are not directly applicable to table names.

SNOMED CT release file naming conventions include some elements that represent information about the provenance, language and release date of a specific file. This information is useful and in some cases essential as a way of distinguishing releases files. However, this information is neither essential nor helpful when naming tables that may contain data from different SNOMED CT versions, editions and extensions.

The release file naming conventions do however include some essential elements that relate directly to the specification of the nature and structure of the data they contain. The following sections provide a summary of the release file naming conventions, identify the elements in release file names that are relevant to database table naming and describe a set rules that can be applied to derive consistent table names from release file names.

## Release File Naming

All SNOMED CT release file are named in accordance with the [Release File Naming Convention](https://app.gitbook.com/s/irKbJsZG57nSWZA4GT0M/release-types-packages-and-files/3.3-naming-conventions-for-release-packages-and-files/3.3.2-release-file-naming-convention). The naming conventions result in names that can be decomposed into parts as illustrated by examples with color coding in the table below.

Table 1: Illustrations of the Release File Naming Conventions

<table data-header-hidden><thead><tr><th></th><th width="464.9921875"></th></tr></thead><tbody><tr><td><strong>Description of the pattern or file illustrated</strong></td><td><strong>Example release file names</strong></td></tr><tr><td><a data-footnote-ref href="#user-content-fn-2">General pattern</a></td><td>prefix_[<mark style="color:purple;">refsetPattern</mark>]<mark style="color:blue;">componentType</mark>_[<mark style="color:red;">refsetType</mark>][<mark style="color:green;">extensionName</mark>]<strong>releaseType</strong>[-<mark style="color:orange;">language</mark>]_<strong>country</strong> _releaseDate.<em>txt</em></td></tr><tr><td>International edition full release concepts file for 2019-07-31</td><td>sct2_<mark style="color:blue;">Concep</mark>t_<strong>Full</strong> _<strong>INT</strong> _20190731.<em>txt</em></td></tr><tr><td>International edition snapshot release english descriptions file</td><td>sct2_<mark style="color:blue;">Description</mark>_<strong>Snapshot</strong> -<mark style="color:orange;">en</mark>_<strong>INT</strong> _20190731.<em>txt</em></td></tr><tr><td>Spanish extension full release spanish descriptions file</td><td>sct2_<mark style="color:blue;">Description</mark>_<mark style="color:green;">SpanishExtension<strong>Full</strong></mark> -<mark style="color:orange;">es</mark>_<strong>INT</strong> _20190430.<em>txt</em></td></tr><tr><td>International edition snapshot release extended maps reference set file</td><td>der2_<mark style="color:purple;">iisssccRefset</mark>_<mark style="color:red;">ExtendedMap<strong>Snapshot</strong></mark> _<strong>INT</strong> _20190731.<em>txt</em></td></tr><tr><td>Spanish extension full release spanish language reference set file</td><td>der2_<mark style="color:purple;">cRefset</mark>_<mark style="color:red;">LanguageSpanishExtension<strong>Full</strong></mark> -<mark style="color:orange;">es</mark>_<strong>INT</strong> _20190430.<em>txt</em></td></tr><tr><td>International edition snapshot release english language reference set file</td><td>der2_<mark style="color:purple;">cRefset</mark>_<mark style="color:red;">Language<strong>Snapshot</strong></mark> -<mark style="color:orange;">en</mark>_<strong>INT</strong> _20190731.<em>txt</em></td></tr></tbody></table>

## File Name Element Relevance to Table Names

The table below identifies the elements of the release file naming pattern that are relevant to the naming of the database tables containing content from those files. It also outlines the reasons why some elements that form an important part of the release file names can or should be omitted from the relevant database table names.

Table 2: Relevance of File Name Pattern Elements to Database Table Names

<table><thead><tr><th width="179.93359375">Filename Element</th><th width="199.875">Relevant to Table Name</th><th>Explanation</th></tr></thead><tbody><tr><td>prefix</td><td>No</td><td>The prefix sct2 or der2 distinguishes components from derivatives (refsets). This information is present in the componentType and refsetType.</td></tr><tr><td><mark style="color:purple;">refsetPattern</mark></td><td>No</td><td>This information relates to the datatypes of additional columns in the file and the table. The table structure includes the required columns so there is no reason to include this in the table name.</td></tr><tr><td><mark style="color:blue;">componentType</mark></td><td>Yes</td><td>This is essential as it indicates either the type of components represented in the table or that this is a reference set</td></tr><tr><td><mark style="color:red;">refsetType</mark></td><td>Yes</td><td>This is essential to distinguish the tables representing different reference set types (and not present in other file names).</td></tr><tr><td><mark style="color:green;">extensionName</mark></td><td>No</td><td>This is not required as data from extensions files should be included in the same tables as the equivalent data from the international release. Individual records maintained in extensions can be distinguished by moduleId</td></tr><tr><td><strong>releaseType</strong></td><td>Yes</td><td>This is essential if importing data from both the full and snapshot release. However, since this is a fundamental grouping, it is probably sensible for this to be a prefix to the table name. Otherwise with long table names this key distinction may be easier to miss. A short prefix denoting release types with a convention that also allows database views to be named in a similar consistent manner is recommended.</td></tr><tr><td><mark style="color:orange;">language</mark></td><td>No</td><td>This is not applicable to the description table name. All descriptions should be accommodated in a single table with the languageCode column indicating the language of the associated term. Similarly it is not applicated to a language reference set table name. All language reference sets should be accommodated in a single table with the refsetId column indicating the language and dialect of each language preference.</td></tr><tr><td><strong>country</strong></td><td>No</td><td>This is not required in the table name as the country or other point of origin of the components and reference set members is indicated by the moduleId.</td></tr><tr><td>releaseDate</td><td>No</td><td>This is not required as data from many releases is included in the full release file tables. In the case of the snapshot it would be possible to include the date of the snapshot in the table name. However this is not recommended because, as noted in<a href="4.2-release-type-options"> Release Type Options</a> multiple sets of tables representing different snapshot releases multiply the required storage capacity required.</td></tr></tbody></table>

## Deriving Table Names from Release File Names

The analysis in Table 2, identifies three elements in the release file name that are relevant to table names. There are various ways in which table names could be derived by combining these elements and one of these is shown in Table 3. The end result (shown in Table 4) is a set of table names that:

* Are as short as possible while clearly identifying:
  * The release type from which they are derived
  * The component or reference set type specification to which they conform
* Are not specific to a particular SNOMED CT release or edition.

{% hint style="warning" %}
**Note**

The rules shown here are those applied to the example SNOMED CT database. Alternative table naming patterns may be preferred by those developing their own SNOMED CT database. However, is important is to ensure that the table naming pattern should be consistently applicable to all release files. Furthermore, it also should be readily applicable to any additional reference set types that may be added to future releases of the International Edition (or included in other SNOMED CT editions and or extensions).
{% endhint %}

Table 3: Rules Applied to Release File Names to Generate Table Name for the Example Database

| **Start with file name pattern**                                                | prefix\_\[<mark style="color:purple;">refsetPattern</mark>]<mark style="color:blue;">componentType</mark>\_\[<mark style="color:red;">refsetType</mark>]\[<mark style="color:green;">extensionName</mark>]**releaseType**\[-<mark style="color:orange;">language</mark>]\_**country** \_releaseDate.*txt* |
| ------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Remove element that are not required**                                        | <mark style="color:blue;">componentType</mark>\[\_<mark style="color:red;">refsetType</mark>]**releaseType**                                                                                                                                                                                              |
| **Make release type the prefix**                                                | **releaseType\_** <mark style="color:blue;">componentType</mark>\[\_<mark style="color:red;">refsetType</mark>]                                                                                                                                                                                           |
| <p><strong>Abbreviate the prefix to 4 characters</strong><br>(full or snap)</p> | **rtyp \_**<mark style="color:blue;">componentType</mark>\[\_<mark style="color:red;">refsetType</mark>]                                                                                                                                                                                                  |

Table 4: Results of Mapping Release File Names to Example Database Table Names

<table><thead><tr><th width="555.06640625">List of Release File Name</th><th width="574.4765625" valign="middle">List of Corresponding Table Names in the Example Database</th></tr></thead><tbody><tr><td>QueJLRRoeGGJ</td><td valign="middle"><div><figure><img src="https://3901702309-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FLnTo62BQeQSW6tBNKv0T%2Fuploads%2Ffb8via4x2bocVPoD6Uru%2Frelease%20file%20names.png?alt=media&#x26;token=13f56c6f-7005-4b63-9472-000c0d4be3cb" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td><em>... list continues for all Snapshot release files</em></td><td valign="middle"><em>... list continues for all the snap</em> tables_</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%20Naming" class="button primary">Provide Feedback</a>

[^1]: A few files in an extension may conform to a reference set that has been defined by the organization responsible for that extension.

[^2]: Pattern elements in square brackets \[ ] are optional depending on file type.