Hi Pierre, This isn't an official answer, I'm just a long time user of SQLAlchemy.
Either way should work fine. The association object is driven by the columns on the association table being FKs, whether or not they're part of a PK isn't relevant. I've used both ways. In my experience, an artificial PK is easier to maintain in the long run. Each way has its minor advantages and disadvantages, but generally a single artificial PK would be my preference. Mike On Mon, 3 Jul 2023, 16:43 Pierre Massé, <pierre.masse.m...@gmail.com> wrote: > Dear all, > > I am currently reworking a bit of my model and stumbled into this > question, which I think mainly has opinionated answers - but I would like > to have some insight regarding SQLAlchemy usage or preferences. > > I have a situation where I am in the exact same case like the one > described in the Association Object > <https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object> > in > the SQLAlchemy ORM docs. > > I want to modelize : > - Caregiver - a person taking care of one or more Helpee > - Helpee - a person who is being taken care of, by one or more Caregiver > - their Relationship, which links a Caregiver to a Helpee, but with > additional data like their family ties (spouse, parent, friend, ...) > > This is typically the Association object use case, a many to many > relationship, holding additional data. > > So far, I have been using a "natural" primary key on the Relationship > table, by using the Caregiver Id, and the Helpee Id to form a composite > primary key. > > From a handful of blog posts (this StackOverflow answer > <https://dba.stackexchange.com/a/6110> being quite in depth), it looks > like adding an "artificial" or surrogate primary key on the Relationship > table should be the way to go. Of course, I would keep a unique constraint > on (Caregiver Id x Helpee Id) on this table along the new primary key. > > My questions are : > - is the addition of a surrogate primary key a good idea - without taking > into account the fact that I am using SQLAlchemy? > - would the "magic" of the association object still operate even though > the mapped ORM relationships would not be part of the primary key anymore? > > The docs example would become: > > > from typing import Optional > > from sqlalchemy import ForeignKey > from sqlalchemy import Integer > from sqlalchemy.orm import Mapped > from sqlalchemy.orm import mapped_column > from sqlalchemy.orm import DeclarativeBase > from sqlalchemy.orm import relationship > > > class Base(DeclarativeBase): > pass > > > class Association(Base): > __tablename__ = "association_table" > * id: Mapped[int] = mapped_column(primary_key=True)* > left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id")*, > primary_key=True*) > right_id: Mapped[int] = mapped_column( > ForeignKey("right_table.id")*, primary_key=True* > ) > extra_data: Mapped[Optional[str]] > child: Mapped["Child"] = relationship(back_populates="parents") > parent: Mapped["Parent"] = relationship(back_populates="children") > * __table_args__ = (UniqueConstraint('left_id', 'right_id', > name='_relationship_uc'),)* > > > class Parent(Base): > __tablename__ = "left_table" > id: Mapped[int] = mapped_column(primary_key=True) > children: Mapped[List["Association"]] = > relationship(back_populates="parent") > > > class Child(Base): > __tablename__ = "right_table" > id: Mapped[int] = mapped_column(primary_key=True) > parents: Mapped[List["Association"]] = > relationship(back_populates="child") > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAH4TWVvPVKtjtyVHJO9WtX2ZCjmdX3aGWDBGCQrvU_7c-CswEg%40mail.gmail.com > <https://groups.google.com/d/msgid/sqlalchemy/CAH4TWVvPVKtjtyVHJO9WtX2ZCjmdX3aGWDBGCQrvU_7c-CswEg%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAHxMHYUTkEbh5MHRZKW3H_qhXkk03HKr8pAv1Tp0VrkA0JM8rw%40mail.gmail.com.
