Storing Hierarchical Data in a Database

[ArticleBot]

This is an article discussion thread for discussing the SitePoint article, "Storing Hierarchical Data in a Database"

[ryushe]

Hi, Í'm using the first version at this moment as I have a many-to-many relation in my DB. So my question might be a bit stupid, but can anyone explain to me how I can have the first level horizontal, and the second layer under that one (also horizontal), etc etc?
Also how to make a visual indicator (link color change) which item is active?

[David C]

Do you mean horizontal in the sense of presentation? If so, the DB query probably wouldn't change, it would be done with HTML/CSS.

[ryushe]

Yes, presentation wise. Problem is, the main "browser" I'm creating this for does not (yet) understand CSS, and at most basic html tags, although no table tags yet.
I "need" something like:

parent1 | parent2 | parent3
child 1 | child 2 | child 3 | child 4
subchild 1 | subchild 2

I've been playing around with creative < b r / > placements, but no luck yet. Only option I see is to make a duplicate function which is triggered by a var passed onto it to activate sublevel. Of course this should be able to be done without creating extra functions I'd say. Any suggestions?

[Big Fat Bob]

Yo

You might find this thread interesting then

http://www.sitepoint.com/forums/showthread.php?t=186601

[Dr Livingston]

XML does have it's uses, though in your point you could query the XML with XPATH though you'd be limited to that given data within the XML file, whereas you couldn't (easily) query the data in one XML file against another one, suchas product catelog and products.

Two seperate though interwinded entities that is where the database has the upperhand... relational data in other words.

On your last question, XML is a great technology... For distribution but not for performing queries. There are a number of XML databases out there, but I cannot see any benifit of using one over a relational database if all your going to do is to perform lookups/queries

[REMIYA]

Is there really a need of left and right value?
Why do we need the left and right?
Isn't just the parent sufficient?

[REMIYA]

I am asking because RDLDB [doc] is hierarchy-related. And I have never felt the need of having right or left and still have never been wondering which child comes first. The first in is the first out (The first child is first, the second is second, the third is third, etc).
That is why I was wondering why would anyone use right and left. And why would anyone need so many computations.

[timvw]

Btw, one could also have a look at pear's nested set class...

[Bokisa]

Hi,

Does somebody know how to get next result:

(I am using same tree as in article):

For example, I wand to target only "Cherry" node;

Food
--Fruit
----Red
-------Cherry
----Yellow
----..more Fruit
--Meat
--...more Food

As can be seen here, I want to display only "Cherry" subtree in complete, and other leafs only in start. If some code need to be added in PHP, this will help too.

regards

Thanks in advance!

[p3t3r]

To retrieve single path to 'banana' you would use the following query

SELECT p.title
FROM tree1 AS node,
tree1 AS p
WHERE node.lft BETWEEN p.lft AND p.rgt
AND node.title = 'banana'
ORDER BY p.lft;

however this won't work if we had 'banana' in more than one node.
If the values of all nodes from one of the bananas were known can the right 'banana' be found with one query?

[chame]

what if a store procedure is created to call the items from the database. How would you update the code to display this information in the correct order on the webpage?

I am creating something similar but using ASP and vbscript and I'm having a difficult time getting the data to display correctly. right now it's display all the items as parents. Any suggestions?

[geniegate]

I had a problem like this awhile ago.

Here's my solution:
http://geniegate.com/art/pdf/tree-st...l-database.pdf

It was kind of nice to be able to do this:

SELECT 
  * 
FROM 
  directory 
WHERE 
  lookup_directory_ancestor_path(id) LIKE 'Blah/%';

And not clutter the program up with a bunch of tree handling stuff.

I was going to go with flat files at first, but decided I wanted relational after thinking about some of the other aspects (integrity checks).

You can also create database-side indexes, but in practice, I haven't needed them. (I just created an index, then dropped it, to see if it'd actually work.)

[tudoroiuj]

how can i find the level?

[r937]

how can i find the level?

which level?

please show your table design

[Kalon]

how can i find the level?

My apologies if this has already been suggested earlier, but this thread is too long for me to go back looking through all the posts.

To store hierarchial data in a database I would recommend using The Nested Set Model. This link also has some very useful sql scripts on extracting hierarchy information from the database.

I have posted some sql scripts on how to move hierarchial nodes.