Model Relationship for different User Roles: What am I missing?

Question

I have had this brain teaser for a day now and I just crossed the point of self-help to seeking help from others. Essentially, I want to map certain models to build an API (Learning Management System API) using FastAPI.

The models: User (with certain roles: Admin, Student, Teacher), Profile, Student, and Teacher.

Here's what I have been able to accomplish so far with these models and their relationship: (Installed and using: FastAPI, SqlAlchemy, Pydantic)

class User(Base):
    __tablename__= "users"

    id: Mapped[uuid_pkg.UUID] = mapped_column(default_factory=uuid_pkg.uuid4, primary_key=True, unique=True, index=True)
    email: Mapped[str] = mapped_column(String(), unique=True, index=True, default=None)
    hashed_password: Mapped[str] = mapped_column(String(), nullable=False, default=None)   
    user_role = relationship("UserRole", back_populates="user", uselist=False,)

I can create users based on the above using schema and my CRUD functions.

class UserRole(Base):
    __tablename__ = "user_roles"
    user_id = Column(
        UUID(as_uuid=True),
        ForeignKey("users.id"),
        primary_key=True,
        nullable=False,
    )
    role_id = Column(
        UUID(as_uuid=True),
        ForeignKey("roles.id"),
        primary_key=True,
        nullable=False,
    )

    role = relationship("Role")
    user = relationship("User", back_populates="user_role", uselist=False)  #one-to-one

I can create a user role based on the above using schema and my CRUD functions.

class Role(Base):
    __tablename__ = "roles"

    id = Column(
        UUID(as_uuid=True), primary_key=True, index=True, default=uuid4
    )
    name = Column(String(100), index=True)   #Admin, Student, Teacher
    description = Column(Text) #describes the Role.name column


    __table_args__ = (
        UniqueConstraint("user_id", "role_id", name="unique_user_role"),
    )

I can create a role based on the above using schema and my CRUD functions.

class Profile(Base):
    __tablename__ = "profile"

    id: Mapped[uuid_pkg.UUID] = mapped_column(default_factory=uuid_pkg.uuid4, primary_key=True, unique=True)

    first_name= mapped_column(String(50))
    last_name= mapped_column(String(50))
    
    phone: Mapped[str] = mapped_column(String(15), default=None)

    avatar: Mapped[str] = mapped_column(String, default="https://something-something.com/xf.jpg")

    user_id: Mapped[uuid_pkg.UUID] = mapped_column(ForeignKey("users.id"), default_factory=uuid_pkg.uuid4, nullable=False)

    user = relationship("User", back_populates="profile", uselist=False)
  

class BaseRole(Base):
    #holds similar data across role type: Admin, Student, Teacher
    __abstract__ = True   #abstract so no table gets created

    id: Mapped[uuid_pkg.UUID] = mapped_column(default_factory=uuid_pkg.uuid4, primary_key=True, unique=True)

    identification_document: Mapped[str] = mapped_column(String(), unique=True, index=True, default=None)
    identification_number: Mapped[str] = mapped_column(String(), unique=True, index=True, default=None)
    identification_type: Mapped[str] = mapped_column(String(), index=True, default=None)

    areas_of_interest: Mapped[List | None] = mapped_column(MutableList.as_mutable(ARRAY(String)))

class Student(BaseRole):
    __tablename__ = "student"

    class InterestedCourse(enum.Enum):  #one course track at any time
        SME = "sme"
        IT = "it"
        NA = "n/a"

    course: Mapped[str] = mapped_column(Enum(InterestedCourse), default=InterestedCourse.NA)

    @declared_attr
    def user_id(cls):
        return  mapped_column(ForeignKey("users.id"), primary_key=True, index=True, unique=True, nullable=False)
    
    @declared_attr
    def user(cls):
        return relationship("User", back_populates=Student.__tablename__, uselist=False)

    __mapper_args__ = {'polymorphic_identity':'student', 'concrete':True}

class Teacher(BaseRole):
    __tablename__ = "teacher"

    ......

I have permissions setup on my routing using the below code and this works:

current_user: models.User = Security(
        dependencies.get_current_active_user,
        scopes=[Role.ADMIN["name"]],
    ),

ERROR (this error is from the abstract-concrete class of BaseRole and Student and Teacher):

sqlalchemy.exc.InvalidRequestError: Mapper properties (i.e. deferred,column_property(), relationship(), etc.) must be declared as @declared_attr callables on declarative mixin classes.  For dataclass field() objects, use a lambda:

NOTE: user_id in user_roles table is not unique on an intentional basis: A user can be an admin and a teacher.

END OF CODE.

My Questions:

1. Is the model relationship the ideal way to go (in terms of building on user roles, user profile, and the role models) or have I over skewed the relationship and need to go back to my drawing board?
2. The reason I am inheriting from an abstract class is so I do not have to rewrite common fields for Student and Teacher, how can I resolve the ERROR? What am I missing?

Answer 1

Mixins!

I pretty much just used BaseRole as a Mixin where I need it to be:

class Student(Base, BaseRole):
        __tablename__ = "student"
    
        class InterestedCourse(enum.Enum):  #one course track at any time
            SME = "sme"
            IT = "it"
            NA = "n/a"
    
        course: Mapped[str] = mapped_column(Enum(InterestedCourse), default=InterestedCourse.NA)
    
#    @declared_attr
#    def user_id(cls):
#        return  mapped_column(ForeignKey("users.id"), primary_key=True, //index=True, unique=True, nullable=False)
        
#    @declared_attr
#    def user(cls):
#       return relationship("User", back_populates=Student.__tablename__, uselist=False)

#    __mapper_args__ = {'polymorphic_identity':'student', 'concrete':True}  #could have stayed