By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,479 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

I have sub-categories but want to display full category path

P: n/a
Let's say I have a simple web application running with just two MySQL
tables. The tables structure is as follows:

Table: category
category_id (PK)
category_name
parent_category (FK) //references category_id in this table

Table: link
link_id (PK)
link_name
link_description
category_id (FK) //references category_id in category table

Here is the data in the category table
1, England, 0
2, West Yorkshire, 1
3, Batley, 2
4, Leeds, 2
5, Bradford, 2

As you can see Batley, Leeds and Bradford are sub-categories of West
Yorkshire which itself is a sub-category of England.

What I want to display when I am browsing through sub-categories of links is
not only the name of that sub-category but it's category's parents, grand
parents and so on like the below example.

UK >West Yorkshire >Batley

I see this on a lot of directory sites but none of my PHP books cover how
this is done. Must be quite simple so can someone please point me in the
right direction. Hope I've explained it well enough, haven't a clue what
this process is called.

Cheers

Phil

Feb 2 '07 #1
Share this Question
Share on Google+
24 Replies


P: n/a
I think it is not a good table design. You need to learn database
normalization first.
http://dev.mysql.com/tech-resources/...alization.html

--
http://www.mastervb.net
http://www.theukmap.com
On Feb 2, 7:58 am, "Phil Latio" <phil.la...@f-in-stupid.co.ukwrote:
Let's say I have a simple web application running with just two MySQL
tables. The tables structure is as follows:

Table: category
category_id (PK)
category_name
parent_category (FK) //references category_id in this table

Table: link
link_id (PK)
link_name
link_description
category_id (FK) //references category_id in category table

Here is the data in the category table
1, England, 0
2, West Yorkshire, 1
3, Batley, 2
4, Leeds, 2
5, Bradford, 2

As you can see Batley, Leeds and Bradford are sub-categories of West
Yorkshire which itself is a sub-category of England.

What I want to display when I am browsing through sub-categories of links is
not only the name of that sub-category but it's category's parents, grand
parents and so on like the below example.

UK >West Yorkshire >Batley

I see this on a lot of directory sites but none of my PHP books cover how
this is done. Must be quite simple so can someone please point me in the
right direction. Hope I've explained it well enough, haven't a clue what
this process is called.

Cheers

Phil

Feb 2 '07 #2

P: n/a

"lorento" <la**********@yahoo.comwrote in message
news:11**********************@q2g2000cwa.googlegro ups.com...
>I think it is not a good table design. You need to learn database
normalization first.
http://dev.mysql.com/tech-resources/...alization.html

--
http://www.mastervb.net
http://www.theukmap.com
On Feb 2, 7:58 am, "Phil Latio" <phil.la...@f-in-stupid.co.ukwrote:
>Let's say I have a simple web application running with just two MySQL
tables. The tables structure is as follows:

Table: category
category_id (PK)
category_name
parent_category (FK) //references category_id in this table

Table: link
link_id (PK)
link_name
link_description
category_id (FK) //references category_id in category table

Here is the data in the category table
1, England, 0
2, West Yorkshire, 1
3, Batley, 2
4, Leeds, 2
5, Bradford, 2

As you can see Batley, Leeds and Bradford are sub-categories of West
Yorkshire which itself is a sub-category of England.

What I want to display when I am browsing through sub-categories of links
is
not only the name of that sub-category but it's category's parents, grand
parents and so on like the below example.

UK >West Yorkshire >Batley

I see this on a lot of directory sites but none of my PHP books cover how
this is done. Must be quite simple so can someone please point me in the
right direction. Hope I've explained it well enough, haven't a clue what
this process is called.

Cheers

Phil
It's often called 'breadcrumb navigation'.

Further to lorento's incredibly useful top post (:p)... I think what he's
trying to say that perhaps the relationship between parent and children in
your tree structure (which seems to be what you are trying to represent in
your table), would be best expressed in a separate table... so you'd have
two tables:

Category
-------------------------
category_id (PK)
category_name

Category_Relations
-------------------------
parent_category_id (FK)
child_category_id (FK)

parent_category_id + child_category_id in the second table together would
form a compound primary key for the table.

The result of this, of course, would be that each child *might* become a
child of multiple parents. This could be an advantage or a disadvantage,
depending on how you want the children to behave.

The other approach you could use, I guess, would be to identify the
following entities/tables (... yes, virginia, I know they're not the same
thing):

Country
Region
Town

