Flask/SQLAlchemy - Difference between association model and association table for many-to-many relationship? Flask/SQLAlchemy - Difference between association model and association table for many-to-many relationship? flask flask

Flask/SQLAlchemy - Difference between association model and association table for many-to-many relationship?


My apologies, I finally stumbled across the answer in the SQLAlchemy docs...

https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many

...where they explicitly define the difference:

Many to Many adds an association table between two classes.

association_table = Table('association', Base.metadata,    Column('left_id', Integer, ForeignKey('left.id')),    Column('right_id', Integer, ForeignKey('right.id')))

The association object pattern is a variant on many-to-many: it’s used when your association table contains additional columns beyond those which are foreign keys to the left and right tables. Instead of using the secondary argument, you map a new class directly to the association table.

class Association(Base):    __tablename__ = 'association'    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)    extra_data = Column(String(50))    left = relationship('Left', backref=backref('right_association'))    right = relationship('Right', backref=backref('left_association'))

Where "Right" and "Left" are tables, defined normally:

class Left(Base):    __tablename__ = 'left'    id = Column(Integer, primary_key = True)    ...class Right(Base):    __tablename__ = 'right'    id = Column(Integer, primary_key = True)    ...

So it's basically creating an association object to reference this extra information if you need to store anything in the association, otherwise it's not necessary to use the ORM layer and you can just create an association table.