How to model several tournaments / brackets types into a SQL database?

Question

I want to model a database to store data of several types of tournaments (whith different types of modes: single rounds, double rounds, league, league + playoffs, losers, ...).

Maybe, this project would be a kind of Challonge: www.challonge.com

My question is: How to create a model in sql-relationship database to store all this types of tournaments?

I can't imagine how to do this work. There is a lot of different tables but all tables is related to one attribute: tournamentType...

Can I store a tournamentType field and use this field to select the appropiate table on query?

Thanks you

Answer 1

I can understand why you're struggling with modeling this. One of the key reasons why this is difficult is because of the object relational impendance-mismatch. While I am a huge fan of SQL and it is an incredibly powerful way of being able to organize data, one of its downfalls - and why NoSQL exists - is because SQL is different from Object Oriented Programming. When you describe leagues, with different matches, it's pretty easy to picture this in object form: A Match object is extended by League_Match, Round_Match, Knockout_Match, etc. Each of these Match objects contains two Team objects. Team can be extended to Winner and Loser...

But this is not how SQL databases work.

So let's translate this into relationships:

I want to model a database to store data of several types of tournaments (whith different types of modes: single rounds, double rounds, league, league + playoffs, losers, ...).

• Tournaments and "modes" are a one to many (1:n) relationship.
• Each tournament has many teams, and each team can be part of many tournaments (n:n).
• Each team has many matches, and each match has two teams (n:n).
• Each tournament has multiple matches but each match only belongs to one tournament (1:n).

The missing piece here that is hard to define as a universal relationship? - In rounds, each future match has two teams. - In knockout matches, each future match has an exponential but shrinking number of choices depending on the number of initial teams.

You could define this in the database layer or you could define this in your application layer. If your goal is to keep referential integrity in mind (which is one of the key reasons I use SQL databases) then you'll want to keep it in the database.

Another way of looking at this: I find that it is easiest for me to design a database when I think about the end result by thinking of it as JSON (or an array, if you prefer) that I can interact with.

Let's look at some sample objects:

Tournament:

[
    {
        name: "team A",
        schedule: [
            {
                date: "11/1/15",
                vs: "team B",
                score1: 2,
                score2: 4
             },
             {
                date: "11/15/15",
                vs: "team C",
             }
        ]
    }
],
[
   //more teams
]

As I see it, this works well for everything except for knockout, where you don't actually know which team is going to play which other team until an elimination takes place. This confirms my feeling that we're going to create descendants of a Tournament class to handle specific types of tournaments.

Therefore I'd recommend three tables with the following columns:

Tournament
- id (int, PK)
- tournament_name
- tournament_type

Team
- id (int, PK)
- team_name (varchar, not null)
# Any other team columns you want.

Match
- id (int, PK, autoincrement)
- date (int)
- team_a_score (int, null)
- team_b_score (int, null)
- status (either future, past, or live)
- tournament_id (int, Foreign Key)

Match_Round
- match_id (int, not null, foreign key to match.id)
- team_a_id (int, not null, foreign key to team.id)
- team_b_id (int, not null, foreign key to team.id)

Match_Knockout
- match_id (int, not null, foreign key to match.id)
- winner__a_of (match_id, not null, foreign key to match.id)
- winner_b_of (match_id, not null, foreign key to match.id)

You have utilized sub-tables in this model. The benefit to this is that knockout matches and round/league matches are very different and you are treating them differently. The downside is that you're adding additional complexity which you're going to have to handle. It may be a bit annoying, but in my experience trying to avoid it only adds more headaches and makes it far less scalable.

Now I'll go back to referential integrity. The challenge with this setup is that theoretically you could have values in both Match_Round and Match_Knockout when they only belong in one. To prevent this, I'd utilize TRIGGERs. Basically, stick a trigger on both the Match_Round and Match_Knockout tables, which prevents an INSERT if the tournament_type is not acceptable.

Although this is a bit of a hassle to set up, it does have the happy benefit of being easy to translate into objects while still maintaining referential integrity.

Answer 2

You could create tables to hold tournament types, league types, playoff types, and have a schedule table, showing an even name along with its tournament type, and then use that relationship to retrieve information about that tournament. Note, this is not MySQL, this is more generic SQL language:

CREATE TABLE tournTypes (
ID int autoincrement primary key,
leagueId int constraint foreign key references leagueTypes.ID,
playoffId int constraint foreign key references playoffTypes.ID
--...other attributes would necessitate more tables
)

CREATE TABLE leagueTypes(
ID int autoincrement primary key,
noOfTeams int,
noOfDivisions int,
interDivPlay bit -- e.g. a flag indicating if teams in different divisions would play
)

CREATE TABLE playoffTypes(
ID int autoincrement primary key,
noOfTeams int,
isDoubleElim bit -- e.g. flag if it is double elimination
)

CREATE TABLE Schedule(
ID int autoincrement primary key,
Name text,
startDate datetime,
endDate datetime,
tournId int constraint foreign key references tournTypes.ID
)

Populating the tables...

INSERT INTO tournTypes VALUES
(1,2),
(1,3),
(2,3),
(3,1)

INSERT INTO leagueTypes VALUES
(16,2,0), -- 16 teams, 2 divisions, teams only play within own division
(8,1,0),
(28,4,1)

INSERT INTO playoffTypes VALUES
(8,0), -- 8 teams, single elimination
(4,0),
(8,1)

INSERT INTO Schedule VALUES
('Champions league','2015-12-10','2016-02-10',1),
('Rec league','2015-11-30','2016-03-04-,2)

Getting info on a tournament...

SELECT Name
,startDate
,endDate
,l.noOfTeams as LeagueSize
,p.noOfTeams as PlayoffTeams
,case p.doubleElim when 0 then 'Single' when 1 then 'Double' end as Elimination
FROM Schedule s
INNER JOIN tournTypes t
ON s.tournId = t.ID
INNER JOIN leagueTypes l
ON t.leagueId = l.ID
INNER JOIN playoffTypes p
ON t.playoffId = p.ID