Dynamic Data Driven Variable Hierarchical Structures in SQL


SQL can support the creation of variable hierarchical structures in a number of differently controlled operations that can be combined. Michael M. David describes a powerful and flexible automatic method of dynamic structure generation.

Review of Already Covered Dynamic Structure Operations

SQL can support the creation of variable hierarchical
structures in a number of differently controlled operations that can be
combined. Three have been shown in the previous articles in this series on SQL
hierarchical processing
. These have been dynamic: hierarchical structure
data modeling; hierarchical structure joining; and SQL’s variable SELECT list to
specify the output. This article describes data driven variable structure
generation and in a future article variable structure creation will be described
using structure transformations. All of these different structure operations
all generate structures in a different way.

Review of Linking Below Root

The capability of linking
below the root
was first described in the previous article. This capability
is also used in supporting data driven variable structures. So this section is repeated
from the previous article and it also uses the same data and structures definitions
which are also shown here in figure 1.

Using the SELECT statement in Figure 1 below to join the defined
ABC and XYZ view structures hierarchically, the lower level structure’s root
node X has semantic control over node Z even though the Z node is used as the
lower structure’s link point. This linking operation should not hierarchically elevate
the Z node above the X node. The data link points between the structures being
combined are used to match up data relationships between the structures being
combined. This is actually a data filtering operation applied at the lower
structure’s Z node and will filter the entire lower structure hierarchically from
the Z node outward in all path directions. This is possible for non relational
data because all data has been placed in relational rowsets.

Review of linking below

The lower level structure has its hierarchical structure
established before the linking process and should be treated as a single fixed
entity regardless of where it is linked to even if it is a logical structure. SQL
does this automatically when it materializes the view. Since node Z is already
affected and controlled by other nodes above it, the hierarchical data modeling
link point is the root node X of the lower structure, as is shown in Figure 1,
while the data relationship data filtering occurs between nodes C and Z. This
consistently preserves the data semantics and structures.

Variable Structure Creation by Looking Back

While the ON clause is meant to make the relationship
between two data structures, it can also specify data filtering that is
specified in the same local manner to reference data at the join point or
further up the active pathway. This is shown in Figure 2 below where AND
A.a=2
has been added to the SELECT statement as shown. This has the effect
of causing the XYZ view join to take place or not to take place depending on if
A.a=2 is true or not. This occurs whenever this join operation is performed so
it can occur on and off as needed. This causes a variable structure generation
controlled by data value. The two possible structure generations are shown in
Figure 2.

Structure generation by looking back

Variable Structure Creation by Looking Forward

In the previous example in Figure 2, the variable structure
was controlled by a data value further up the pathway, but as seen with linking
below the structure root in Figure 1, data references can also be made further
down the structure. This is shown in Figure 3 where a data value further down
the pathway controls whether a join takes place or not in order to control
variable structures. The XYZ view must be accessed in order to test if it is
needed because it contains the data item needing testing. This is a look-a-head
operation, which is performed automatically in SQL because of its view materialization
processing.

Structure generation by looking forward

Variable Structure Creation Using Multiple Choice Selection

So far in the examples shown, the choice has been controlling
whether a substructure is dynamically created or skipped. It is possible to
have a choice of selecting one from any number of different structures. This is
performed by including multiple LEFT Joins with different views as shown below
in Figure 4. Testing different data items allows more than one view selection generated
at a time.

Structure generation using multiple choices

Multipath Variable Structure Creation

Variable structure creation can also be applied
independently to any number of pathways in a structure. This is performed by
using multiple LEFT Joins as in the previous example in Figure 4 except the
LEFT Joins target different pathways in the structure. This is shown in Figure
5 where look back and look forward methods are both used. Not shown is that both
variable structures can be generated since they are testing a different data
sources (A.a and O.o).

Multipath structure generation

Nested Variable Structure Creation

Nested structure generation can be applied top down. This is
shown in Figure 6 where view XYZ is applied variably following view ABC, and
view MNO is applied to view XYZ variably. The XYZ view itself could variably
invoke the MNO view. When this is known in advance, this is a better more
powerful and flexible way to perform variable nested structure generation
because it uses reuse automatically and abstracts the complexity of nesting.

Nested structure generation

Conclusion

The data driven variable structure generation shown in this
article is a very powerful and flexible automatic method of dynamic structure
generation. The different data driven techniques shown can be combined as
needed. The SQL hierarchical examples in this article can be tested using my
company’s online interactive ANSI SQL Transparent XML Hierarchical Processor prototype.

»


See All Articles by Columnist

Michael M. David

Michael M. David
Michael M. David
Michael M. David is the founder of Advanced Data Access Technologies, Inc. Previously, he was the lead XML architect for NCR/Teradata, and served as their representative to the ANSI SQLX Group. Before that he was a staff scientist for Teradata and designed high level multi-featured SQL utilities. He has more than 25 years of experience researching and designing commercial nonprocedural heterogeneous database hierarchical query processing products using flat, relational and hierarchical data. From this experience, he authored the book Advanced ANSI SQL Data Modeling and Structure Processing, as well as numerous papers and articles on this subject.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles