Hi, I think this is a pretty good match for Joined Table polymorphic inheritance, as described in the docs <http://www.sqlalchemy.org/docs/adv_datamapping.html#advdatamapping_inhe ritance_joined>. Your nodes_table would correspond to the employees table, and the 'subclass' tables such as video correspond to the engineers table. Your foreign key problem is solved by giving the video table a foreign key pointing back to the node table, instead of the other way round.
I think of adjacency lists and nested sets as more about hierarchies of a single type of object (imagine trying to represent a family tree with a row for each Person). I don't really think they're relevant in this case. Hope that helps, Simon > -----Original Message----- > From: sqlalchemy@googlegroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of Alexandre CONRAD > Sent: 03 August 2007 10:44 > To: sqlalchemy > Subject: [sqlalchemy] Hierachical data > > > Hello, > > this is more a general database question rather than a SA > one, but maybe > someone has already achieve this with SA. I know there's a > small chapter > about this already in the SA docs (1), but as far as I > understood, this > is about the "Adjacency List" model and not about the "Nested Set" > model. This article (2) explains about both approches and the "Nested > Set" model would suite better my needs I think. > > Maybe this should need some attention to implement in SA some API to > handle nodes (insert, move, remove) of herachical trees in SA the > "Nested Set" way. > > Right now I have a playlist management application that is > XML-based. So > a playlist has many nodes that either represent media > elements ("video", > "image") or classification elements ("group"): > > <main> > <video>intro.avi</video> > <group datein="20070701" dateout="20070731"> > <video>foo.avi</video> > <video>bar.avi</video> > </group> > <group> > <video>weather_intro.avi</video> > <image duration="5" start="0000" stop="1159">morning.jpg</image> > <image duration="5" start="1200" > stop="2359">afternoon.jpg</image> > <video>brought_to_you_by.avi</video> > <media_list pick_style="random" pick_many="1"> > <video>brand_A.avi</video> > <video>brand_B.avi</video> > <video dateout="20071231">brand_C.avi</video> > </media_list> > </group> > <video>outro.avi</video> > </main> > > You can understand that the playlist has the ability to store > different > types of medias (images, videos), some group elements used for > classification, can apply some scheduling data to any elements as > attributes. > > So I'm looking foward to integrate this hierarchical tree in a flat > database. > > Basicly, I'd have a "playlist" to hold the main playlist: > > playlist_table = Table('playlists', meta, > Column('id', Integer, primary_key=True), > Column('name', Unicode(20), nullable=False), > Column('description', Unicode(40)), > ) > > Then an "nodes" table to store every type of nodes (group, > video, image) > and some scheduling attributes. > > nodes_table = Table('nodes', meta, > Column('id', Integer, primary_key=True), > Column('typeof', Unicode(20), nullable=False), > Column('foreign_id', Integer), > Column('lft', Integer, nullable=False), # For "Nested Set" model > Column('rgt', Integer, nullable=False), # For "Nested Set" model > Column('scheduled_from', DateTime), > Column('scheduled_until', DateTime), > Column('id_playlist', None, ForeignKey('playlists.id')), > ) > > Every row would store "group", "video" or "image" in the > 'typeof' field > to identify the type of node. And the id of the media would be in the > 'foreign_id' field. 'typeof' and 'foreign_id' are the two > field I don't > feel clean about. > > Lets finish with the "videos" table for the example: > > videos_table = Table('videos', meta, > Column('id', Integer, primary_key=True), > Column('file_name', Unicode(255), nullable=False), > Column('category', Unicode(255), nullable=False), > Column('duration', Integer), > Column('id_codec', None, ForeignKey('codecs.id')), > ) > > As the "nodes" table would need to point to different tables > to find the > corresponding media, I couldn't have a foreign key for a natural > relation. Or I would need to have as many foreign keys in the "nodes" > table (id_video, id_image, ...) and only fill in the nedded > column. But > this is not efficient. > > The whole idea is about browing down my tree through python objects > attributes like SA already does so well when two tables are > related to > each other, but returning a different type of object depending on the > kind of node I hit. > > class Node(object): > ... > class Video(Node): > ... > class Image(Node): > ... > > Any ideas one how I could deal with that would be greatly > appreciated. I > hope this is clear enough to not scare people from reading > it. At least > if you got there, it's a good point. :) > > ps: I just felt on the docs about "Concrete Inheritance, Polymorphic" > (3), maybe this could be something to dig for ? > > (1) > http://www.sqlalchemy.org/docs/documentation.html#advdatamappi > ng_selfreferential > (2) > http://www.vbmysql.com/articles/database-design/managing-hiera > rchical-data-in-mysql/ > (3)http://www.sqlalchemy.org/docs/documentation.html#advdatama pping_inheritance_concrete > > 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 -~----------~----~----~----~------~----~------~--~---
