Arnar Birgisson wrote: > If I understand correctly the OP has a need to store a set of > hierarchical "Nodes". Only that some node types (video, image) can't > have children while others can (group, media_list).
Correct, "group" and "media_list" will have childen. I'm also going to integrate a "playlist" type of node to nest a playlist into another one. Actually, a "playlist" node won't have children, you'll need to click the playlist to open it in a new page. > Alexandre, why do you find that the nested set model suits your needs > better? The nested set model is particularly suited to the following > type of queries: > 1. find all children of a particular node, without needing to know > their generation > 2. test if node A is a descendant of node B > > These are queries that are expensive with adjacency lists but > inexpensive with nested sets. Nested sets however may require you to > update a lot of nodes if your tree changes. Traditionally you would do > that with triggers or stored procedures - but those don't play well > with ORMs which assumes that an update of a row won't invalidate > cached instances of other rows. > > Do you have specific needs for those kind of queries? I don't know > your application but I'd probably go with adjacency lists and a common > "Node" superclass that provides access to child and parent nodes. > Derive other classes from that and use joined table polymorphism as > Simon suggested. The nested looks more efficient. But, things are still a little confused in my head. I need to well put down the pros and cons of each technic for my needs. I was using XML and I'm now switching to a flat database with technics I've never used yet. Basicly, my application is a web-based interface that displays a playlist. So at this point, I'll need to walk down the whole tree to retrieve each node's data (type, name, duration, etc.) and build up the HTML representation (probably with HTML lists <ul> and <li> tags). Then, the user (playlist manager) will have multiple tools available to manipulate the playlist: - create node somewhere specific in the tree (image, video, group, playlist, media_list) - move node N sibling up - move node N sibling down - move node top - move node bottom - edit node's data (change media, schedule info) - duplicate node - delete node All action would be recursive, so if a group node is moved up or deleted, children will follow (either up, or to /dev/null). I do understand that nested sets take more ressource when creating/moving/deleting items as every node on the right needs to be updated. When this is done, the whole tree will be exported to XML, concatening all nested playlists if any, and sent to the "player". On the player side, the parser will walk down each node, read the media (if on schedule) and move on to the next node until the last one before walking the tree all over again. But we're talking XML now, which is off-topic. > There is another way to store hierarchical data than adjacency lists > and nested sets though. If you have an invariant id on your objects > (numerical or textual) you can store an objects "path", governed by > > object.path = object.parent.path + delimiter + object.id > > This might not be efficient for deeply nested trees maybe, but it can > help in answering the types of queries mentioned above efficiently > without requiring you to touch multiple rows upon inserting or > deleting. Also, it would be easy to implement in SA and you could have > all sorts of convenience methods on a Node class, like > get_direct_children > get_all_descendants > is_parent > is_child > is_descendant This reminds me of the XML XPath which I used pretty much for playlist manipulation. Although, you example feels less consistent because if a media is removed from the catalog, I would like the database to remove the playlist node that refers to that video. Also, how big should my "path" column be ? I guess I should make it big enough to store a long very long path, but it is not accurate. Thanks for your suggestions. I'll look at "joined table polymorphism" closely and try to figure out what are the pros and cons for each hierarchical database technics. ps: I've ordered today the "Joe Celko's Trees and Hierarchies in SQL for Smarties". Regards, -- Alexandre CONRAD --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
