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.

Feedback