Après avoir inséré des données dans MySQL, augmentez conditionnellement deux valeurs

Question

J'ai actuellement les trois tableaux suivants dans une base de données de football :

équipes(nom)
saison(nom, début, fin)
jeu (identifiant, date, saison, équipe à domicile, équipe à l'extérieur, HomeTeamScore, AwayTeamScore)
      (l'équipe locale, l'équipe extérieure et la saison sont des clés étrangères)

Maintenant, je souhaite avoir un nouveau tableau qui enregistre les buts marqués et les buts encaissés par chaque équipe, ainsi que les points qu'ils ont gagnés au cours de chaque saison (un point pour chaque match nul, trois pour chaque victoire). Cela rendra le classement facile à obtenir.

J'ai envisagé de créer un tableau comme celui-ci :

stats (saison, équipe, buts marqués, buts encaissés, points)

Ensuite, chaque fois qu'une nouvelle correspondance est insérée, je mets également à jour ce tableau. Ce tableau contiendra une ligne pour chaque combinaison équipe+saison. Je ne sais pas si c'est la meilleure solution car je sais que j'introduis de la redondance, mais comme ces informations doivent être calculées fréquemment, j'ai pensé que cela pourrait être utile. Je souhaite créer un déclencheur pour mettre à jour ces informations, mais je ne sais pas trop comment faire : selon l'équipe qui participe au match, je dois mettre à jour deux lignes dans le tableau des statistiques, et selon qu'elles le soient à la maison ou à l'extérieur, j'ai besoin de les mettre à jour avec des valeurs différentes.

Idéalement, ce déclencheur devrait créer une entrée dans cette nouvelle table si l'équipe n'a pas inséré d'enregistrement pour la saison à laquelle appartient le jeu, mais je ne suis même pas sûr qu'une telle condition soit possible dans MySQL. Je sais que je n'ai fourni aucun test que j'ai effectué, mais c'est parce que je n'ai vraiment pas pu trouver de demande similaire en ligne (ou plus généralement, une demande permettant d'interroger facilement les informations dont j'ai besoin).

De plus, je suis ouvert à de meilleures idées sur la façon de gérer cette situation.

Answer 1

Je pense que vous devriez envisager une structure comme celle-ci :

Teams(id,name)
Seasons(id, year,begin_date,end_date)
Games(id, date, season (外键), hometeam (外键), awayteam (外键), HomeTeamScore, AwayTeamScore)

Cette structure n’est pas non plus optimale. À mon avis, vous pourriez opter pour une meilleure structure :

Teams(id,name)
Seasons(id, year,begin_date,end_date)
Matches(id, date, season (外键), home_team (外键), away_team (外键))
Goals(id,team,game,player?)

Le tableau Goal sera utilisé pour enregistrer chaque but et vous pourrez ensuite construire des résultats de match à partir de celui-ci, en évitant l'utilisation des champs "HomeTeamScore" et "AwayTeamScore".

En ce qui concerne le tableau des statistiques, vous devez savoir qui a gagné les points, alors restons fidèles à notre structure de table finale :

Teams(id,name)
Seasons(id, year,begin_date,end_date)
Matches(id, date, season (外键), home_team (外键), away_team (外键), status)
Goals(id,team,game,player?)

La valeur du champ d'état dans les correspondances peut être : ['1','X','2']

• 1 - L'équipe locale gagne
• X - tirage au sort
• 2 - L'équipe à l'extérieur gagne

De cette façon, vous pouvez facilement avoir tout pour calculer vos statistiques, par exemple pour l'équipe avec l'ID 12 :

Select * from Matches where home_team = 12 and result = '1';
Select * from Matches where away_team = 12 and result = '2';
Select * from Matches where home_team = 12 or away_team=12 and result='X';

Vous pouvez l'utiliser comme point de départ pour créer une requête légèrement plus complexe en utilisant les fonctions group by et group pour calculer les statistiques de l'équipe. Une fois que vous avez créé avec succès une requête comme celle-ci, je vous recommande d'utiliser Views

D’ailleurs, ces requêtes que vous souhaitez exécuter ne sont pas lourdes, vous n’avez pas forcément besoin de triggers, pensez d’abord au design de la base de données !

Answer 2

Un moyen beaucoup plus simple que d'utiliser des déclencheurs pour conserver des données redondantes est d'utiliser une vue, il s'agit simplement d'une somme syndicale de base :

create view stats as (
    select season, team, sum(goalsscored) goalsscored, sum(goalsconcedded) goalsconcedded, sum(points) points
    from (
        select season, hometeam team, HomeTeamScore goalsscored, AwayTeamScore goalsconcedded,
            case when HomeTeamScore > AwayTeamScore then 3 when HomeTeamScore=AwayTeamScore then 1 else 0 end points
        from game
        union all
        select season, awayteam team, AwayTeamScore goalsscored, HomeTeamScore goalsconcedded,
            case when AwayTeamScore > HomeTeamScore then 3 when AwayTeamScore=HomeTeamScore then 1 else 0 end points
        from game
    ) games
    group by season, team
);