Hi Chris, this is more of a relational design question than SQLAlchemy-related, but take a look at this for an at-a-glance summary of different approaches and their pros and cons:
http://vadimtropashko.wordpress.com/2008/08/09/one-more-nested-intervals-vs-adjacency-list-comparison/ ... here for some illustration and examples: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://communities.bmc.com/communities/docs/DOC-9902 and here for links to more than you really want to know on the subject: http://troels.arvin.dk/db/rdbms/links/#hierarchical SQLAlchemy support any of these approaches well enough that I don't think you need to factor SQLAlchemy into your choice of relational design at all. Check out /examples/nested_sets/ and /examples/ adjacency_list/ (in the SQLAlchemy distribution) for what are probably the two most common approaches. Regards, - Gulli On Sep 8, 8:22 am, Chris Withers <ch...@simplistix.co.uk> wrote: > Hi All, > > I'm trying to solve a hierarchical access control problem, both on the > storage and querying side. > > So, say I have a tree of content: > > / > /a/ > /a/1 > /a/2 > /b/ > /b/1 > /b/2 > > I want to be able to express and search on the following types of > requirements: > > "User X should be able to access all content in /a and content in /b/1" > > The storage side just needs to be usable, speed wise, but obviously the > query side needs to be lighting fast as it'll be hit *hard* and often. > > What schema/indexes would people recommend for this? > How would I query that schema fast in SQLAlchemy to be able to answer > the above type of questions. I guess the API that needs to be fast would > be along the lines of: > > def can_access(user_id,path): > return True or False > > The grant python API would look like: > > def grant_access(user_id,*paths): > ... > > Any help much appreciated! > > cheers, > > Chris > > -- > Simplistix - Content Management, Batch Processing & Python Consulting > -http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.