[FEATURE] Standings

[gbm25]

Feature Request

Description

To accommodate the new standings data coming from the LoLEsports API, we need to expand our database structure. This data includes additional details such as team scores, tournament splits, seasons, stages, and more. This requires the creation of new tables and modification of existing ones to ensure we can store and retrieve this data efficiently while avoiding data duplication.

Use Case

The use case for this feature is to store the new standings data coming from the LoLEsports API. This data is crucial for providing users with up-to-date information about team standings and match results. This will enhance the user experience by providing a more comprehensive view of the tournament progress.

Proposed Solution

To accommodate the new standings data, we need to create the following new tables:

• scores
• tournament_split
• season
• season_splits
• stages
• sections
• rankings
• teams_rankings
• rounds
• round_matches

In addition, it's necessary to add a new column, season_id, to the existing tournament table.

Each of these tables is designed to hold a specific piece of the standings data coming from the API. For instance, the scores table will hold the scores data, which includes team_id, split_id, position, and points. The tournament_split table will hold data about the splits in each tournament. The season table will hold data about the seasons, and so on.

By creating these new tables and adding a new column to the tournament table, we can store the new standings data in a structured and efficient manner. The use of foreign keys ensures data integrity and consistency across tables.

Here is the DDL for the new tables:

CREATE TABLE tournament_split (
id SERIAL PRIMARY KEY,
tournament_id INT NOT NULL,
split_id INT,
name VARCHAR(255),
FOREIGN KEY (tournament_id) REFERENCES tournament(id)
);

CREATE TABLE season (
id SERIAL PRIMARY KEY,
season_id INT,
name VARCHAR(255),
slug VARCHAR(255),
status VARCHAR(255),
start_time TIMESTAMP,
end_time TIMESTAMP
);

CREATE TABLE season_splits (
id SERIAL PRIMARY KEY,
season_id INT NOT NULL,
name VARCHAR(255),
slug VARCHAR(255),
start_time TIMESTAMP,
end_time TIMESTAMP,
FOREIGN KEY (season_id) REFERENCES season(id)
);

ALTER TABLE tournament
ADD COLUMN season_id INT,
ADD FOREIGN KEY (season_id) REFERENCES season(id);

CREATE TABLE stages (
id SERIAL PRIMARY KEY,
split_id INT NOT NULL,
name VARCHAR(255),
slug VARCHAR(255),
FOREIGN KEY (split_id) REFERENCES tournament_split(id)
);

CREATE TABLE sections (
id SERIAL PRIMARY KEY,
stage_id INT NOT NULL,
section_id INT,
name VARCHAR(255),
type VARCHAR(255),
FOREIGN KEY (stage_id) REFERENCES stages(id)
);

CREATE TABLE rounds (
id SERIAL PRIMARY KEY,
section_id INT NOT NULL,
round_order INT,
FOREIGN KEY (section_id) REFERENCES sections(id)
);

CREATE TABLE round_matches (
id SERIAL PRIMARY KEY,
round_id INT NOT NULL,
match_order INT,
name VARCHAR(255),
slug VARCHAR(255),
match_id INT NOT NULL,
FOREIGN KEY (round_id) REFERENCES rounds(id),
FOREIGN KEY (match_id) REFERENCES schedule(id)
);

CREATE TABLE rankings (
id SERIAL PRIMARY KEY,
section_id INT NOT NULL,
ordinal INT,
FOREIGN KEY (section_id) REFERENCES sections(id)
);

CREATE TABLE teams_rankings (
id SERIAL PRIMARY KEY,
ranking_id INT NOT NULL,
team_id INT NOT NULL,
wins INT,
ties INT,
losses INT,
FOREIGN KEY (ranking_id) REFERENCES rankings(id),
FOREIGN KEY (team_id) REFERENCES team(id)
);

CREATE TABLE scores (
id SERIAL PRIMARY KEY,
team_id INT NOT NULL,
split_id INT NOT NULL,
position INT,
points INT,
FOREIGN KEY (team_id) REFERENCES team(id),
FOREIGN KEY (split_id) REFERENCES tournament_split(id)
);

Additional Context

To see the an example of the JSOn, you can make a GET request to the getStandingV3 endpoint of the LoLEsports API.

The proposed solution aims to avoid data duplication. The new tables are designed to link to existing tables wherever possible. For instance, the scores table includes a foreign key to the team and tournament_split tables, ensuring that team and split data are not duplicated in the scores table.

Possible Alternatives

Possible alternatives to this proposed solution and their potential advantages and disadvantages could be discussed further.

Priority

The priority level for this feature request is high due to the importance of the standings data for users.