svidgen
12-13-2007, 03:46 PM
Hello all,
I would like to hear any opinions anyone has from personal experience or referenced from professional documentation on the following data storage requirements:
implementable in MySQL 5
contains hierarchical rows/objects
the ability to select any given row/object and all children, preferably without recursion or looping
the ability to apply permissions/associate users with any given row/object, applying to all descendants (this may be automatic with fulfillment of the previous requirement)
ability to handle a relatively equal number of updates, inserts, and selects in *high frequency*!
the ability to quickly apply flags and possibly other updates to all descendants of any given row/object (preferably without recursion)
*bonus* the ability to quickly apply flags and other updates to all ancestors
If anyone would like the visual: Imagine an application in which the users can create objects and sub-objects and grant access to any given object (or sub-object) to any number of other users. Certain flags or numerical changes to an object must be applied to either all descendants or all ancestors. And of course, sharing any given object should implicitly share all descendant objects.
It's sort of like having a bunch of people work on an XML document, except there could be a LOT of users, all of which have access on a node basis, and where the number of nodes is enormous, and the depth and width of the hierarchy is unknown.
Maybe there's even an open source solution more targeted to something like this than MySQL is ... I'm just not aware of it?
Any thoughts, opinions, suggestions, and tips are greatly appreciated!
Thanks,
I would like to hear any opinions anyone has from personal experience or referenced from professional documentation on the following data storage requirements:
implementable in MySQL 5
contains hierarchical rows/objects
the ability to select any given row/object and all children, preferably without recursion or looping
the ability to apply permissions/associate users with any given row/object, applying to all descendants (this may be automatic with fulfillment of the previous requirement)
ability to handle a relatively equal number of updates, inserts, and selects in *high frequency*!
the ability to quickly apply flags and possibly other updates to all descendants of any given row/object (preferably without recursion)
*bonus* the ability to quickly apply flags and other updates to all ancestors
If anyone would like the visual: Imagine an application in which the users can create objects and sub-objects and grant access to any given object (or sub-object) to any number of other users. Certain flags or numerical changes to an object must be applied to either all descendants or all ancestors. And of course, sharing any given object should implicitly share all descendant objects.
It's sort of like having a bunch of people work on an XML document, except there could be a LOT of users, all of which have access on a node basis, and where the number of nodes is enormous, and the depth and width of the hierarchy is unknown.
Maybe there's even an open source solution more targeted to something like this than MySQL is ... I'm just not aware of it?
Any thoughts, opinions, suggestions, and tips are greatly appreciated!
Thanks,