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
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
- 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
- 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:
- ... etc as new structures are added.
- The first six fields in these tables have the common column names from the
- The subsequent fields are named by type and position:
- ... etc.
- This polymorphic field approach to column naming is used because column names may
release files for different
Reference Set patterns, even when column data types are the same.
Note: Two other approaches could be used here.
- 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
- 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.