Another approach could be have only tables Persons, Document and Document2Person. The table Document2Person will have integer column with bitmask, which specifies roles.
Dne Ätvrtek, 3. dubna 2014 4:44:07 UTC+2 Tony B napsal(a): > > I'm quite new to SqlAlchemy, and I'm rolling right along with my project, > but I've hit a snag. I've been having trouble searching successfully for > this problem, but maybe I'm not using the right keywords. My apologies if > this is an oft repeated problem. > > I'm using SqlAlchemy 0.9 and SQLite, and trying to model credits for > documents (authorship, and such) > > Some details: > > - I have a model with 3 classes of objects/tables: Documents, Persons, > and Roles. > - I want to associate Documents with pairs of Person-Roles (aka > Credits). > - Persons and Roles each have only an ID and a name. (Roles might be > "editor", "writer", "artist", etc) > - A Person may fill zero or more Roles on a given document, > - A particular Role may occur zero to many times on a document (i.e. > multiple writers). > - A Person can have different Roles in different documents. (writer > for one, editor on another) > > Just playing in SQLite, I created a three-way join table (called > "credits"), with a primary key from each of the three aforementioned > tables. Somehow I cobbled together a functional select statement that > represents what I want: > > select documents.id, persons.name as person, roles.name as role > from documents > inner join credits > on credits.comic_id=documents.id > inner join persons > on credits.person_id=persons.id > inner join roles > on credits.role_id=roles.id > > I would like to somehow create a relationship for the Document class that > will give me the Credit pairs, as a python tuple or dict. > > Is this do-able? Or do I need to rethink my model? I'm a novice with > respect to some of this complex DB stuff, so any help would be appreciated! > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
