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 = IF( lft > ?, lft + 2, lft ), rgt = IF( rgt >= ?, rgt + 2, rgt ), assignments.id = IF( id > ?, id + 2, id ) WHERE rgt >= ?; (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/[EMAIL PROTECTED]