I've got a catalog at work and I want to juke it up a bit because I'm tired of it being extremely slow. I'd like to support the modified preorder tree traversal storage method for the category tree of the catalog but i'd have to support dated versionning.
I've thought about creating versions of trees based on dates, so as soon as something changes I create a copy of the tree at hand at current date and then create my modifications. But the problem with that is that an import of a catalog that occurs every night would create, at long range, an extremely heavy table of tree traversal elements. Combined with the fact that a user could go and change a date anytime in the admin, it could then again generate another tree version.
Most of our catalogs are comprised of 100-250 categories, so a few versions would not create more than a few thousand elements, but mix that to a year of operation, that would easily go up to 90000 entries just to store the tree that can change.
So my question is this:
Is there a way you'd implement a fast tree traversal method (preferably modified preorder with left and right columns) that would support dated versioning?