On 8/3/07, Alexandre CONRAD <[EMAIL PROTECTED]> wrote: > 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).
You should definitely go with adjacency lists, no doubt. Nested sets will be very inefficient for all of those operations and poorly supported by SA (or any ORM for that matter). Also, that last one, the recursive bit, you get for free with adjacency lists. Trees constructed and manipulated by humans tend to not get very deep (20 levels is not deep for the computer) so there's another reason not to take on the overhead of maintaining nested sets. I.e. you won't get the benefits anyways. > 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. None of the strategies for storing hierarchical data will give you the property that nodes are removed from the tree if their referenced item is removed. You will have to do that yourself regardless. The path column should be at least (max depth)*(max length of key)*(length of delimiter). If you have a table of 10 million nodes, a numeric key is at most 7 characters long. A 256 character field would give you 32 levels of recursion minimum, but more in praxis since not all keys will be 7 characters long. Besides, given the operations you listed above, an adjacency list is still a better option. Arnar --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
