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
