How do I properly do this many-to-many relationship in SQLAlchemy? Getting "Bidirectional attribute conflict detected"

Question

I am setting up my DB in code and am having trouble trying to figure out how to do a many-to-many relationship in the way I want to.

Basically, I have a User class, which has a m2m with the Team Class. Indeed, one team has many users, and one user can be part of many teams. This is all fine so far. The problem arises when I want to give "admin" rights to a user of the team.

I tried using an Association Object but I could not figure out how to mark the relationship as an admin. So instead I created a 2nd mapping table to map teams to their admins in the user table. As shown here:

Base = declarative_base()

# Many to Many relationship mapping tables
user_team_map = Table(
    'user_team_map', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id'), primary_key=True),
    Column('team_id', Integer, ForeignKey('teams.id'), primary_key=True)
)

admin_team_map = Table(
    'admin_team_map', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id'), primary_key=True),
    Column('team_id', Integer, ForeignKey('teams.id'), primary_key=True)
)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(24), nullable=False)
    teams = relationship('Team', secondary=user_team_map, back_populates='users')
    admin_of = relationship('Team', secondary=admin_team_map, back_populates='users')


class Team(Base):
    __tablename__ = 'teams'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    users = relationship('User', secondary=user_team_map, back_populates='teams')
    admins = relationship('User', secondary=admin_team_map, back_populates='teams')

When I try create these table I get: ValueError: Bidirectional attribute conflict detected: Passing object <User at 0x7f2089d7dcc0> to attribute "Team.admins" triggers a modify event on attribute "Team.users" via the backref "User.teams".

I am certainly not a DB wizard so I guess it is my overall approach which is incorrect. How should I go about achieving the relationship I want? If I should use the Association Object how would I do this?

Answer 1

(Qualification: I don't know SQLAlchemy, so I cannot answer in those terms. I will answer in Relational-Database and SQL terms: I am sure you can figure out the implementation. The terms I use are Relational only).

Yes, the error message is valid, even though the error message text might not be specific or indicative of the problem to you, because you do have duplicates (not acceptable in the database or the ORM). Eg. per Qualification, I would not use the term "*directional" because relationships do not have a direction in Relational databases or SQL (they have a parent and child).

There are two levels to the problem, I will explain it in logical order. The first level is the database storage, the second is the issue of converting the storage to Classes.

1 Storage

1.1 Associative Table

The physical level needs to be understood first. Although it normally results from logical design, it appears there is a gap there: you have gone to the physical without formally considering the logical.

What you have is an Associative table user_team_map. It associates users with teams, and resolves the n-to-n relationship into two 0-to-n relationships, one to each parent. (I would not use the word map either.)

• All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993.
• My IDEF1X Introduction is essential reading.

1.2 Yours, Relationalised

Then you need to identify that:

each team has 0-to-n has admins (which are users)

But you "could not figure out how to mark the relationship as an admin", so you duplicated the entire user_team_map structure into admin_team_map.

• That is a gross Normalisation error, which will cause problems at both the logical and physical levels. So it is no surprise that SQLAlchemy barfs at it (although the error message text is not indicative). For one, it allows an user to be an admin in a team who are not users in that team.

We simply need to "mark" an user as an admin in the user_team-map table. An Associative table contains only the references to the two parents, adding a column to it will move it out of the category of Associative table, into ordinary table.

• Nulls are suicide. They are due to Normalisation errors.

1.3 Relational Solution

• Note that this implements the Predicate:
  each team has 0-to-n admins
  not the Predicate:
  each team has 0-to-1 admin
• MemberName & AdminName are RoleNames of UserName

1.4 Record Filing System Solution (not recommended)

Due to the "theoreticians" and "authors" promoting and marketing 1960's pre-relational systems as "relational", in the "literature" and "textbooks", there is a lot of confusion. The resulting RFS has none of the Integrity; Power; or Speed of Relational systems. In any case, that is what ORMs use, that is what you are using, so I will provide that.

This is what one comes up with, if one follows such directions, which purport to be "relational".

• Duplicate rows, which are prohibited in the Relational Model, are not prevented
• the philistines love their duplicate Foreign Key references, and the circular reference problems that it causes.

1.5 Record Filing System Solution (not recommended)

If we fix just those two errors.

• Note that one additional column and one additional index is required in each table for the physical pointers Record Id.
• The proper indices (eg. on UserName) cannot be removed: if you do [1.4], you will allow duplicate rows (not records), which are prohibited.
• All other primitiveness has not been addressed
• In this simple example, if one subtracts the Record Ids, it is elevated to Relational [1.3].

2 Class

There appears to be errors of both understanding and implementation in the classes that you use. This is a classic and very common when using an ORM. The problem is compounded when the underlying storage is not properly understood or implemented.

I trust you will implement [1.5]:

• For sanity and ease of implementation (prevent preventable errors), the base Classes must match the storage in the database: these are Atomic, because you would CRUD them
• For the windows that require both an Atomic Class and multiples values (lists), you need a separate Class which is read only (you should not update the database via these Objects).

One More

If you would like to understand more about relationships, and how they are implemented at the SQL level (which hosts all the possibilities at the logical level), read this this answer (Part I only).