Thanks a lot Micheal. It was the way I was leaning forward to - but having confirmation from a long time user made me take the decision.
Have a nice day ! Pierre Le lun. 3 juil. 2023 à 22:54, 'Michael Mulqueen' via sqlalchemy < sqlalchemy@googlegroups.com> a écrit : > 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 > <https://groups.google.com/d/msgid/sqlalchemy/CAHxMHYUTkEbh5MHRZKW3H_qhXkk03HKr8pAv1Tp0VrkA0JM8rw%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/CAH4TWVsgLi5WNr%2BYXjJ4m1a0cN3fSeBA3QNZDx_F9FY3H%2BYU2A%40mail.gmail.com.
