I have the following table schema:
categories(id, label, left_id, right_id)
and have been using the Modified Preorder Tree Traversal algorithm to manage the tree. This has been working well, but i'm now at a junction where i need to move a node that may or may not contain child nodes.
...so two use cases might be the user would want to make "garden" a child of "products". The more complex situation would be that the user wants to move "household" as a child of "root", yet still retain "garden" as a child of "household".
Can anyone give me some pointers in the right direction?
edit: i'm using innoDB so would prefer to use transactions if possible.