beerpong-elo/internal/repo/sqlSchema.sql

CREATE TABLE Players (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(255) NOT NULL,
    elo INT
);

CREATE TABLE Games (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    added DATETIME NOT NULL,
    score INT NOT NULL,
    overtime BOOL NOT NULL,
    author INT,
    team0player0 INT,
    team0player1 INT,
    team1player0 INT,
    team1player1 INT,                    
    FOREIGN KEY (author) REFERENCES Players(id) on DELETE SET NULL,
    FOREIGN KEY (team0player0) REFERENCES Players(id) ON DELETE SET NULL,
    FOREIGN KEY (team0player1) REFERENCES Players(id) ON DELETE SET NULL,
    FOREIGN KEY (team1player0) REFERENCES Players(id) ON DELETE SET NULL,
    FOREIGN KEY (team1player1) REFERENCES Players(id) ON DELETE SET NULL
);

CREATE TABLE GameResults (
  id INT AUTO_INCREMENT PRIMARY KEY,
  game INT,
  player INT,
  startElo INT,
  endElo INT,
  FOREIGN KEY (game) REFERENCES Games(id) on DELETE SET NULL,
  FOREIGN KEY (player) REFERENCES Players(id) on DELETE SET NULL
)

DELIMITER $$
CREATE TRIGGER UpdateCurrentELO
AFTER INSERT ON GameResults
FOR EACH ROW
BEGIN
    UPDATE Players
    SET elo = NEW.endElo
    WHERE id = NEW.player;
END$$

DELIMITER ;