25 lines
849 B
SQL
25 lines
849 B
SQL
CREATE DATABASE IF NOT EXISTS eaglertiers;
|
|
USE eaglertiers;
|
|
|
|
CREATE TABLE IF NOT EXISTS players (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(32) NOT NULL UNIQUE,
|
|
region ENUM('NA','EU','AS','AU') NOT NULL DEFAULT 'NA',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS player_ranks (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
player_id INT NOT NULL,
|
|
gamemode VARCHAR(32) NOT NULL,
|
|
tier ENUM('HT1','LT1','HT2','LT2','HT3','LT3','HT4','LT4','HT5','LT5') NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY unique_player_gamemode (player_id, gamemode),
|
|
CONSTRAINT fk_player_ranks_player
|
|
FOREIGN KEY (player_id)
|
|
REFERENCES players(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_player_ranks_gamemode ON player_ranks (gamemode);
|