Sqlalchemy multiple many to many

Question

I'm pretty new to Sqlalchemy and in general I'm not a SQL guru. I'm currently trying to build a model to manage different workspaces (you can see them as organizations), with the following requirements:

A user can create multiple workspaces
When a user creates a workspace, it has automatically assigned the "owner" Role
One user can have just one role in the workspace
Every workspace can contain multiple users with different roles
In every workspace I can create Teams
Different workspaces can have the same team name (but different team_id)
Teams can live without users assigned to them, but they stick to workspaces
Users can be assigned to a team by the owner of the workspace

This is the code I wrote until now:

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    email = Column(String, unique=True, index=True, nullable=False)
    first_name = Column(String)
    last_name = Column(String)
    password = Column(String, nullable=False)
    is_active = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)


class Workspace(Base):
    __tablename__ = "workspace"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String)
    description = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)
    users = relationship(User, lambda: user_workspace, backref='workspaces')


class Role(Base):
    __tablename__ = "role"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String)
    capabilities = Column(Integer)



class Team(Base):
    __tablename__ = "team"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String)
    workspace = relationship(Workspace, lambda: user_workspace, backref='teams')


user_workspace = Table(
    'user_workspace', Base.metadata,
    Column('user_id', Integer, ForeignKey(User.id), primary_key=True),
    Column('workspace_id', Integer, ForeignKey(Workspace.id), primary_key=True),
)

Now if I create a new workspace I'm able to automatically populate the user_workspace table with the workspace_id and the user_id. But currently I'm a bit blocked on how to extend the user_workspace table so it can contain references also to role_id and team_id.

Anyone can suggest me the best approach? I searched a lot, but I didn't really find something similar to this (maybe it's just me being newbie with that stuff). Thanks you in advance!

Sqlalchemy multiple many to many

0 Answers