468,532 Members | 1,612 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,532 developers. It's quick & easy.

Updating a nested set

I'm basing some work on Joe Celko's excellent idea of using nested sets
to represent an organizational structure as opposed to an adjacency
list. By and large it's a great idea, but not without its pitfalls. I'm
writing now to ask your collective opinion of the best practice.

I've got my nested set table, and since the left bound is unique, I'm
using it as a foreign key to the table that contains assignment
information. The problem is that if I add an element to the middle of
the nested set, the left bound change on the elements to the right of
the insert. This requires an update of the foreign key on the assignment
table.

I see two options:
1. Update the foreign key on the assignment table with a multi-table
UPDATE as I nudge the necessary left bounds:

UPDATE elements, assignments
SET lft =3D IF( lft > ?, lft + 2, lft ),
rgt =3D IF( rgt >=3D ?, rgt + 2, rgt ),
assignments.id =3D IF( id > ?, id + 2, id )
WHERE rgt >=3D ?;
(given that '?' is the right node of the parent entitiy)

2. Don't use the left bound as the foreign key. While this seems
straight forward, it complicates queries like "all assignments for this
element and it's sub-elements."

I'm leaning toward #1, but I have the nagging feeling that it's more
complex than the example query is able to manage.
There could be a solution I haven't considered, as well... I'd love to
learn about it.

__________________________________

Jim Knepley
Network Security Engineering
x88321

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
0 2700

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by omission9 | last post: by
6 posts views Thread by Andy Baker | last post: by
2 posts views Thread by Falc2199 | last post: by
6 posts views Thread by B0nj | last post: by
8 posts views Thread by Robert W. | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.