473,698 Members | 2,134 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Tree structure


I'm looking for some advice on how to design a set of tables to represent a
tree style table structure in MySQL.

I have a table that stores a set of items, with each item having a type. The
type of the item belongs to a tree structure of types.

e.g.

Type1 +---- Type2 ---- Type3 +---- Type4
| +---- Type5
|
+---- Type6 ---- Type7

Type8 +---- Type9
+---- Type10 +---- Type11
+---- Type12

I need to run queries on the item table to pull back all items that belong
to a type or any of it's child types

e.g.

where type = Type2 or type = Type3 or type = Type4 or type = Type5
or
where type = Type10 or type = Type11 or type = Type12

(I hope the diagram and psuedo code helps to explain what I'm trying to
achieve :-))

At the moment I store the structure of the type tree in an array within PHP
and build up a long where clause but someone hinted that there might be a
more efficient database approach to this - a cross reference table?
Unfortunately I didn't get a chance to discuss it further with him.

If anyone could suggest an approach or even point me in the general
direction of some good info on the concept I'd much appreciate it,

Steve
Jul 19 '05 #1
3 1985
Nevermind. I should have done some more searching first :-)

If anyone is trying to do the same thing then they might want to check out
this article:

http://www.sitepoint.com/article/1105

It has an excellent approach to it, starting from page 2.

Steve
"Steve" <me@removethisb it_stephenmcnab b.com> wrote in message
news:40******** **************@ news-text.dial.pipex .com...

I'm looking for some advice on how to design a set of tables to represent a tree style table structure in MySQL.

I have a table that stores a set of items, with each item having a type. The type of the item belongs to a tree structure of types.

e.g.

Type1 +---- Type2 ---- Type3 +---- Type4
| +---- Type5
|
+---- Type6 ---- Type7

Type8 +---- Type9
+---- Type10 +---- Type11
+---- Type12

I need to run queries on the item table to pull back all items that belong
to a type or any of it's child types

e.g.

where type = Type2 or type = Type3 or type = Type4 or type = Type5
or
where type = Type10 or type = Type11 or type = Type12

(I hope the diagram and psuedo code helps to explain what I'm trying to
achieve :-))

At the moment I store the structure of the type tree in an array within PHP and build up a long where clause but someone hinted that there might be a
more efficient database approach to this - a cross reference table?
Unfortunately I didn't get a chance to discuss it further with him.

If anyone could suggest an approach or even point me in the general
direction of some good info on the concept I'd much appreciate it,

Steve

Jul 19 '05 #2
Nevermind. I should have done some more searching first :-)

If anyone is trying to do the same thing then they might want to check out
this article:

http://www.sitepoint.com/article/1105

It has an excellent approach to it, starting from page 2.

Steve
"Steve" <me@removethisb it_stephenmcnab b.com> wrote in message
news:40******** **************@ news-text.dial.pipex .com...

I'm looking for some advice on how to design a set of tables to represent a tree style table structure in MySQL.

I have a table that stores a set of items, with each item having a type. The type of the item belongs to a tree structure of types.

e.g.

Type1 +---- Type2 ---- Type3 +---- Type4
| +---- Type5
|
+---- Type6 ---- Type7

Type8 +---- Type9
+---- Type10 +---- Type11
+---- Type12

I need to run queries on the item table to pull back all items that belong
to a type or any of it's child types

e.g.

where type = Type2 or type = Type3 or type = Type4 or type = Type5
or
where type = Type10 or type = Type11 or type = Type12

(I hope the diagram and psuedo code helps to explain what I'm trying to
achieve :-))

At the moment I store the structure of the type tree in an array within PHP and build up a long where clause but someone hinted that there might be a
more efficient database approach to this - a cross reference table?
Unfortunately I didn't get a chance to discuss it further with him.

If anyone could suggest an approach or even point me in the general
direction of some good info on the concept I'd much appreciate it,

Steve

Jul 19 '05 #3
Nevermind. I should have done some more searching first :-)

If anyone is trying to do the same thing then they might want to check out
this article:

http://www.sitepoint.com/article/1105

It has an excellent approach to it, starting from page 2.

Steve
"Steve" <me@removethisb it_stephenmcnab b.com> wrote in message
news:40******** **************@ news-text.dial.pipex .com...

