471,047 Members | 1,197 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,047 software developers and data experts.

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

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

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 2762

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

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.