SNOMED Documentation Search

 Other Documents
Skip to end of metadata
Go to start of metadata

Current Version - Under Revision

This section outlines an example of a relational approach to representation of a full view of the SNOMED CT Resources. The example has been developed and tested using the Open Source database MySql Community Edition.

The example schema is based closely on the RF2 structure and is used in subsequent discussions of implementation issue and options for addressing those issues.

Note: The approach described here is only an illustrative example. It shows one way to represent the data but should not to be interpreted as a recommended or standard approach.

The general approach is as follows:

  • Each datatype in the RF2 specification is expressed with a common mapping to a database datatype:
    • Alternative implementations following the same general pattern could use a different datatype map but the mapping should be consistent within an implementation. Reasons for different datatype maps include implementer preferences and the capabilities of the database.
  • Each of the main file types specified in RF2 is instantiated as a database table:
    • Each table is named for the component type (e.g. sct2_Concept, sct2_Description, sct2_Relationship, sct2_Identifier).
    • Each field in these tables has column name from the release file
    • Each field is assigned the appropriate datatype (and where appropriate size).
  • Refsets are represented slightly differently from the other files:
    • One table structure for each distinct structure present in the release data:
      • der2_Refset.
      • der2_Reset_c.
      • der2_Refset_cc.
      • der2_Refset_ci.
      • der2_Refset_i.
      • der2_Refset_s.
      • der2_Refset_ss.
      • ... etc as new structures are added.
    • The first six fields in these tables have the common column names from the release file
    • The subsequent fields are named by type and position:
      • sctid1.
      • string1.
      • integer1.
      • ... etc.
    • This polymorphic field approach to column naming is used because column names may varies between release files for different Reference Set patterns, even when column data types are the same.

Note: Two other approaches could be used here.

      1. A separate table for each type of Refset based on column names rather than on structure. This would require a several tables with similar types of Relationships to other components .
      2. A single general purpose Refset table with multiple polymorphic fields. For example, strings that could be used to represent the other data types. This could cause inefficiencies for sctid type fields as the joins between these and target components would be heterogeneous.