alan-turing-institute / rl_tournament

Reinforcement Learning Tournament Director
3 stars 3 forks source link

Design database schema #9

Open nbarlowATI opened 3 years ago

nbarlowATI commented 3 years ago

Suggestion (SQLAlchemy) for pretty minimal set of classes (tables):

class Team(Base):
    __tablename__ = "team"
    team_id = Column(Integer, primary_key=True, nullable=False,
                      autoincrement=True)
    matches = relationship("Match", uselist=True,
                         back_populates="team")

class Match(Base):
    __tablename__ = "match"
    match_id = Column(Integer, primary_key=True, nullable=False,
                      autoincrement=True)
    match_time = Column(DateTime, nullable=False)
    pelican_team = relationship("Team", back_populates="team")
    # docker image name and tag
    pelican_agent = Column(String(100), nullable=False)
    panther_team = relationship("Team", back_populates="team")
    # docker image name and tag
    panther_agent = Column(String(100), nullable=False)
    # link to game config json
    game_config = Column(String(100), nullable=False)
    games = relationship("Game", uselist=True,
                         back_populates="match")

class Game(Base):
    __tablename__ = "game"
    game_id = Column(Integer, primary_key=True, nullable=False,
                     autoincrement=True)
    num_turns = Column(Integer, nullable=False)
    result_code = Column(String(100), nullable=False)
    # link to logfile (on cloud storage)
    logfile_url = Column(String(100), nullable=False)
    match = relationship("Match", back_populates="game")
    match_id = Column(Integer, ForeignKey("match.match_id"))

So a "Match" is made up of some number of individual "Games". The Match and Game classes would then have methods to return who won them, based on the "result_code" for each Game.

tomaslaz commented 3 years ago

I would suggest to consider:

class Team(Base)

To add:

team_name = Column(String(100), nullable=False) team_members = Column(String(1000), nullable=False))

nbarlowATI commented 3 years ago

I now think we should have an Agent class / table, since it is actually agents that play the tournaments, and one team can have more than one agent (even in the same tournament). So a lot of the relations that Team had can move into Agent, and there is a one-to-many relation between Team and Agent.