Hierarchical Data Storage

[Bart Pietercil]

On 11-feb-07, at 10:02, Ruslan Zasukhin wrote:

> On 10/2/07 9:02 PM, "Bart Pietercil" <bart.pietercil at gmail.com> wrote:
>
>> Hi List,
>>
>> as I'm new to the features of Valentina, I post this question to
>> ascertain that I'm on the right track.
>>
>> The subject says it all: are there in the feature set of Valentina
>> things I should now when storing hierarchical data?
>>
>> The model is a company with loosely defined departments (cells in our
>> db). The cells are just groups of people, but that's not really
>> relevant for the discussion.
>>
>> What I would go for in a relational model is described here:
>>
>> http://www.sitepoint.com/article/hierarchical-data-database/2
>>
>>
>> However the model described in aforementioned page(the modified
>> preorder tree traversal storage method) to me has very high
>> resemblance with the network-model database described in Valentina's
>> documentation ( intro to the databasemodel)
>> Further down is noted that the Link abstraction model (re) introduces
>> the network model for Valentina.
>>
>> Now I don't really see if working with links would bring an advantage
>> to the modified preorder tree traversal storage method, but I might
>> be overlooking something.
>>
>> The goal must be : one query to obtain all ascendant ID's, one query
>> to obtain all descendants ID's. ( A manager of a cell is also manager
>> of the subordinate cell's personnel. So when he says show me all
>> (from all cells) my personnel status, I want to be able to obtain all
>> cell id's with one query (not using recursion), at least until
>> someone comes up with a better way that is :-)
>
> I have read this article completely. Its new idea for me to use 2  
> integer
> columns to improve things!
>
> At morning I have next thoughts:
>
> * we have BinaryLink - okay.
>
> * we can introduce its sub-class
>           HierarchyBinaryLink
>
> * so developer make table T1 with only ONE field (following example of
> article)
>
>         T1 ( productName, and of course more fields can be, ... )
>
> And now establish self-recursive HierarchyBinaryLink
>
>     CREATE HIERARCHY BINARY LINK link_name (T1)
>         USING { LIST | PREORDER }

What List would that be  in the USING clause ?

>
> So this class HierarchyBinaryLink will implement both ways.
> Will contains algorithms and data outside of T1.
>
>     It seems to me just COOL.  :-)
>

Do you mean that this Hierarchy binary link will "automagically"  
implement the left and right values for each  node ?


> Then we can make some specific methods on API level and of course  
> on SQL
> level to work with this link.
>
> I think this theme is very closed to idea of VCursorHierarchy which  
> we also
> plan to implement. It should return not flat table, but really  
> hierarchy
> information.
>
>
> ---------
> Another good point here.
>
> Even if you self will implement preorder tree on Valentina 2.5.5
> You will get much faster speed of updates
>
>     UPDATE ...  Left = left + 2
>
> Because this is operation on separate column, and Valentina keep  
> fields by
> columns, so you win > N times (when N is number of fields in table).


So for this Valentina (2.5.5) I basically implement a flat table T1  
like this

T1(rec_id 'automatic', values_i_need_in_the_table, leftfield,rightfield)

There is probably something to be gained if I am to introduce already  
a binary link (self join) in order to have an easy acces to the  
direct children of a certain item without needing to go through the  
whole stack. It looks like we would end up with a mixed model then.  
Recursive model (without recursion because we only use it for level n  
+ 1) +  preorder tree traversal for links that are going deeper than  
level n +1

TIA

Bart Pietercil