If your structure requires that you have infinite tree 'nodes' however, this
approach will not work, and so your current structure works better. I guess
it depends on whether you are trying to construct an n-leafed tree, or
enforce strict locality rules... time for a specification?

It is incorrect IMO to say that your current structure is 'not a good table
design', since you may, indeed be trying to construct a 'tree' rather than a
strict entitiy model.

Horses for courses.
Feb 2 '07 #3

P: n/a
la**********@yahoo.com says...
I think it is not a good table design. You need to learn database
normalization first.
http://dev.mysql.com/tech-resources/...alization.html
Internally self-referencing tables are actually *good* database design,
and satisfy normalization rules.

<aside>

Because it's difficult to follow context.

Why is top-posting bad?

</aside>
Here is the data in the category table
1, England, 0
2, West Yorkshire, 1
3, Batley, 2
4, Leeds, 2
5, Bradford, 2

What I want to display when I am browsing through sub-categories of links is
not only the name of that sub-category but it's category's parents, grand
parents and so on like the below example.

UK >West Yorkshire >Batley

I see this on a lot of directory sites but none of my PHP books cover how
this is done. Must be quite simple so can someone please point me in the
right direction. Hope I've explained it well enough, haven't a clue what
this process is called.
It's most commonly called "breadcrumbing" (from the Hansel & Gretel fairy
tale).

The following pseudo-code should do the job, but is *not* optimised as I
nowadays use Oracle which supports the wonderful CONNECT BY construct to
recursively query self-referencing tables, so this is a simple
slegdehammer MySQL version.

My MySQL (and some of my PHP) is *very* rusty, but if there are any
errors, at least it will give you an idea of how it might be done:

Note also that it doesn't cater for bad data, eg. a link_id which won't
resolve back to an eventual parent with a category_id of 0.

<?php
function makeBreadcrumbs($link_id) {
// set up a counter
$counter=1;
// get the name for this link_id
$query="SELECT link_name, category_id FROM link WHERE link_id=$link_id";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
// put it as the first row in an array
$bread_crumb_array=array();
$bread_crumb_array[$count]=$row("link_name");
// get the parent
$category_id=$row("category_id");
// now climb the tree if not already at the top
while($category_id!=0) { // careful of errors here !!!
// this time get the parent's name
$query="SELECT link_name, category_id FROM link WHERE link_id=
$category_id";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
// increment the counter
$counter++;
// put it in the array
$bread_crumb_array[$counter]=$row("link_name");
// get the next parent
$category_id=$row("category_id");
}
// now you have an array of names to the top in reverse number order
// get the last one first
$breadCrumbString=$bread_crumb_array[$counter];
// work back through the array
while($counter>0) {
$breadCrumbString.=" >".$bread_crumb_array[$counter];
// decrement the counter
$counter--;
}
return $breadCrumbString;
}
// test using the example
$link_id=3;
echo makeBreadcrumbs(link_id);
?>

Geoff M
Feb 2 '07 #4

P: n/a

"Geoff Muldoon" <ge***********@trap.gmail.comwrote in message
news:MP************************@news.readfreenews. net...
la**********@yahoo.com says...
>I think it is not a good table design. You need to learn database
normalization first.
http://dev.mysql.com/tech-resources/...alization.html

Internally self-referencing tables are actually *good* database design,
and satisfy normalization rules.

<aside>

Because it's difficult to follow context.

Why is top-posting bad?

</aside>
[snip - see OP]

I agree... nothing wrong with self-reference if you are trying to construct
a tree.

Feb 2 '07 #5

P: n/a
..oO(Phil Latio)
>As you can see Batley, Leeds and Bradford are sub-categories of West
Yorkshire which itself is a sub-category of England.

What I want to display when I am browsing through sub-categories of links is
not only the name of that sub-category but it's category's parents, grand
parents and so on like the below example.
Unfortunately you can't do that with a single SQL statement (unless you
use a nested set structure, but that would be real overkill). You have
to walk up the tree in a loop and fetch every parent node until you
reach the root node.

Micha
Feb 2 '07 #6

P: n/a
On 2 Feb, 00:58, "Phil Latio" <phil.la...@f-in-stupid.co.ukwrote:
Let's say I have a simple web application running with just two MySQL
tables. The tables structure is as follows:

Table: category
category_id (PK)
category_name
parent_category (FK) //references category_id in this table

Table: link
link_id (PK)
link_name
link_description
category_id (FK) //references category_id in category table

