On Thu, Sep 4, 2014 at 4:35 PM, Michael Bayer <mike...@zzzcomputing.com>
wrote:
>
> On Sep 4, 2014, at 6:58 PM, Ken Lareau <klar...@tagged.com> wrote:
>
> On Thu, Sep 4, 2014 at 3:30 PM, Michael Bayer <mike...@zzzcomputing.com>
> wrote:
>
>>
>> On Sep 4, 2014, at 5:37 PM, Ken Lareau <klar...@tagged.com> wrote:
>>
>> So I have a few tables as follows (abbreviated for unnecessary columns):
>>
>> class Project(Base):
>> __tablename__ = 'projects'
>>
>> id = Column(u'project_id', INTEGER(), primary_key=True)
>>
>> applications = relationship(
>> 'AppDefinition',
>> secondary=lambda: Base.metadata.tables['project_package'],
>> passive_deletes=True,
>> lazy=False,
>> )
>>
>> package_definitions = relationship(
>> 'PackageDefinition',
>> secondary=lambda: Base.metadata.tables['project_package'],
>> passive_deletes=True,
>> )
>>
>>
>> class PackageDefinition(Base):
>> __tablename__ = 'package_definitions'
>>
>> id = Column(u'pkg_def_id', INTEGER(), primary_key=True)
>>
>> applications = relationship(
>> 'AppDefinition',
>> secondary=lambda: Base.metadata.tables['project_package'],
>> passive_deletes=True,
>> )
>>
>> projects = relationship(
>> 'Project',
>> secondary=lambda: Base.metadata.tables['project_package'],
>> passive_deletes=True,
>> )
>>
>>
>> class AppDefinition(Base):
>> __tablename__ = 'app_definitions'
>>
>> id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True)
>>
>> package_definitions = relationship(
>> 'PackageDefinition',
>> secondary=lambda: Base.metadata.tables['project_package'],
>> passive_deletes=True,
>> )
>>
>> projects = relationship(
>> 'Project',
>> secondary=lambda: Base.metadata.tables['project_package'],
>> passive_deletes=True,
>> )
>>
>>
>> class ProjectPackage(Base):
>> __tablename__ = 'project_package'
>>
>> project_id = Column(
>> INTEGER(),
>> ForeignKey('projects.project_id', ondelete='cascade'),
>> primary_key=True
>> )
>> pkg_def_id = Column(
>> INTEGER(),
>> ForeignKey('package_definitions.pkg_def_id', ondelete='cascade'),
>> primary_key=True
>> )
>> app_id = Column(
>> SMALLINT(display_width=6),
>> ForeignKey('app_definitions.AppID', ondelete='cascade'),
>> primary_key=True
>> )
>>
>> app_definition = relationship('AppDefinition', uselist=False)
>> package_definition = relationship('PackageDefinition', uselist=False)
>> project = relationship('Project', uselist=False)
>>
>>
>> this model has serious issues, though it can be made to hobble along with
>> some small changes. The “three way association table” is not a great
>> pattern in the first place. Additionally, using that same table as
>> “secondary” in all those places is clearly the issue here, and they don’t
>> even have back_populates between them, which are used in many-to-many in
>> order to try avoid “double deleting”. But that wouldn’t solve the issue
>> here anyway.
>>
>
> As I feared, I figured this model would be frowned upon. :) Note that
> this model (or the underlying database)
> is not set in stone; if you have suggestions on a better way to link the
> three main tables, I would love to hear
> it (and we did add back_populates (somehow missed them during the
> restructuring of the models), but as
> you said, didn't help in this case).
>
>
> A row in the 3-way table means that for this project_id, and pkg_def_id,
> and app_id, that’s a single allowed value, so that’s star schema, OK
> (though they call star schema “denormalized” - i don’t exactly understand
> why, if you make all the columns part of the primary key). But then you
> have functions that give you lists of things based on two-column slices of
> that table, in order to relate different dimensions to each other;
> projects->app_definitions, means you can have Project (X) ->
> AppDefnition(Y) any number of times, for every value of pkg_def_id. I
> guess that’s the awkward part here. Those aren’t really collection-based
> “relationships” so much as analytical queries across dimension tables.
> They are valid queries but aren’t really “object/relational” - when we map
> a class to another with a many-to-many collection in between, that means
> something more specific.
>
> So maybe its just, object relational mapping assumes certain patterns are
> mappable to objects and collections, and 3-way associations aren’t quite in
> that pattern :).
>
Hmm, understood, though the main reason for these relationships were to
avoid having to make
DB queries within the main application code (pushing them out into the
models) and simplify the
logic; if you had seen what our application code looked like before, you
might shudder even more
than over what we're doing here. :)
Is there a way to essentially allow something like 'for app in
project.applications:' without having
to make an explicit query to the DB first, but avoiding the awkwardness of
the current setup?
--
- Ken Lareau
--
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.
