On 20/01/12 11:54, bruce wrote: > Hi. > > Got a major pain that I'm trying to solve using mysql. > > Trying to handle a hierarchical tree structure, where I have a > parent/child structure that grows as data is added to the system. > > The process needs to continuously determine if the overall tree, and > all the associated nodes/leafs have "completed" so not only is the > tree growing, but data for the given node/leaf is also changing, > > The system is comprised of a parent app which spawns descendant apps > that in turn can spawn descendant apps, and so on.. > > The system is represented in mysql as a parent/child tree, where each > spawned app has an ID, as well as a status for the completion status > of the app. > > I'm trying to find someone I can talk to regarding this, so I can get > clarity on how this can be implemented. > > The process needs to be able to: > -update the tree tbl with updated data from the running apps > -update the tbl with new nodes/leafs as the spawned apps are created > -quickly return 0/1 if the descendants of a node have been complete > > I've created a few different tbl defs, and played with a few different > approaches, but haven't got this right yet. I've looked at a number of > different articles covering hierarchical, adjacency models, closures, > etc... > > **The nested soln isn't applicable to the project, as the data/tree > tbl is continually growing, which would require a complete rebuilding > of the nested tbls, which would impose a computational/time hit on the > process. > > I can provide the sample tbl defs/data that I'm using, as well as more > data on what I'm trying to accomplish. > > So, if you're skilled in this area, let's talk. > > Thanks > > -bruce > I'd agree with the general observation that your problem might not be suited for a relational database. If you've a good reason for using one I'd go for the materialised path (http://en.wikipedia.org/wiki/Materialized_path) or stored proc with recursion approaches myself. I've used all the various approaches over my career.
Which processes are writing data and how many? Which processes are reading data and how many? Which processes are both reading and writing data and how many? How quickly do you expect new entries to be added? Will entries ever be deleted? Do you need transactions? What volume of working set data are we talking about? If "The process needs to continuously determine" means lots of writers and single analyzing process I'd definitely use stored procs and have the procs write to a job queue table for the analysis process. Nigel -- Nigel Wood Plusnet BSS Architect -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