Here is the data in the category table
1, England, 0
2, West Yorkshire, 1
3, Batley, 2
4, Leeds, 2
5, Bradford, 2

As you can see Batley, Leeds and Bradford are sub-categories of West
Yorkshire which itself is a sub-category of England.

What I want to display when I am browsing through sub-categories of links is
not only the name of that sub-category but it's category's parents, grand
parents and so on like the below example.

UK >West Yorkshire >Batley

I see this on a lot of directory sites but none of my PHP books cover how
this is done. Must be quite simple so can someone please point me in the
right direction. Hope I've explained it well enough, haven't a clue what
this process is called.

Cheers

Phil
This is an adjacency list model and the solution, as others have
pointed out, is to use a php loop - or, alternatively, switch to a
nested set model! However, if you know that the 'depth' of the tree is
never going to be greater than 4, say, then you can just join the
table to itself that number of times to guarantee you're at the top of
the tree. Here's an example...

SELECT *
FROM relations AS A
LEFT JOIN relations AS B ON B.parent = A.id
LEFT JOIN relations AS C ON C.parent = B.id
LEFT JOIN relations AS D ON D.parent = C.id
WHERE ISNULL( A.parent )

Notice in this example that the id at the very top of the tree has a
parent id of NULL and not 0.

Feb 2 '07 #7

P: n/a
Unfortunately you can't do that with a single SQL statement (unless you
use a nested set structure, but that would be real overkill). You have
to walk up the tree in a loop and fetch every parent node until you
reach the root node.
Thanks for your answer.

Cheers

Phil
Feb 2 '07 #8

P: n/a
It's most commonly called "breadcrumbing" (from the Hansel & Gretel fairy
tale).
Thanks. Popped that into Google and came up with whole load of people asking
the same question as me. Surprised I didn't find any tutorials on this
subject.
<?php
function makeBreadcrumbs($link_id) {
// set up a counter
$counter=1;
// get the name for this link_id
$query="SELECT link_name, category_id FROM link WHERE link_id=$link_id";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
// put it as the first row in an array
$bread_crumb_array=array();
$bread_crumb_array[$count]=$row("link_name");
// get the parent
$category_id=$row("category_id");
// now climb the tree if not already at the top
while($category_id!=0) { // careful of errors here !!!
// this time get the parent's name
$query="SELECT link_name, category_id FROM link WHERE link_id=
$category_id";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
// increment the counter
$counter++;
// put it in the array
$bread_crumb_array[$counter]=$row("link_name");
// get the next parent
$category_id=$row("category_id");
}
// now you have an array of names to the top in reverse number order
// get the last one first
$breadCrumbString=$bread_crumb_array[$counter];
// work back through the array
while($counter>0) {
$breadCrumbString.=" >".$bread_crumb_array[$counter];
// decrement the counter
$counter--;
}
return $breadCrumbString;
}
// test using the example
$link_id=3;
echo makeBreadcrumbs(link_id);
?>
Thanks for this code. It will assist me greatly.

I will actually post a working solution when I have one so everyone can
share it.

Cheers

Phil
Feb 2 '07 #9

P: n/a
Phil Latio wrote:
Let's say I have a simple web application running with just two MySQL
tables. The tables structure is as follows:

Table: category
category_id (PK)
category_name
parent_category (FK) //references category_id in this table

Table: link
link_id (PK)
link_name
link_description
category_id (FK) //references category_id in category table

Here is the data in the category table
1, England, 0
2, West Yorkshire, 1
3, Batley, 2
4, Leeds, 2
5, Bradford, 2

As you can see Batley, Leeds and Bradford are sub-categories of West
Yorkshire which itself is a sub-category of England.

What I want to display when I am browsing through sub-categories of links is
not only the name of that sub-category but it's category's parents, grand
parents and so on like the below example.

UK >West Yorkshire >Batley

I see this on a lot of directory sites but none of my PHP books cover how
this is done. Must be quite simple so can someone please point me in the
right direction. Hope I've explained it well enough, haven't a clue what
this process is called.

Cheers

Phil
Phil,

What you need is recursive SQL, which isn't supported by MySQL (yet,
anyway).

If you're running a recent version of MySQL you can do it with a stored
procedure. Try asking in comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Feb 3 '07 #10

P: n/a
Try asking in comp.databases.mysql.
Oh, the irony of it.

Feb 3 '07 #11

P: n/a
strawberry wrote:
>Try asking in comp.databases.mysql.

Oh, the irony of it.
And why is that? This can easily be done with recursive SQL. MySQL
doesn't support it yet, but there are ways to do it.