I'm looking for some advice on how to design a set of tables to represent a tree style table structure in MySQL.

I have a table that stores a set of items, with each item having a type. The type of the item belongs to a tree structure of types.

e.g.

Type1 +---- Type2 ---- Type3 +---- Type4
| +---- Type5
|
+---- Type6 ---- Type7

Type8 +---- Type9
+---- Type10 +---- Type11
+---- Type12

I need to run queries on the item table to pull back all items that belong
to a type or any of it's child types

e.g.

where type = Type2 or type = Type3 or type = Type4 or type = Type5
or
where type = Type10 or type = Type11 or type = Type12

(I hope the diagram and psuedo code helps to explain what I'm trying to
achieve :-))

At the moment I store the structure of the type tree in an array within PHP and build up a long where clause but someone hinted that there might be a
more efficient database approach to this - a cross reference table?
Unfortunately I didn't get a chance to discuss it further with him.

If anyone could suggest an approach or even point me in the general
direction of some good info on the concept I'd much appreciate it,

Steve

Jul 19 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
14033
by: Steve Johnson | last post by:
Been banging my head on this for two days now. Hope someone can help! My test program below is in the form of a single JSP, with a Node class build in. (All the coded needed to run is below.) The technical requirements: 1) Store tree data in the database so that it can be extracted as a tree structure. For test purposes,
2
5804
by: ragha | last post by:
Dear friends I am emulating thr tree structure mentioned in the article http://www.15seconds.com/issue/010921.htm I have succesfully created the folder structure for level 2 I need this structure till 5 level is there any easier way to generate this using xsl My xml tree node is
1
2005
by: googleo | last post by:
Hi, in my application I want to handle and store data in a hierarchic data structure. For example: persons who manage houses; houses have various numbers of floors; floors have various numbers of rooms etc. My first concept is to use a tree structure, to ideally save the whole tree to file and load from file.
1
7175
by: Srihari | last post by:
I'm trying to develop a tree structure using javascript. The node values of the tree are generating from a mysql table depending on login. The tree structure contains 3 sub levels. I developed static HTML tree using http://www.treeview.net. now i need to generate this tree dynamically. Can any one has code for this?
4
612
by: Stephan Tobies | last post by:
Hi everyone, I am looking for a good data structure that could be used to represent families of trees with shared sub-trees and copy-on-write semantics. On a very abstract level, I would like to have an API like this: Let Node be a suitably defined data structure. Then the following functions shall be supported:
3
2553
by: _DS | last post by:
Problem with mapping a directory tree to a tree control: It takes a while to recurse subdirs and map them to nodes. This is solved in some books I've seen (MacDonald, Albahari) by reading the subdir only when its node on the tree control is opened. Has anyone pre-packaged this functionality into a control?
1
2744
by: David Hirschfield | last post by:
I've written a tree-like data structure that stores arbitrary python objects. The objective was for the tree structure to allow any number of children per node, and any number of root nodes...and for it to be speedy for trees with thousands of nodes. At its core, the structure is just a list of lists arranged so that if node A has children B and C and node B has child D the data looks like: A = B =
5
3721
by: hankypan1 | last post by:
Hi All, I need a tree data structure for my application. It is the non -cyclic simple tree where i can have any number of children node and each child can recursively become a sub tree like a normal tree. Now the thing is i can popullate my tree at compile time like a global data. Since i know my tree definition at compile time, instead of using pointers to point to siblings or child nodes, i am planning to use the array with...
8
1796
by: =?ISO-8859-1?Q?m=E9choui?= | last post by:
Problem: - You have tree structure (XML-like) that you don't want to create 100% in memory, because it just takes too long (for instance, you need a http request to request the information from a slow distant site). - But you want to be able to request data from it, such has "give me all nodes that are under a "//foo/bar" tree, and have a child with an "baz" attribute of value "zzz". Question :
0
2201
by: mac | last post by:
I found that with memory allocating techniques used nowadays (addresses alignment, eg. on 32bit machines) one can detect loops in a tree structure very fast, without using extra memory. This is due to a possibility of storing extra information in unused bits of the tree pointers (it works for linked lists too). One can say it is dangerous or obvious, but I haven't seen it anywhere, and maybe it will be useful for someone. The procedure...
0
8598
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9152
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9016
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8887
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7709
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5858
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4613
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2321
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1997
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.