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.