But it wouldn't be appropriate for a PHP newsgroup.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Feb 3 '07 #12

P: n/a
On Feb 3, 3:41 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:
strawberry wrote:
Try asking in comp.databases.mysql.
Oh, the irony of it.

And why is that? This can easily be done with recursive SQL. MySQL
doesn't support it yet, but there are ways to do it.

But it wouldn't be appropriate for a PHP newsgroup.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@attglobal.net
==================
?
The OP originally posted 'accidentally' to comp.databases.mysql before
quickly re-posting here -and hence, apparently 'multi-posting' - for
which he was soundly chastised.

Feb 3 '07 #13

P: n/a
strawberry wrote:
On Feb 3, 3:41 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:
>strawberry wrote:
>>>Try asking in comp.databases.mysql.
Oh, the irony of it.
And why is that? This can easily be done with recursive SQL. MySQL
doesn't support it yet, but there are ways to do it.

But it wouldn't be appropriate for a PHP newsgroup.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@attglobal.net
==================

?
The OP originally posted 'accidentally' to comp.databases.mysql before
quickly re-posting here -and hence, apparently 'multi-posting' - for
which he was soundly chastised.
Ah, OK.

I can see from his original question he was asking how to do it in PHP.
But I think that was the wrong question. Just asking how to do it in
comp.databases.mysql could have given him an SQL answer.

But it's also why cross-posting is better than multi-posting.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Feb 3 '07 #14

P: n/a
Ah, OK.
>
I can see from his original question he was asking how to do it in PHP.
But I think that was the wrong question. Just asking how to do it in
comp.databases.mysql could have given him an SQL answer.

But it's also why cross-posting is better than multi-posting.
Sincere thanks for your input, Jerry. The person who chastised me for cross
posting actually posted again and apologised so that is sorted now.
Originally I was debating which group would be best but since I am learning
PHP, I thought comp.lang.php would be most appropriate. I wasn't trying to
cross post or multi-post, just hit this group which I failed miserably with
my first shot

I actually now have a working solution written in PHP. It's built upon what
Geoff Muldoon posted on this thread although it does have a minor bug (well
irritation more than bug) which currently has me puzzled.

Cheers

Phil
Feb 4 '07 #15

P: n/a
On Feb 2, 5:58 am, "Phil Latio" <phil.la...@f-in-stupid.co.ukwrote:
Let's say I have a simple web application running with just two MySQL
tables. The tables structure is as follows:
<snip>
As you can see Batley, Leeds and Bradford are sub-categories of West
Yorkshire which itself is a sub-category of England.

What I want to display when I am browsing through sub-categories of links is
not only the name of that sub-category but it's category's parents, grand
parents and so on like the below example.

UK >West Yorkshire >Batley
<snip>

Answer is Nested Set Model architecture <http://dev.mysql.com/
tech-resources/articles/hierarchical-data.html>

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

Feb 4 '07 #16

P: n/a
R. Rajesh Jeba Anbiah wrote:
On Feb 2, 5:58 am, "Phil Latio" <phil.la...@f-in-stupid.co.ukwrote:
>Let's say I have a simple web application running with just two MySQL
tables. The tables structure is as follows:
<snip>
>As you can see Batley, Leeds and Bradford are sub-categories of West
Yorkshire which itself is a sub-category of England.

What I want to display when I am browsing through sub-categories of links is
not only the name of that sub-category but it's category's parents, grand
parents and so on like the below example.

UK >West Yorkshire >Batley
<snip>

Answer is Nested Set Model architecture <http://dev.mysql.com/
tech-resources/articles/hierarchical-data.html>

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

Not really. I've used nested sets before. They're find if the data is
static and/or the tables small. But dynamic data can be a real PITA to
manage, especially when the tables grow.

For instance, if you need to add a new entry to the bottom left node,
you potentially would have to renumber over 90% of the nodes in your table.

This is glossed over very lightly in the discussion itself. The fact
is, with a large table, it can be very costly to update all of those nodes.

Recursive SQL is much better. And it can be emulated in stored procedures.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Feb 4 '07 #17

P: n/a
Geoff

Based on your model I came up with the following. It uses Jason Gilmore's
MySQL connection class from his book "Beginning PHP5 and MySQL" published by
Apress.

I am quite pleased with this as I managed to get rid of the trailing >>
which looked very untidy.

What I shall eventually do is turn it into a class.

Thanks for your help

Cheers

Phil

