Using SQLAlchemy, I need to assign users into pools, and a ranking for each pool they’re in

Here is the relevant code.

I’m writing a Flask application where users can join pools and compete against others within those pools (currently, pools and users are in a many to many relationship). Each user will need a rating for each pool that he/she is in (Elo Rating), and I’m not sure how to implement that into my existing structure. Any suggestions to implement it that either fit into my solution or change it would be appreciated.

(Right now, each user has a rating but it is shared amongst pools, which does not make sense for Elo)

def load_user(user_id):
    return User.query.get(int(user_id))

pool_user = db.Table('pool_user', 
    db.Column('pool_id', db.Integer, db.ForeignKey('pool.id')),
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')))

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
    password = db.Column(db.String(60), nullable=False)
    rating = db.Column(db.Integer, nullable=False)
    pools = db.relationship('Pool', secondary=pool_user, backref=('members'))

    def get_reset_token(self, expires_sec=1800):
        s = Serializer(current_app.config['SECRET_KEY'])
        return s.dumps({'user_id': self.id}).decode('utf-8')
    
    @staticmethod
    def verify_reset_token(token):
        s = Serializer(current_app.config['SECRET_KEY'])
        try:
            user_id = s.loads(token)['user_id']
        except:
            return None
        return User.query.get(user_id)

    def __repr__(self):
        return f"User('{self.username}', '{self.email}', '{self.image_file}', '{self.rating}')"

class Pool(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), nullable=False)

    def __repr__(self):
        return f"Pool: {self.name}"

Answer

For me, you should replace association table (pool_user) with association model (UserPool).

def load_user(user_id):
    return User.query.get(int(user_id))

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
    password = db.Column(db.String(60), nullable=False)
    pools = db.relationship('UserPool')

    def get_reset_token(self, expires_sec=1800):
        s = Serializer(current_app.config['SECRET_KEY'])
        return s.dumps({'user_id': self.id}).decode('utf-8')
    
    @staticmethod
    def verify_reset_token(token):
        s = Serializer(current_app.config['SECRET_KEY'])
        try:
            user_id = s.loads(token)['user_id']
        except:
            return None
        return User.query.get(user_id)

    def __repr__(self):
        return f"User('{self.username}', '{self.email}', '{self.image_file}'"

class Pool(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), nullable=False)
    members = db.relationship('UserPool')

    def __repr__(self):
        return f"Pool: {self.name}"

class UserPool(db.Model):
    pool_id = db.Column(db.Integer, db.ForeignKey('pool.id'), primary_key=True),
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
    rating = db.Column(db.Integer, nullable=False)