Tournament table formation, stored SQL procedures

The other day I read a post about the automated formation of football championships and decided to share my solution to this problem, which I used for a small game. The implementation of the draw is not a standard approach, using MS SQL Server stored procedures.

As a result, I got a database structure and stored procedures that allow me to create a table of games between teams (to draw) and process the championship results. All scripts can be downloaded from the repository on github .

Championship game table

The main stored procedure is the formation of championship games between teams. When forming, I adhered to the basic rules of the tournament:
  • The number of teams participating in the tournament must be even;
  • Each team must play with the other 2 times - in its own stadium and in the stadium of rivals;
  • In one round, the same team can play only once;
  • For winning the match, the team receives - 2 points, for a draw - 1 point, and for losing, respectively - 0.

Let's take a step-by-step look at the algorithm for generating a game table. I will try to describe the logic in detail, not boring and with a demonstration of circuits. As an example, let's take a championship in which 4 teams participate, although the algorithm can work with any even number of teams. Conditionally, let us designate our teams with numbers 1, 2, 3 and 4, which in my implementation are their direct IDs.

The first thing to do was to calculate the number of championship tournaments and form pairs of teams for the games. The number of tournaments depends on the number of teams, since one team can play in one round only once and it should not play with itself. This is calculated by the formula:

@TournamentsCnt = (@TeamsCnt * (@TeamsCnt - 1) * 2) / @TeamsCnt

After that, we create a list of team pairs for all the championship games. To do this, we can assume that we have 2 lists with identical numbers for all the championship teams.

Each team of the first list must play with each team of the second list once, excluding the team with the same number (itself). As a result, we get a list of pairs of teams of the championship of the following type.

After we have received a list of pairs of teams, we need to distribute them among the rounds without forgetting the condition: one team can play only once per round and two teams can play only twice with each other for the championship in the home and guest stadiums.

The number of games that take place in one round is equal to the total number of championship teams. To form a tour, the very first pair of teams from the previously generated list is taken, team numbers are recorded in the tour game and in a temporary array, and they are deleted from the list of team pairs. The same logic will be used to form the remaining games of the tour, only now pairs of teams whose numbers are in the temporary array will be skipped, so two identical teams will not be able to play in one round, but no team will be left idle. Upon completion, the temporary array is cleared and this algorithm will be repeated until all tours are filled.

At the end of the algorithm, the list of all pairs of championship teams will be empty, games will be formed and all rounds filled with games - everything complies with the rules of the championship.

The main algorithm that I wanted to share is described, I did not go into the details of the syntax of SQL stored procedures, since this is another topic and another post. All scripts with example implementation can be found in the github repository. At the moment, the scripts are running and tested on MS SQL Server, if this is interesting for you, then I will rewrite it under the DBMS that you use. The project also contains stored procedures for calculating the best goalkeeper, scorer, “Champion's Way” and several others for interacting with championship data.

For the scripts to work correctly, you must first create the FootballTournament database. For the stored counting procedures to work better than the scorer, goalkeeper and other auxiliary stored procedures, the first two should be performed: the formation of tournaments and the generation of random data from game results.

Run the scripts from the example in the following order:
  1. Formation of the database schema ' schema.sql '.
  2. Filling tables with players, teams, coaches and their stadiums ' fillTestData.sql '.
  3. Creating stored procedures ' storedProcedurs.sql '.
  4. Calling stored procedures ' execute.sql '.

I thank everyone who was interested in reading to the end.

Link to the project repository on github

Also popular now: