Should I use a surrogate primary key on an Association Object pattern?

[Pierre Massé]

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 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 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")

[Michael Mulqueen]

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

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAH4TWVvPVKtjtyVHJO9WtX2ZCjmdX3aGWDBGCQrvU_7c-CswEg%40mail.gmail.com.

[Pierre Massé]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAHxMHYUTkEbh5MHRZKW3H_qhXkk03HKr8pAv1Tp0VrkA0JM8rw%40mail.gmail.com.