For the past year I've been watching Apex Legends tournaments on Twitch with great excitement. I discovered that there is no dedicated tournament software that is geared towards an Apex Twitch community.
In this exercise, the following facts are to be modeled: A Twitch streamer can host any number of tournaments for his community. Each tournament consists of a maximum of 20 teams. A team in turn consists of a maximum of three players, one of whom is the team leader.
Each tournament has any number of rounds. A community moderator manually sets the status of the tournament to started, checked and completed.
Each team can achieve a certain number of points in a round. These are made up of the placement and the number of kills.
BTW: Leave us an email if you are interested in the software ;-)
Solution
Schema
Table user {
id integer [primary key, not null, unique]
name char(255) [not null, unique]
// can be retrieved by using the Twitch OAuth login
email char(255) [not null, unique]
// is the user a Twitch streamer?`
is_organizer tinyint(1)
}
// status of the tourney
enum tourney_status {
PREPARATION
RUNNING
CLOSED
}
Table tourney {
id integer [primary key, not null, unique]
// public
guid uuid [not null, unique]
name char(255) [not null]
status toruney_status [not null, default: "PREPARATION"]
}
enum round_status {
NOT_STARTED
IN_PROGRESS
IN_REVIEW
FINISHED
}
Table round {
id integer [primary key, not null, unique]
// public
guid uuid [not null, unique]
status round_status [not null, default: "NOT_STARTED"]
tourney_id integer [not null, ref: > tourney.id]
}
Table team {
id integer [primary key, not null, unique]
guid uuid [not null, unique]
name char(255) [not null]
tourney_id integer [not null, ref: > tourney.id]
}
// role of a tourney participant
enum participation_role {
// ORGANIZER = Twitch streamer
ORGANIZER
// MODERATOR = elected by the Twitch streamer
MODERATOR
// TEAM_LEADER = a user who has created a team
TEAM_LEADER
// TEAM_PLAYER = a user who has joined a team
TEAM_PLAYER
}
Table participant {
id integer [primary key, not null, unique]
role participation_role [not null, default: "TEAM_PLAYER"]
user_id integer [not null, ref: > user.id]
tourney_id integer [not null, ref: > tourney.id]
// a participant can *not* belong to a team if he is a organizer or moderator
team_id integer [null, ref: > team.id]
}
// a team's result for a given round
Table result {
id integer [primary key, not null, unique]
// public guid is not required, as it can be derived from the tourney/round/team combination
placement_points integer [not null, default: "0"]
kill_points integer [not null, default: "0"]
// total_points is basically SUM(placement_points, kill_points) and can be calculated with a trigger
total_points integer [not null, default: "0"]
round_id integer [not null, ref: > round.id]
team_id integer [not null, ref: > team.id]
}