hierarchical data handling

[anirban1087]

Hi,
I am working on a project where I have to deal with hierarchical data.
The data looks like
Member-1
|
---------------
| |
member2 member3
|---------------| |
member4 member6 ----------------
| |
member7 member5
I have searched a lot to find a good solution to store this data, but haven't got a good one. Can any one help me on this problem?

Thanks in advance.

[rje7]

oracle supports hierarchical database by using types. those type of data can be handled using the queries like in the following page.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm

[mschroeder]

How proficient are you with MySQL and PHP? There are usually two ways I see this solved. The easier to grasp of the two being recursion and the more complex but powerful would be using something like Modified Preorder Tree Traversal (MPTT).

I was not aware Oracle supported a native hierarchal data type will definitely keep that in mind.

http://articles.sitepoint.com/article/hierarchical-data-database
http://en.wikipedia.org/wiki/Tree_traversal
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[anirban1087]

Hi mschroeder,
I have already seen those links before the posting. Thanks for your response. But those links didn't solved my problem. Here is the details of my problem -

1. Each member can have two physical hands (L & R) and n logical hands (under other members down stream in the same tree).

E.G.
A. 49400 is a member with two hands 108984(L) & 119968(R)
B. 119968 has two hands 119967(L) & 119991(R) both are sponsored by 49400 i.e. logical hands 0f 49400.

C. 119967 has two hands 119091(L) & 119992(R) where 119091 is sponsored by 119968 & 119992 is by 49400.

D. 108984 has two hands 119087(L) & 119088(R), where 119087's L is 75271 & 119088's R is 75272 and both are sponsored by 108984.

Please help me to solve this problem.

Thanks in advance.

[mschroeder]

@anirban1087

I have attached a visual representation of what I gather reading your description.
From your earlier post I see you are trying to develop a storage solution for this.

I still think MPTT would be the way to store the hierarchical as illustrated in the second attachment I included. Once the data is stored in this fashion you can use the mechanism described in those links I provided to retrieve, insert and update your data with ease.

To handle the "logicl" hands, I would have a many-to-many join table where the member is joined to n members indicating the number of sponsored or logical hands that exist.

So 49400 has 2 physical hands (L & R) and has 3 logical hands.
The physical hands would be from selecting the direct child nodes, this would be true for all relationships. The logical hands would come from a separate table that maintained the logical relationship.

|MemberId | HandId   |
+---------+----------+
| 49400   | 119167   |
| 49400   | 119992   |
| 49400   | 119991   |
+---------+----------+

The description as to how you want to work with this data is rather vague so please elaborate if I'm way off base.

[anirban1087]

mschroeder
Thanks a lot for your response. You have done a great work. It will solve my problem.