RE: [PHP-DB] trees in MySQL

2002-12-19 Thread Cal Evans
http://froogle.google.com/froogle?q=%22SQL+for+Smarties%22&btnG=Froogle+Sear
ch

Chaps 28 & 29. (ok, I had to read both but it sounds like you already
understand the concepts.) The end of chap 29 he gives a simple example for
maintaining what you are talking about.  I used it to write a dynamic menu
system for a web portal for a client. It is possible to do and not terribly
difficult. Given the lack of stored procedures you do end up executing 8-10
sql commands one after the other but if you are using a transaction safe
table type (*COUGH*InnoDB*COUGH*) then it works pretty good.

Unlike one of the suggestions I read here, this system doesn't like holes in
the numbers. But he gives code for adding and subtracting nodes.  By
combining the two, I actually made it so the client could move entire
branches.

HTH,
=C=

*
* Cal Evans
* The Virtual CIO
* http://www.calevans.com
*


-Original Message-
From: José Moreira [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 10:45 AM
To: Php-Db (Correio electrónico)
Subject: [PHP-DB] trees in MySQL


hello im implementing a portal and im choosing between the 'Nested Set' and
'Adjacency Lists tree' models.
<http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html>


im perfectly aware of the 'Adjacency Lists tree' model how-to and I am
studying the 'Nested Set', example follows :

CREATE TABLE LanguageTree (

  Language VARCHAR(255) PRIMARY KEY
, Parent VARCHAR(255)
, Lft LONG
, Rgt LONG
, INDEX idxLft (Lft)
, INDEX idxRgt (Rgt)
) Comment = "Related languages";


A[1 12]
  /  \
 /\
B[2 3]   C[4 11]
   /  \
  /\
  D[5 8] F[9 10]
 |
  E[6 7]


my doubt is that if i want to insert a new node, for example 'G' under 'A'

A[1 12]
  / | \
 /   \
B[2 3]G[]   C[4 11]
   / \
  /   \
  D[5 8] F[9 10]
 |
  E[6 7]


i have? to update almost every left,righ fields after that node, resulting
in a website performance downgrade 


help? even if thjats the only way, how can i do it? perhaps im getting it
wrong ...


best regards


José Moreira
TEGOPI S.A.
http://www.tegopi.pt









--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




RE: [PHP-DB] trees in MySQL

2002-12-19 Thread José Moreira
thank you for your help,

the portal will (problably) use sort of a directory index where a node can
be a folder to go 'inside' or a

portlet like moduled application like a forum, or a partial download list
( depending on current node ).

knowing that the 'Adjacency Lists tree' and a bunch of tables would be more
easy to implement i believe the

'Nested Set', few tables ( the tree, users, permissions and a table for each
type of portlet,) and a buch of SQL statements

would be more eficient considering scalability ... correct me if im wrong :)

the first node levels will have just a few very slowly increasing branches
but the forum branch will be more dynamic

depending on site success.

perhaps the 5 or 10 seccions will be sufficientin these first levels and
could use a 1000 unit difference for the more

dinamic. perhaps using also a automated trigger PHP procedure to increase
the number of series when the difference becomes


low.


tree theory is very interesting and has much to offer in terms of knowledge
...





-Mensagem original-
De: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]]
Enviada: quinta-feira, 19 de Dezembro de 2002 17:08
Para: [EMAIL PROTECTED]; Php-Db (Correio electrónico)
Assunto: Re: [PHP-DB] trees in MySQL


If you don't know how many nodes you're going to have and the amount of
branches under each node, then that's the only way to do it.

However, if you can define those numbers, you can use something like the
following. A manager would have 50 numbers, for example. So, rather than
counting through the tree to determine left and right pairs, the first
manager simply has left:1 and right:51. Now, a section leader (SL) under him
has 10 numbers (so a manager can have 5 section leaders). The first section
manager would have 2 - 11, the second 12 - 21, etc. Now each section leader
can have an employee (E) that gets an adjacent left and right pair (so they
are a "leaf" with nothing under them).

Something like this, hopefully the formatting remains.

Manager
150
  |
 /\
SL SL
2  1112-21
   | |
  /   \ E
E  E  13-14
3-4 5-6

So if you count around the tree, you see it still goes in order, but it
leaves holes where you can "fill" people in.

So, if you can assign specific limits like that, to add a new section leader
under this manager, you simply select the largest "right" number for the
current section leaders, which will be 21, and add 10 to that, so the "new"
section leader would have numbers 22-31.

  Manager
  150
|
  / \
  / |\
SL  SL SL
2  1112  21   22  31
   | |
/ \ E
EE13-14
3-4 5-6

That's probably confusing, but it works on paper for well defined trees. I
can explain in more detail if you're interested, but I don't know if it
applies to your situation or not.

---John Holmes...

- Original Message -
From: "José Moreira" <[EMAIL PROTECTED]>
To: "Php-Db (Correio electrónico)" <[EMAIL PROTECTED]>
Sent: Thursday, December 19, 2002 11:45 AM
Subject: [PHP-DB] trees in MySQL


