Page tree


Data Sources for Composite Views

Composite views need to get data from snapshot views. In most cases the requirements will be met by use of the current snapshot view1 of each of the relevant tables. However, when reviewing past data the relevant retrospective views will need to be accessed. For this reason, consideration should be given to creating similar composite views for each supported snapshot view2 .

Most composite views should to gather all the required data from the table views for the same snapshot as illustrated in  Table 4.8.1-1.

Table 4.8.1-1: Source Views for Data in Composite Views of Different Snapshots

Composite Viewsnap_pref
Description Viewsnap_descriptionsnap1_descriptionsnap2_description
Language Refset Viewsnap_refset_languagesnap1_refset_languagesnap2_refset_language

Composite views may themselves gather data from other composite views. For example as shown in  Table 4.8.1-2 gets preferred term data from the preferred term composite views shown above.

Table 4.8.1-2: Composite View Including Data from Another Composite View

Composite Viewsnap_rel_prefsnap1_rel_prefsnap2_rel_pref
Concept Viewsnap_conceptsnap1_conceptsnap2_concept
Relationship Viewsnap_relationshipsnap1_relationshipsnap2_relationship

Preferred Term View


Composite views designed to support review of changes may gather data from different views as illustrated in  Table 4.8.1-3.

Table 4.8.1-3: Source Views for Data in Historical Composite Views of Different Delta Views

Composite Viewdelta_inactive_concepts
Concept Viewdelta_conceptdelta1_conceptdelta2_concept
Association Refset Viewsnap_refset_associationsnap_refset_associationsnap_refset_association
Attribute Value Refset Viewsnap_refset_attributevaluesnap_refset_attributevaluesnap_refset_attributevalue

Preferred Term View


Fully Specified Name View3


Representation of Composite Views

Composite views should be represented as database views rather than a physical database tables. Composite views denormalize data by combining the same data in different views therefore attempts to represent composite views as database tables is likely to rapidly multiply the size of the database. The example below is just one of many cases where creating concrete database tables to accommodate composite views might seem an attractive idea. However, pursuing this would create redundant data with few benefits, a major impact on storage requirements and a significantly more complex maintenance process when reviewing and installing future release packages. In contrast, representing composite views as database views, ensures the data is derived in real-time from tables representing the authoritative content of the full and/or snapshot release files.


Most English language descriptions are either preferred or acceptable in both US and GB english. Therefore instantiating tables that represent the sets of preferred and acceptable terms in either or both dialects would not only duplicate much of the data in that table but would require even more space to duplicate the relevant indexes. In addition to the impact of disk space, data duplicated in these composite tables would need updating to take account of new releases.

Ref Notes
1 As noted in 4.6.3. Optimizing Versioned Table Views the current snapshot may be represented as tables or database views. While this may make a difference to performance it does not make any difference to the design of composite views.
2 In the SNOMED CT example database, most composite views have been created for the current snapshot (snap) and for both of the configurable retrospective snapshot views (snap1 and snap2). However, composite views that access either the transitive closure (snap_transclose) or proximal primitives (snap_proxprim) are not supported for the retrospective snapshots. This is because those tables are at present on available for the current snapshot view.
3 The views snap_fsn, snap1_fsn and snap2_fsn are composite views similar to snap_pref but return the fully specified name rather than the preferred term.

  • No labels