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.
|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.
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.
|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
Fully Specified Name View3
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.
|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.|