MySQL: Initialize pivot table with placeholders for nonexistent data
I'll try to give it as usual so that I can reuse it.
I am running a site with a fairly large MySQL database that has grown to initialize multiple pivot / cumulative tables. As an example, let's say these are football statistics. Since I am handling multiple soccer leagues in the same database, many of them play games of different lengths - for example indoor soccer leagues play four quarters and most outdoor leagues play halves.
I have three tables that are important for this exercise. I have edited all the fields that I do not consider relevant to the answer I am looking for.
GAME
`game`.id
`game`.home_team_id
`game`.away_team_id
`game`.number_of_periods
GOAL
// Records for each goal scored in the game
`goal`.id
`goal`.game_id
`goal`.team_id
`goal`.period_number
`goal`.player_id
`goal`.assist_player_id
PERIOD_SUMMARY
`period`.id
`period`.game_id
`period`.team_id
`period`.number
`period`.goals_scored
Ultimately I should have entries for EVERY period played in the period pivot table, regardless of whether a goal was scored. This table only needs to be initialized once, as it is quite easy to add the appropriate zero padded records with a trigger on game creation and run insert / update queries to update the period_summary table.
It's also pretty easy for me to group all the goals and initialize the period summary table with SUM (), which I have a little problem outlining an efficient way to "fill" any periods t has a goal scored from 0.
I'm trying to figure out if it's easier or more efficient:
- Write a trigger and populate the entire period_summary table with 0-filled values, and then run a query that I already know to update the corresponding records for the periods in which goals were scored.
- Use some other method (maybe a temporary stored procedure?) That will only 0-populate records if there is no match in the targets table.
a source to share
You already have a placeholder. "Placeholder for unknown data" in SQL is null.
You don't need to pre-fill anything: either you have a row with some columns that have an unknown value (null), or you don't have a row at all, so doing the outer join will end up with a row that's all null. In any case, the attribute data (essentially not id fields) will be empty.
And the aggregate sum()
will ignore zeros.
So, let's say that you have a line for the game (since it is planned in advance), but there are no corresponding lines for its periods (since they have not been played yet). Then you make an outer form to combine into the period (outer so that you include both games and games without period data):
select a.*, sum(b.goals_scored)
from game a left outer join period b on (b.game_id = a.id)
group by a.id;
This shows the general goals (for both teams) for the game; for games without periods, you return zero (which means in SQL, "we don't know yet")
This query only shows general goals for completed games and games (games for which at least one period has been played):
select a.*, sum(b.goals_scored)
from game a join period b on (b.game_id = a.id)
group by a.id;
This view filters out incomplete games (assuming you always add early periods to later ones):
create view complete_games as
select a.* from games a
where exists (select * from period b
where b.game_id = a.id and b.number = a.number_of_periods)
Using this view, we can then summarize only completed games:
select a.*, sum(b.goals_scored)
from complete_games a join period b on (b.game_id = a.id)
group by a.id;
So there is no need to pre-fill, no need for a trigger, most importantly, no need to add false data (requiring zero targets when in fact this period has not been replayed yet), no need to update the correct data, Just insert the period when u you have data for it.
a source to share
ISTM that Option 1 is clearly easier: you already know how to increase the bump counter if you can trust the counter is already there. Suppose you go with option 2, not only is it harder to fill in the missing zeros (I suppose this should happen at the end of the period), you will also need to find a way to start the counter from 1 if there is no previous entry and the first goal is scored.
In terms of space efficiency: Ultimately, you will need the same disk space anyway. It would be a little more efficient to fill the zeros only at the end of the period, but of course the space that the periods were running in would be larger than the space for the periods. Anyway.
As far as insert / update efficiency is concerned: you will need to search when the target is hammered anyway, because there might already be a non-zero counter. Therefore, you need to create an index that allows you to efficiently search for the game, team and period. Given that a query that is always updating is shorter, there is a good chance it is also more efficient.
a source to share