After inserting data in MySQL, conditionally increase two values

Question

I currently have the following three tables in a football database:

teams(name)
season(name, beginning, end)
game(id, Date, season, hometeam, awayteam, HomeTeamScore, AwayTeamScore)
      (hometeam, awayteam and season are foreign keys)

Now I want to have a new table that records the goals scored and goals conceded by each team, as well as the points they earned in each season (one point for each draw, three for each win) . This will make ranking easy to obtain.

I considered creating a table like this:

stats(season, team, goalsscored, goalsconcedded, points)

Then every time a new match is inserted, I also update this table. This table will contain one row for each team's season combination. I'm not sure if this is the best solution as I know I'm introducing redundancy, but since this information needs to be calculated frequently I thought it might be useful. I want to create a trigger to update this information, but I don't really know how to do it: depending on which team is participating in the match, I need to update two rows in the stats table, and depending on whether they are home or away, I need Update them with different values.

Ideally, this trigger should create an entry in this new table if the team hasn't inserted a record for the season the game belongs to, but I'm not even sure if such a condition is possible in MySQL. I know I didn't provide any testing of what I did, but that's because I really couldn't find a similar request online (or more generally, one that makes it easy to query the information I need).

Also, I'm open to better ideas on how to handle this situation.

Answer 1

I think you should consider a structure like this:

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

This structure is not optimal either. In my personal opinion, you could go with a better structure:

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

The Goal table will be used to record every goal and you can then build match results from it, avoiding the use of the "HomeTeamScore" and "AwayTeamScore" fields.

As for the Stats table, you need to know who won the points, so let's stick with our final table structure:

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

The status field value in Matches can be: ['1','X','2']

• 1 - Home team wins
• X - Draw
• 2 - Away team wins

This way you can easily have everything to calculate your statistics, for example for team with 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';

You can use this as a starting point to build a slightly more complex query that uses the group by and group functions to calculate team statistics. Once you have successfully created a query like this, I recommend using Views

By the way, these queries you want to execute are not heavy, you don't necessarily need triggers, consider the database design first!

Answer 2

A much simpler way than using triggers to maintain redundant data is to use a view; this is just a basic union sum:

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
);