> hello im implementing a portal and im choosing between the 'Nested Set'
and
> 'Adjacency Lists tree' models.
> <http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html>
>
>
> im perfectly aware of the 'Adjacency Lists tree' model how-to and I am
> studying the 'Nested Set', example follows :
>
> CREATE TABLE LanguageTree (
>
>   Language VARCHAR(255) PRIMARY KEY
> , Parent VARCHAR(255)
> , Lft LONG
> , Rgt LONG
> , INDEX idxLft (Lft)
> , INDEX idxRgt (Rgt)
> ) Comment = "Related languages";
>
>
> A[1 12]
>   /  \
> /   \
> B[2 3]   C[4 11]
>/  \
>   /\
>   D[5 8] F[9 10]
>  |
>   E[6 7]
>
>
> my doubt is that if i want to insert a new node, for example 'G' under 'A'
>
> A[1 12]
>   / | \
> /  \
> B[2 3]G[]   C[4 11]
>/ \
>   /   \
>   D[5 8] F[9 10]
>  |
>   E[6 7]
>
>
> i have? to update almost every left,righ fields after that node, resulting
> in a website performance downgrade 
>
>
> help? even if thjats the only way, how can i do it? perhaps im getting it
> wrong ...
>
>
> best regards
>
>
> José Moreira
> TEGOPI S.A.
> http://www.tegopi.pt
>
>
>
>
>
>
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




Re: [PHP-DB] trees in MySQL

2002-12-19 Thread Ignatius Reilly
The difficulty you are being confronting could be alleviated by using an XML data 
model (meaning non relational). Of course, I don't know about your requirements, but 
it could be a direction worth investigating.

I use the php_xslt.dll (sablotron) extension, which I find works quite pleasantly.

