I have league and tournament separately because league can consist of tournaments.
This is my database model so far for all kinds of statistics and complicated rating system(robustness, ratings … )
How should i modify my database for tournaments , like single elimination, double elimination, round robin or round-robin + single elimination.
I was thinking that i will make a procedure for tournaments/leagues that makes a view and adds all people there who play on that tournament and then the procedure looks who is playing with who and those who win get to next game or something ?
For example lets say i get 32 players and i must pair them with each other , that is not very hard. But what happens when 16 people win the game and go to next round ? How to i show this in tournament table ? Keep in mind that this must be done instantly and automatically , because i have to show it on screen while tournament is ongoing.
Okay if that is easy , then how should i show for example 32 players doing double elimination , even if you lose you get a second game and the mechanics is complicated …?
Also what if the player-pool changes , what if there is 17 players ?
You have a number of questions that you need to answer, but there is one disconnect that jumps out at me: you want to reflect functionality in your DB to a screen display, but you have not considered how to connect the pieces. What the solution needs is:
In other words, you need to create a server program that will access the database and present the data to the web page
Now for your database – I think a tournament is a special case of a league and not a different entity, so I would eliminate that, and add a BOOLEAN to the league table “isTournament” to flag that the competition is a tournament rather than a plain ol’ league.
When you create a new league, you generate all the games to be played in the games table. As they are played, you update the winnerId so you can select the completed games records and calculate the rankings.
For a tournament, it’s slightly different. You generate the games for the first round, and generate the next round game records based on the selection of winners. If you have an uneven number of players, one player will be given a bye into the next round, so you simply mark him as the winner as you create the game. Do you have rules for who the winners of one round play in the next round? You can apply these to the system automatically as the results arrive in.