Archives of the TeradataForum

Message Posted: Thu, 24 Jan 2002 @ 20:38:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Karen,

I have built a recursive structure a couple of times. Both times were to represent a hierarchy.

We currently have a recursive table that is the basis for a metadata application. The table contains a unique id as well as the unique id of the parent row. An artificial 'root' row with an id of 0 was inserted to ensure that everything had a parent. In this particular case, each row has a 'type' associated with it so I built a view for each type of row.

For example, there is a view for type=Database, there is a view for type=Table that includes information about its parent Database, then a view for type=Column that includes information about its parent table and grandparent Database. Ultimately, you have a view that navigates the recursion so that the user or application only sees a flat representation of the hierarchy. Each view was built as a building block digging deeper into the hierarchy.

There were several reasons why this structure was chosen. It makes it easy to write an application to navigate the hierarchy. A single macro allows read access to a particular point in the table as well as the ability to navigate to its parent, its siblings (same parent) and its children.

The second example was designed to store XML. The structure held any XML that was loaded. The challenge was supporting an application group that had rapidly changing and\or diverse XML streams. A C program parsed the XML and built the same parent\child relationships as in the first example. This allowed all the XML to use the same load process into an XML staging area. The application team abandoned the structure because the SQL to extract the data became difficult to write.

The difference between the two implementations is that the first example logically models the data so that extraction is simply building a view to represent the logical entity where the second example was difficult to use because it was too flexible.

I hope that helps.

Jim

	<Prev	Next>		<<First	<Prev	Next>	Last>>