Ignatius

  - Original Message -
  From: "José Moreira" <[EMAIL PROTECTED]>
  To: "Php-Db (Correio electrónico)" <[EMAIL PROTECTED]>
  Sent: Thursday, December 19, 2002 11:45 AM
  Subject: [PHP-DB] trees in MySQL


  > hello im implementing a portal and im choosing between the 'Nested Set'
  and
  > 'Adjacency Lists tree' models.
  > <http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html>
  >
  >
  > im perfectly aware of the 'Adjacency Lists tree' model how-to and I am
  > studying the 'Nested Set', example follows :
  >
  > CREATE TABLE LanguageTree (
  >
  >   Language VARCHAR(255) PRIMARY KEY
  > , Parent VARCHAR(255)
  > , Lft LONG
  > , Rgt LONG
  > , INDEX idxLft (Lft)
  > , INDEX idxRgt (Rgt)
  > ) Comment = "Related languages";
  >
  >
  > A[1 12]
  >   /  \
  > /   \
  > B[2 3]   C[4 11]
  >/  \
  >   /\
  >   D[5 8] F[9 10]
  >  |
  >   E[6 7]
  >
  >
  > my doubt is that if i want to insert a new node, for example 'G' under 'A'
  >
  > A[1 12]
  >   / | \
  > /  \
  > B[2 3]G[]   C[4 11]
  >/ \
  >   /   \
  >   D[5 8] F[9 10]
  >  |
  >   E[6 7]
  >
  >
  > i have? to update almost every left,righ fields after that node, resulting
  > in a website performance downgrade 
  >
  >
  > help? even if thjats the only way, how can i do it? perhaps im getting it
  > wrong ...
  >
  >
  > best regards
  >
  >
  > José Moreira
  > TEGOPI S.A.
  > http://www.tegopi.pt
  >
  >
  >
  >
  >
  >
  >
  >
  >
  > --
  > PHP Database Mailing List (http://www.php.net/)
  > To unsubscribe, visit: http://www.php.net/unsub.php
  >


  -- 
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php





Re: [PHP-DB] trees in MySQL

2002-12-19 Thread 1LT John W. Holmes
If you don't know how many nodes you're going to have and the amount of
branches under each node, then that's the only way to do it.

However, if you can define those numbers, you can use something like the
following. A manager would have 50 numbers, for example. So, rather than
counting through the tree to determine left and right pairs, the first
manager simply has left:1 and right:51. Now, a section leader (SL) under him
has 10 numbers (so a manager can have 5 section leaders). The first section
manager would have 2 - 11, the second 12 - 21, etc. Now each section leader
can have an employee (E) that gets an adjacent left and right pair (so they
are a "leaf" with nothing under them).

Something like this, hopefully the formatting remains.

Manager
150
  |
 /\
SL SL
2  1112-21
   | |
  /   \ E
E  E  13-14
3-4 5-6

So if you count around the tree, you see it still goes in order, but it
leaves holes where you can "fill" people in.

So, if you can assign specific limits like that, to add a new section leader
under this manager, you simply select the largest "right" number for the
current section leaders, which will be 21, and add 10 to that, so the "new"
section leader would have numbers 22-31.

  Manager
  150
|
  / \
  / |\
SL  SL SL
2  1112  21   22  31
   | |
/ \ E
EE13-14
3-4 5-6

That's probably confusing, but it works on paper for well defined trees. I
can explain in more detail if you're interested, but I don't know if it
applies to your situation or not.

---John Holmes...

- Original Message -
From: "José Moreira" <[EMAIL PROTECTED]>
To: "Php-Db (Correio electrónico)" <[EMAIL PROTECTED]>
Sent: Thursday, December 19, 2002 11:45 AM
Subject: [PHP-DB] trees in MySQL


> hello im implementing a portal and im choosing between the 'Nested Set'
and
> 'Adjacency Lists tree' models.
> <http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html>
>
>
> im perfectly aware of the 'Adjacency Lists tree' model how-to and I am
> studying the 'Nested Set', example follows :
>
> CREATE TABLE LanguageTree (
>
>   Language VARCHAR(255) PRIMARY KEY
> , Parent VARCHAR(255)
> , Lft LONG
> , Rgt LONG
> , INDEX idxLft (Lft)
> , INDEX idxRgt (Rgt)
> ) Comment = "Related languages";
>
>
> A[1 12]
>   /  \
> /   \
> B[2 3]   C[4 11]
>/  \
>   /\
>   D[5 8] F[9 10]
>  |
>   E[6 7]
>
>
> my doubt is that if i want to insert a new node, for example 'G' under 'A'
>
> A[1 12]
>   / | \
> /  \
> B[2 3]G[]   C[4 11]
>/ \
>   /   \
>   D[5 8] F[9 10]
>  |
>   E[6 7]
>
>
> i have? to update almost every left,righ fields after that node, resulting
> in a website performance downgrade 
>
>
> help? even if thjats the only way, how can i do it? perhaps im getting it
> wrong ...
>
>
> best regards
>
>
> José Moreira
> TEGOPI S.A.
> http://www.tegopi.pt
>
>
>
>
>
>
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




RE: [PHP-DB] trees in MySQL

2002-12-19 Thread Hutchins, Richard
Jose,

In addition to any advice you may receive from your post today, check out
the PHP archives. I distinctly remember a discussion related to these models
maybe a month back that may be of interest to you. Don't remember if it
dealt with your specific concerns, but I'm pretty sure it dealt with the
flexibility of application of the two models in different environments.

Happy hunting.

Rich

> -Original Message-
> From: José Moreira [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, December 19, 2002 11:45 AM
> To: Php-Db (Correio electrónico)
> Subject: [PHP-DB] trees in MySQL
> 
> 
> hello im implementing a portal and im choosing between the 
> 'Nested Set' and
> 'Adjacency Lists tree' models.
> <http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html>
> 
> 
> im perfectly aware of the 'Adjacency Lists tree' model how-to and I am
> studying the 'Nested Set', example follows :
> 
> CREATE TABLE LanguageTree (
> 
>   Language VARCHAR(255) PRIMARY KEY
> , Parent VARCHAR(255)
> , Lft LONG
> , Rgt LONG
> , INDEX idxLft (Lft)
> , INDEX idxRgt (Rgt)
> ) Comment = "Related languages";
> 
> 
>   A[1 12]
> /  \
>/\
> B[2 3]   C[4 11]
>  /  \
> /\
> D[5 8] F[9 10]
>|
> E[6 7]
> 
> 
> my doubt is that if i want to insert a new node, for example 
> 'G' under 'A'
> 
>   A[1 12]
> / | \
>/   \
> B[2 3]G[]   C[4 11]
>  / \
> /   \
> D[5 8] F[9 10]
>|
> E[6 7]
> 
> 
> i have? to update almost every left,righ fields after that 
> node, resulting
> in a website performance downgrade 
> 
> 
> help? even if thjats the only way, how can i do it? perhaps 
> im getting it
> wrong ...
> 
> 
> best regards
> 
> 
> José Moreira
> TEGOPI S.A.
> http://www.tegopi.pt
> 
> 
> 
> 
> 
> 
> 
> 
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




[PHP-DB] trees in MySQL

2002-12-19 Thread José Moreira
hello im implementing a portal and im choosing between the 'Nested Set' and
'Adjacency Lists tree' models.



im perfectly aware of the 'Adjacency Lists tree' model how-to and I am
studying the 'Nested Set', example follows :

CREATE TABLE LanguageTree (

  Language VARCHAR(255) PRIMARY KEY
, Parent VARCHAR(255)
, Lft LONG
, Rgt LONG
, INDEX idxLft (Lft)
, INDEX idxRgt (Rgt)
) Comment = "Related languages";


A[1 12]
  /  \
 /\
B[2 3]   C[4 11]
   /  \
  /\
  D[5 8] F[9 10]
 |
  E[6 7]


my doubt is that if i want to insert a new node, for example 'G' under 'A'

A[1 12]
  / | \
 /   \
B[2 3]G[]   C[4 11]
   / \
  /   \
  D[5 8] F[9 10]
 |
  E[6 7]


i have? to update almost every left,righ fields after that node, resulting
in a website performance downgrade 


help? even if thjats the only way, how can i do it? perhaps im getting it
wrong ...


best regards


José Moreira
TEGOPI S.A.
http://www.tegopi.pt









-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php