Updating the Tournament Format

As I mentioned in my last post, as I worked through the tournaments (now I have the basics of 1970-2010 loaded in the database), I realized that my simple tournament format table wasn't going to work. Without over engineering things, and after reviewing the formats of all the World Cups, I decided to create a new table that stored the content of each round, and then in the tournament_format table, I added a round_1_format_code, round_2_format_code, and round_3_format_code columns linking to this new table. The new table holds information about if the round is a group or playoff, and then if it's a group, the number of teams that advance from each group, and the overall number of teams that should progress from this stage. This helped to solve some of the 3rd place teams, (e.g. in Mexico 1986, there were 24 teams in 6 groups in the group stage, and the top 2 from each group went through, (12 teams), as well as 4 third place teams). This forced me to add a 'round_number' column (not to be confused with 'round_code') to the game table, to track what stage of the tournament we are in. So far it's all working great!!

Next time I'm going to talk about 2 other challenges I had, co-hosted tournaments (Korea & Japan), and the change in points (2 points for a win in all tournament from 1930-1990, 3 points for a win in all tournaments from 1994 - 2010.