Hierarchical Data Storage

[Ruslan Zasukhin]

On 10/2/07 9:02 PM, "Bart Pietercil" <bart.pietercil at gmail.com> wrote:

Hi Bart,

> 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

Right.

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

1) I want underline that exists 2 different aspects:

    * how you store your data.
    * how you later can query your data.

2) on above URL page the most ugly thing is that parent is a string.
    Actually I think this is just example for article.
    in real life this will be PK + FK based on ULONG for example.

3) in Valentina this table you can do best of all using

    CREATE TABLE T1(
        parentPtr as ObjectPtr to T1,
        ...
    );


-------- 
> 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 :-)

And you want do this with help of SQL I assume.

No, we do not have yet such command, although I believe similar things
exists in e.g. Oracle.

Please add into Mantis this feature request. We will need do some research
to choose best way how to implement this for general case.

I recall that into SQL 2003 standard was added such features -- support of
queries on hierarchical structures. But they are so ugly...
I have read example of such query -- is was one page of a book by size!

Seems Oracle have own syntax for this ...

Deal is that you have ask here may be simplest query. Just collect all
ascendants. Easy see that can exists much more complex things as
    find ascendant  on 3d level with name = 'xxxx'
    which have ascendant on 2n level with age < 17


-------------
Btw,  May be language for such searches is Prolog/Lisp  :-)
    

------------
btw2,   you sure that on above URL page is described YOUR task?
    as I see here is described tree with ORDER of nodes.
    in your case I think order of nodes do not present


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]