SQL Practical Guide

Search

SNOMED CT Document Library

    Loading...

Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

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 view

1

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 view

2

.

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 View snap_pref
 snap1_pref
 snap2_pref
Description View snap_description snap1_description snap2_description
Language Refset View snap_refset_language snap1_refset_language snap2_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 View snap_rel_pref snap1_rel_pref snap2_rel_pref
Concept View snap_concept snap1_concept snap2_concept
Relationship View snap_relationship snap1_relationship snap2_relationship

Preferred Term View

 snap_pref snap1_pref snap2_pref

Composite views designed to support review of changes may gather data from different views as illustrated in  <span style="color:red;">Error: Referenced caption id not found!</span><div class="macroHelp"><b>Captions on current page:</b><br/> - Table: with ID: <b>composite-source-views</b> <br/> - Table: with ID: <b>composite-view-including-composite-view</b> <br/> - Table: with ID: <b>composite-source-views</b> <br/> .

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

Composite View delta_inactive_concepts
 delta1_inactive_concepts
 delta2_inactive_concepts
Concept View delta_concept delta1_concept delta2_concept
Association Refset View snap_refset_association snap_refset_association snap_refset_association
Attribute Value Refset View snap_refset_attributevalue snap_refset_attributevalue snap_refset_attributevalue

Preferred Term View


 snap_pref snap_pref snap_pref

Fully Specified Name View

3

snap_fsn snap_fsn snap_fsn

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.

Example

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.


Footnotes
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.

Feedback
  • No labels