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 :).
--
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.