<?php

require 'class.MySQL.php';
$link_id = 4;
breadcrumb($link_id);

function breadcrumb ($object)
{

while($object >0)
{
$db=new MySQL("*****", "*****", "*****", "***");
$db->connect();
$db->select();
$db->query("SELECT category_name,parent_category_id from category where
category_id=$object");
$result=$db->fetchArray();
$breadcrumb_array[] = $result[category_name];
$object = $result[parent_category_id];
$counter ++;
}

print $breadcrumb_array[$counter];
$counter --;
while ($counter >0)
{
print $breadcrumb_array[$counter];
print ">";
$counter --;
}
print $breadcrumb_array[0];
}
?>
Feb 4 '07 #18

P: n/a
..oO(Phil Latio)
>Based on your model I came up with the following. It uses Jason Gilmore's
MySQL connection class from his book "Beginning PHP5 and MySQL" published by
Apress.
You don't have to reconnect to the MySQL server in each and every loop.
Do it once before entering the while loop, then just fire your queries.
>I am quite pleased with this as I managed to get rid of the trailing >>
which looked very untidy.
There's another (IMHO easier) way to avoid that: While running the
queries collect all breadcrumb items in an array. After that reverse the
array and just use implode() to print it out with '>>' separators. Also
set error_reporting to E_ALL while developing - your code will throw
some notices.

Slightly modified, but untested:

function breadcrumb($object) {
$db = new MySQL("*****", "*****", "*****", "***");
$db->connect();
$db->select();
$breadcrumb_array = array();
while ($object 0) {
$db->query("
SELECT category_name, parent_category_id
FROM category
WHERE category_id = $object"
);
$result = $db->fetchArray();
$breadcrumb_array[] = $result['category_name'];
$object = $result['parent_category_id'];
}
print implode(' >', array_reverse($breadcrumb_array));
}

Micha
Feb 5 '07 #19

P: n/a
..oO(R. Rajesh Jeba Anbiah)
Answer is Nested Set Model architecture <http://dev.mysql.com/
tech-resources/articles/hierarchical-data.html>
Definitely not. In most cases a nested set structure is total overkill,
especially if all you need is a simple parent-child relation. Running
some queries recursively is quite cheap (even cheaper with prepared
statements) and also much simpler compared to the very complex issue of
handling a nested set.

Micha
Feb 5 '07 #20

P: n/a
There's another (IMHO easier) way to avoid that: While running the
queries collect all breadcrumb items in an array. After that reverse the
array and just use implode() to print it out with '>>' separators. Also
set error_reporting to E_ALL while developing - your code will throw
some notices.

Slightly modified, but untested:

function breadcrumb($object) {
$db = new MySQL("*****", "*****", "*****", "***");
$db->connect();
$db->select();
$breadcrumb_array = array();
while ($object 0) {
$db->query("
SELECT category_name, parent_category_id
FROM category
WHERE category_id = $object"
);
$result = $db->fetchArray();
$breadcrumb_array[] = $result['category_name'];
$object = $result['parent_category_id'];
}
print implode(' >', array_reverse($breadcrumb_array));
}
Thanks for this extra feedback. Always good to see other peoples ideas.

Not come across "array_reverse" before although "implode" sounds familiar
but not used that either. I will check them out.

Cheers

Phil
Feb 5 '07 #21

P: n/a
On Feb 5, 7:45 pm, Michael Fesser <neti...@gmx.dewrote:
.oO(R.RajeshJebaAnbiah)
Answer is Nested Set Model architecture <http://dev.mysql.com/
tech-resources/articles/hierarchical-data.html>

Definitely not. In most cases a nested set structure is total overkill,
especially if all you need is a simple parent-child relation. Running
some queries recursively is quite cheap (even cheaper with prepared
statements) and also much simpler compared to the very complex issue of
handling a nested set.
My memory is vain on the theory of the recursive SQL, but the
overhead in nested set architecture is only when you insert the data,
but not while fetching. Usually the categories structure won't change
often--IOW, it's more or less static than dynamic. There is some good
stuff on nested sets here <http://dev.e-taller.net/dbtree/>

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

Feb 6 '07 #22

P: n/a
R. Rajesh Jeba Anbiah wrote:
On Feb 5, 7:45 pm, Michael Fesser <neti...@gmx.dewrote:
>.oO(R.RajeshJebaAnbiah)
>> Answer is Nested Set Model architecture <http://dev.mysql.com/
tech-resources/articles/hierarchical-data.html>
Definitely not. In most cases a nested set structure is total overkill,
especially if all you need is a simple parent-child relation. Running
some queries recursively is quite cheap (even cheaper with prepared
statements) and also much simpler compared to the very complex issue of
handling a nested set.

My memory is vain on the theory of the recursive SQL, but the
overhead in nested set architecture is only when you insert the data,
but not while fetching. Usually the categories structure won't change
often--IOW, it's more or less static than dynamic. There is some good
stuff on nested sets here <http://dev.e-taller.net/dbtree/>

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/
Rajesh,

A side note. The nested set isn't appropriate - and IME it seldom works
very in real life. The nested set has its own problems like inserting
new items into a large table. It's also more complex to manage than a
simple parent/child relationship. I agree with Micha that is is way too
complicated for a simple job like this.

Hopefully MySQL will get recursive SQL soon. It's quite handy for
managing parent-child relationships and other tree types. And it
handles other types of relationships as well - including ones where
nested sets don't work.

But meanwhile he can do it with stored procedures.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Feb 6 '07 #23

P: n/a
On Feb 6, 5:06 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:
R.RajeshJebaAnbiahwrote:
<snip>
My memory is vain on the theory of the recursive SQL, but the
overhead in nested set architecture is only when you insert the data,
but not while fetching. Usually the categories structure won't change
often--IOW, it's more or less static than dynamic. There is some good
stuff on nested sets here <http://dev.e-taller.net/dbtree/>

A side note. The nested set isn't appropriate - and IME it seldom works
very in real life. The nested set has its own problems like inserting
new items into a large table. It's also more complex to manage than a
simple parent/child relationship. I agree with Micha that is is way too
complicated for a simple job like this.
As I said earlier, the categories kinda thing for a normal web
application will be very limited--the table data will be more or less
static. I'm expecting the table size will be 200-500 records. But,
when you take the breadcrumb, you have to show it is on every pages--
the headache is higher here. So, nested set architecture will be ideal
here or it's variants (my memory recalls two or more variants)
Hopefully MySQL will get recursive SQL soon. It's quite handy for
managing parent-child relationships and other tree types. And it
handles other types of relationships as well - including ones where
nested sets don't work.
As I said, my memory is vain on the theory of recursive SQL; but I
still know as I implemented nested sets will be ideal for showing
breadcrumbs. I'll lurk <news:comp.database.theorysometime later.

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

Feb 7 '07 #24

P: n/a
R. Rajesh Jeba Anbiah wrote:
On Feb 6, 5:06 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:
>R.RajeshJebaAnbiahwrote:
<snip>
>> My memory is vain on the theory of the recursive SQL, but the
overhead in nested set architecture is only when you insert the data,
but not while fetching. Usually the categories structure won't change
often--IOW, it's more or less static than dynamic. There is some good
stuff on nested sets here <http://dev.e-taller.net/dbtree/>
A side note. The nested set isn't appropriate - and IME it seldom works
very in real life. The nested set has its own problems like inserting
new items into a large table. It's also more complex to manage than a
simple parent/child relationship. I agree with Micha that is is way too
complicated for a simple job like this.

As I said earlier, the categories kinda thing for a normal web
application will be very limited--the table data will be more or less
static. I'm expecting the table size will be 200-500 records. But,
when you take the breadcrumb, you have to show it is on every pages--
the headache is higher here. So, nested set architecture will be ideal
here or it's variants (my memory recalls two or more variants)
Maybe, maybe not. It all depends, doesn't it? And how far down one
goes. I can see this to be a few dozen entries - but I could also
easily see it grow to tens of thousands.

And it may or may not be static. I could also see something which is
fairly dynamic.
>Hopefully MySQL will get recursive SQL soon. It's quite handy for
managing parent-child relationships and other tree types. And it
handles other types of relationships as well - including ones where
nested sets don't work.

As I said, my memory is vain on the theory of recursive SQL; but I
still know as I implemented nested sets will be ideal for showing
breadcrumbs. I'll lurk <news:comp.database.theorysometime later.

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/
Try news:comp.database.most.databases.implement.recurs ive.sql.
Recursive SQL is not theory - it is present in many large databases,
including oracle, sql server and db2. Just not MySQL yet.

I've used the nested set architecture before. It's really a PITA to
manage, and way overkill for something as simple as a parent-child
relationship. And the latter can be easily handled in a stored procedure.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Feb 7 '07 #25

This discussion thread is closed

Replies have been disabled for this discussion.