Schema SQL

[Alexandrehebertvincent]

[Alexandrehebertvincent]

Version MySQL

-- MySQL Script generated by MySQL Workbench
-- Wed Jan 11 15:54:26 2017
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema tsap_acquisition
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `tsap_acquisition` ;

-- -----------------------------------------------------
-- Schema tsap_acquisition
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `tsap_acquisition` DEFAULT CHARACTER SET utf8 ;
USE `tsap_acquisition` ;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`saisons`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`saisons` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`saisons` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `annees` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`sports`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`sports` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`sports` (
  `id` VARCHAR(3) NOT NULL,
  `nom` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`niveaux`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`niveaux` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`niveaux` (
  `int` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(45) NULL,
  PRIMARY KEY (`int`),
  UNIQUE INDEX `int_UNIQUE` (`int` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`equipes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`equipes` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`equipes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(45) NULL,
  `ville` VARCHAR(45) NULL,
  `sport_id` VARCHAR(3) NULL,
  `niveau_id` INT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_sport_equipe_idx` (`sport_id` ASC),
  INDEX `fk_equipe_niveau_idx` (`niveau_id` ASC),
  CONSTRAINT `fk_sport_equipe`
    FOREIGN KEY (`sport_id`)
    REFERENCES `tsap_acquisition`.`sports` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_equipe_niveau`
    FOREIGN KEY (`niveau_id`)
    REFERENCES `tsap_acquisition`.`niveaux` (`int`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`joueurs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`joueurs` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`joueurs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `numero` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`lieux`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`lieux` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`lieux` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(45) NOT NULL,
  `ville` VARCHAR(45) NOT NULL,
  `adresse` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`parties`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`parties` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`parties` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `equipe_maison` INT UNSIGNED NOT NULL,
  `equipe_adverse` INT UNSIGNED NOT NULL,
  `saison_id` INT UNSIGNED NOT NULL,
  `lieux` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_partie_equipe_maison_idx` (`equipe_maison` ASC),
  INDEX `fk_partie_equipe_adverse_idx` (`equipe_adverse` ASC),
  INDEX `fk_partie_equipe_lieux_idx` (`lieux` ASC),
  INDEX `fk_partie_equipe_saison_idx` (`saison_id` ASC),
  CONSTRAINT `fk_partie_equipe_maison`
    FOREIGN KEY (`equipe_maison`)
    REFERENCES `tsap_acquisition`.`equipes` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_partie_equipe_adverse`
    FOREIGN KEY (`equipe_adverse`)
    REFERENCES `tsap_acquisition`.`equipes` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_partie_equipe_lieux`
    FOREIGN KEY (`lieux`)
    REFERENCES `tsap_acquisition`.`lieux` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_partie_equipe_saison`
    FOREIGN KEY (`saison_id`)
    REFERENCES `tsap_acquisition`.`saisons` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`zones`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`zones` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`zones` (
  `id` VARCHAR(3) NOT NULL,
  `nom` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`type_actions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`type_actions` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`type_actions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`actions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`actions` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`actions` (
  `id` INT UNSIGNED NOT NULL,
  `type_action` INT UNSIGNED NOT NULL,
  `action_valide` INT(1) UNSIGNED NOT NULL,
  `zone` VARCHAR(3) NOT NULL,
  `partie` INT UNSIGNED NOT NULL,
  `x` INT NULL,
  `y` INT NULL,
  `temps` TIMESTAMP NOT NULL,
  `pointage_maison` INT NOT NULL DEFAULT 0,
  `pointage_adverse` INT NOT NULL DEFAULT 0,
  `joueur_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_action_position_idx` (`zone` ASC),
  INDEX `fk_action_nom_idx` (`type_action` ASC),
  INDEX `fk_action_partie_idx` (`partie` ASC),
  INDEX `fk_action_joueur_idx` (`joueur_id` ASC),
  CONSTRAINT `fk_action_position`
    FOREIGN KEY (`zone`)
    REFERENCES `tsap_acquisition`.`zones` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_action_nom`
    FOREIGN KEY (`type_action`)
    REFERENCES `tsap_acquisition`.`type_actions` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_action_partie`
    FOREIGN KEY (`partie`)
    REFERENCES `tsap_acquisition`.`parties` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_action_joueur`
    FOREIGN KEY (`joueur_id`)
    REFERENCES `tsap_acquisition`.`joueurs` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`joueurs_equipes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`joueurs_equipes` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`joueurs_equipes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `joueur` INT UNSIGNED NOT NULL,
  `equipe` INT UNSIGNED NOT NULL,
  `creation` TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP',
  `update` TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_joueur_equipe_idx` (`joueur` ASC),
  INDEX `fk_equipe_joueur_idx` (`equipe` ASC),
  CONSTRAINT `fk_joueur_equipe`
    FOREIGN KEY (`joueur`)
    REFERENCES `tsap_acquisition`.`joueurs` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_equipe_joueur`
    FOREIGN KEY (`equipe`)
    REFERENCES `tsap_acquisition`.`equipes` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`entraineurs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`entraineurs` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`entraineurs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `prenom` VARCHAR(45) NOT NULL,
  `nom` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `pass_hash` VARCHAR(256) NULL,
  `token` VARCHAR(256) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tsap_acquisition`.`entraineur_equipes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tsap_acquisition`.`entraineur_equipes` ;

CREATE TABLE IF NOT EXISTS `tsap_acquisition`.`entraineur_equipes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entraineur` INT UNSIGNED NOT NULL,
  `equipe` INT UNSIGNED NOT NULL,
  `creation` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_mandat_entraineur_idx` (`entraineur` ASC),
  INDEX `fk_mandat_equipe_idx` (`equipe` ASC),
  CONSTRAINT `fk_mandat_entraineur`
    FOREIGN KEY (`entraineur`)
    REFERENCES `tsap_acquisition`.`entraineurs` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_mandat_equipe`
    FOREIGN KEY (`equipe`)
    REFERENCES `tsap_acquisition`.`equipes` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[laurentlp]

DROP TABLE IF EXISTS "admins" CASCADE;
DROP TABLE IF EXISTS "actions" CASCADE;
DROP TABLE IF EXISTS "games" CASCADE;
DROP TABLE IF EXISTS "teams" CASCADE;
DROP TABLE IF EXISTS "player_team" CASCADE;
DROP TABLE IF EXISTS "coach_team" CASCADE;
DROP TABLE IF EXISTS "zones" CASCADE;
DROP TABLE IF EXISTS "sports" CASCADE;
DROP TABLE IF EXISTS "players" CASCADE;
DROP TABLE IF EXISTS "locations" CASCADE;
DROP TABLE IF EXISTS "categories" CASCADE;
DROP TABLE IF EXISTS "coaches" CASCADE;
DROP TABLE IF EXISTS "actions_type" CASCADE;
DROP TABLE IF EXISTS "seasons" CASCADE;
DROP TABLE IF EXISTS "positions" CASCADE;
DROP TABLE IF EXISTS "movements_type" CASCADE;
DROP TABLE IF EXISTS "player_position_game_team" CASCADE;
DROP TABLE IF EXISTS "videos" CASCADE;
DROP TABLE IF EXISTS "metrics" CASCADE;

-- -----------------------------------------------------
-- Table "admins"
-- -----------------------------------------------------

CREATE TABLE "admins" (
  "id" SERIAL PRIMARY KEY,
  "email" VARCHAR(256) NOT NULL,
  "pass_hash" VARCHAR(256) NOT NULL,
  "token_reset" VARCHAR(256) NULL,
  "token_login" VARCHAR(256) NULL);

-- -----------------------------------------------------
-- Table "seasons"
-- -----------------------------------------------------

CREATE TABLE "seasons" (
  "id" SERIAL PRIMARY KEY,
  "years" VARCHAR(10) NOT NULL);

-- -----------------------------------------------------
-- Table "sports"
-- -----------------------------------------------------

CREATE TABLE "sports" (
  "id" VARCHAR(3) PRIMARY KEY,
  "name" VARCHAR(256) NOT NULL);

-- -----------------------------------------------------
-- Table "categories"
-- -----------------------------------------------------

CREATE TABLE "categories" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(256) NULL);

-- -----------------------------------------------------
-- Table "teams"
-- -----------------------------------------------------

CREATE TABLE "teams" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(256) NULL,
  "city" VARCHAR(256) NULL,
  "id_sport" VARCHAR(3) NULL,
  "id_category" INT NULL,
  CONSTRAINT "fk_sport_team"
    FOREIGN KEY ("id_sport")
    REFERENCES "sports" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_team_category"
    FOREIGN KEY ("id_category")
    REFERENCES "categories" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "players"
-- -----------------------------------------------------

CREATE TABLE "players" (
  "id" SERIAL PRIMARY KEY,
  "number" INT NOT NULL,
  "email" VARCHAR(256) NULL,
  "fname" VARCHAR(256) NULL,
  "lname" VARCHAR(256) NULL,
  "pass_hash" VARCHAR(256) NULL,
  "token_request" VARCHAR(256) NULL,
  "token_reset" VARCHAR(256) NULL,
  "token_connection" VARCHAR(256) NULL);

-- -----------------------------------------------------
-- Table "locations"
-- -----------------------------------------------------

CREATE TABLE "locations" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(256) NOT NULL,
  "city" VARCHAR(256) NOT NULL,
  "adress" VARCHAR(256) NULL);

-- -----------------------------------------------------
-- Table "video"
-- -----------------------------------------------------

CREATE TABLE "videos" (
  "id" SERIAL PRIMARY KEY,
  "path" TEXT NOT NULL,
  "completed" INT NOT NULL DEFAULT 0);

-- -----------------------------------------------------
-- Table "games"
-- -----------------------------------------------------

CREATE TABLE "games" (
  "id" SERIAL PRIMARY KEY,
  "id_home_team" INT NOT NULL,
  "id_opposing_team" INT NOT NULL,
  "id_season" INT NOT NULL,
  "id_location" INT NOT NULL,
  "id_video" INT NOT NULL,
  "date" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "fk_game_home_team"
    FOREIGN KEY ("id_home_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_game_opposing_team"
    FOREIGN KEY ("id_opposing_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_game_team_location"
    FOREIGN KEY ("id_location")
    REFERENCES "locations" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_game_team_season"
    FOREIGN KEY ("id_season")
    REFERENCES "seasons" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_game_team_video"
    FOREIGN KEY ("id_video")
    REFERENCES "videos" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "positions"
-- -----------------------------------------------------

CREATE TABLE "positions" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(45) NOT NULL);

-- -----------------------------------------------------
-- Table "player_position_game_team"
-- -----------------------------------------------------

CREATE TABLE "player_position_game_team" (
  "id" SERIAL PRIMARY KEY,
  "id_player" INT NOT NULL,
  "id_game" INT NOT NULL,
  "id_position" INT NOT NULL,
  "id_team" INT NOT NULL,
  CONSTRAINT "fk_player_position"
    FOREIGN KEY ("id_player")
    REFERENCES "players" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_game"
    FOREIGN KEY ("id_game")
    REFERENCES "games" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_position"
    FOREIGN KEY ("id_position")
    REFERENCES "positions" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_team"
    FOREIGN KEY ("id_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "zones"
-- -----------------------------------------------------

CREATE TABLE "zones" (
  "id" VARCHAR(3) PRIMARY KEY,
  "name" VARCHAR(45) NOT NULL);

-- -----------------------------------------------------
-- Table "movements_type"
-- -----------------------------------------------------

CREATE TABLE "movements_type" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(10) NOT NULL);

-- -----------------------------------------------------
-- Table "actions_type"
-- -----------------------------------------------------

CREATE TABLE "actions_type" (
  "id" VARCHAR(5) PRIMARY KEY,
  "name" VARCHAR(256) NOT NULL,
  "description" TEXT NULL,
  "id_movement_type" INT NOT NULL,
  CONSTRAINT "fk_movement_type"
    FOREIGN KEY ("id_movement_type")
    REFERENCES "movements_type" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "actions"
-- -----------------------------------------------------

CREATE TABLE "actions" (
  "id" SERIAL PRIMARY KEY ,
  "id_action_type" VARCHAR(5) NOT NULL,
  "id_player" INT  NOT NULL,
  "id_zone" VARCHAR(3) NOT NULL,
  "id_game" INT NOT NULL,
  "is_positive" INT NOT NULL,
  "x1" FLOAT NOT NULL,
  "y1" FLOAT NOT NULL,
  "x2" FLOAT NOT NULL,
  "y2" FLOAT NOT NULL,
  "time" TIMESTAMP NOT NULL,
  "home_score" INT NOT NULL DEFAULT 0,
  "guest_score" INT NOT NULL DEFAULT 0,
  CONSTRAINT "fk_action_position"
    FOREIGN KEY ("id_zone")
    REFERENCES "zones" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_action_name"
    FOREIGN KEY ("id_action_type")
    REFERENCES "actions_type" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_action_game"
    FOREIGN KEY ("id_game")
    REFERENCES "games" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_action_player"
    FOREIGN KEY ("id_player")
    REFERENCES "players" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "player_team"
-- -----------------------------------------------------

CREATE TABLE "player_team" (
  "id" SERIAL PRIMARY KEY ,
  "id_player" INT NOT NULL,
  "id_team" INT NOT NULL,
  "created" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  "updated" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "fk_player"
    FOREIGN KEY ("id_player")
    REFERENCES "players" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_team"
    FOREIGN KEY ("id_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "coaches"
-- -----------------------------------------------------

CREATE TABLE "coaches" (
  "id" SERIAL PRIMARY KEY ,
  "fname" VARCHAR(256) NOT NULL,
  "lname" VARCHAR(256) NOT NULL,
  "email" VARCHAR(256) NULL,
  "pass_hash" VARCHAR(256) NULL,
  "token_request" VARCHAR(256) NULL,
  "token_reset" VARCHAR(256) NULL,
  "token_connection" VARCHAR(256) NULL);

-- -----------------------------------------------------
-- Table "coach_team"
-- -----------------------------------------------------

CREATE TABLE "coach_team" (
  "id" SERIAL PRIMARY KEY ,
  "id_coach" INT NOT NULL,
  "id_team" INT NOT NULL,
  "created" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "fk_mandat_coach"
    FOREIGN KEY ("id_coach")
    REFERENCES "coaches" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_mandat_team"
    FOREIGN KEY ("id_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "metrics"
-- -----------------------------------------------------

CREATE TABLE "metrics" (
  "name" VARCHAR(256) NOT NULL,
  "equation" TEXT NOT NULL,
  "id_team" INT NOT NULL,
  PRIMARY KEY ("name", "equation"),
  CONSTRAINT "fk_metric_team"
    FOREIGN KEY ("id_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

[ghost]

Quelques questions/suggestions pour le modèle:

Dans la table 'Saison', pourquoi pas avoir des champs distincts pour les années de début et fin? Est-ce qu'une équipe fait partie d'une ligue? Si oui, on devra modifier le schéma en conséquence. Devrions-nous répartir les points accumulés lors d'une partie par période?

[laurentlp]

DROP TABLE IF EXISTS "admins" CASCADE;
DROP TABLE IF EXISTS "actions" CASCADE;
DROP TABLE IF EXISTS "videos" CASCADE;
DROP TABLE IF EXISTS "player_position_game_team" CASCADE;
DROP TABLE IF EXISTS "games" CASCADE;
DROP TABLE IF EXISTS "player_team" CASCADE;
DROP TABLE IF EXISTS "coach_team" CASCADE;
DROP TABLE IF EXISTS "metrics" CASCADE;
DROP TABLE IF EXISTS "teams" CASCADE;
DROP TABLE IF EXISTS "zones" CASCADE;
DROP TABLE IF EXISTS "sports" CASCADE;
DROP TABLE IF EXISTS "players" CASCADE;
DROP TABLE IF EXISTS "locations" CASCADE;
DROP TABLE IF EXISTS "field_types" CASCADE;
DROP TABLE IF EXISTS "categories" CASCADE;
DROP TABLE IF EXISTS "coaches" CASCADE;
DROP TABLE IF EXISTS "actions_type" CASCADE;
DROP TABLE IF EXISTS "seasons" CASCADE;
DROP TABLE IF EXISTS "positions" CASCADE;
DROP TABLE IF EXISTS "movements_type" CASCADE;

-- -----------------------------------------------------
-- Table "admins"
-- -----------------------------------------------------

CREATE TABLE "admins" (
  "id" SERIAL PRIMARY KEY,
  "email" VARCHAR(256) NOT NULL,
  "pass_hash" VARCHAR(256) NOT NULL,
  "token_reset" VARCHAR(256) NULL,
  "token_login" VARCHAR(256) NULL);

-- -----------------------------------------------------
-- Table "seasons"
-- -----------------------------------------------------

CREATE TABLE "seasons" (
  "id" SERIAL PRIMARY KEY,
  "years" VARCHAR(10) NOT NULL);

-- -----------------------------------------------------
-- Table "sports"
-- -----------------------------------------------------

CREATE TABLE "sports" (
  "id" VARCHAR(3) PRIMARY KEY,
  "name" VARCHAR(256) NOT NULL);

-- -----------------------------------------------------
-- Table "categories"
-- -----------------------------------------------------

CREATE TABLE "categories" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(256) NULL);

-- -----------------------------------------------------
-- Table "teams"
-- -----------------------------------------------------

CREATE TABLE "teams" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(256) NULL,
  "city" VARCHAR(256) NULL,
  "id_sport" VARCHAR(3) NULL,
  "id_category" INT NULL,
  CONSTRAINT "fk_sport_team"
    FOREIGN KEY ("id_sport")
    REFERENCES "sports" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_team_category"
    FOREIGN KEY ("id_category")
    REFERENCES "categories" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "players"
-- -----------------------------------------------------

CREATE TABLE "players" (
  "id" SERIAL PRIMARY KEY,
  "number" INT NOT NULL,
  "email" VARCHAR(256) NULL,
  "fname" VARCHAR(256) NULL,
  "lname" VARCHAR(256) NULL,
  "pass_hash" VARCHAR(256) NULL,
  "token_request" VARCHAR(256) NULL,
  "token_reset" VARCHAR(256) NULL,
  "token_login" VARCHAR(256) NULL);

-- -----------------------------------------------------
-- Table "locations"
-- -----------------------------------------------------

CREATE TABLE "locations" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(256) NOT NULL,
  "city" VARCHAR(256) NOT NULL,
  "address" VARCHAR(256) NULL,
  "inside_outside" VARCHAR(256) NOT NULL);

-- -----------------------------------------------------
-- Table "field_types"
-- -----------------------------------------------------

CREATE TABLE "field_types" (
  "id" SERIAL PRIMARY KEY,
  "type" VARCHAR(256) NOT NULL,
  "description" VARCHAR(256) NULL);

-- -----------------------------------------------------
-- Table "games"
-- -----------------------------------------------------

CREATE TABLE "games" (
  "id" SERIAL PRIMARY KEY,
  "id_team" INT NOT NULL,
  "status" VARCHAR(50) NOT NULL,
  "opposing_team" VARCHAR(100) NOT NULL,
  "id_season" INT NOT NULL,
  "id_location" INT NOT NULL,
  "field_condition" VARCHAR(45) NULL,
  "temperature" VARCHAR(45) NULL,
  "degree" VARCHAR(10) NULL
  "date" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "fk_game_team"
    FOREIGN KEY ("id_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_game_team_location"
    FOREIGN KEY ("id_location")
    REFERENCES "locations" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_game_team_season"
    FOREIGN KEY ("id_season")
    REFERENCES "seasons" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "video"
-- -----------------------------------------------------

CREATE TABLE "videos" (
  "id" SERIAL PRIMARY KEY,
  "path" TEXT NOT NULL,
  "part" INT NOT NULL DEFAULT 1,
  "completed" INT NOT NULL DEFAULT 0,
  "id_game" INT NOT NULL,
  CONSTRAINT "fk_game_id"
    FOREIGN KEY ("id_game")
    REFERENCES "games" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "positions"
-- -----------------------------------------------------

CREATE TABLE "positions" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(45) NOT NULL);

-- -----------------------------------------------------
-- Table "player_position_game_team"
-- -----------------------------------------------------

CREATE TABLE "player_position_game_team" (
  "id" SERIAL PRIMARY KEY,
  "id_player" INT NOT NULL,
  "id_game" INT NOT NULL,
  "id_position" INT NOT NULL,
  "id_team" INT NOT NULL,
  CONSTRAINT "fk_player_position"
    FOREIGN KEY ("id_player")
    REFERENCES "players" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_game"
    FOREIGN KEY ("id_game")
    REFERENCES "games" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_position"
    FOREIGN KEY ("id_position")
    REFERENCES "positions" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_team"
    FOREIGN KEY ("id_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "zones"
-- -----------------------------------------------------

CREATE TABLE "zones" (
  "id" VARCHAR(3) PRIMARY KEY,
  "name" VARCHAR(45) NOT NULL);

-- -----------------------------------------------------
-- Table "movements_type"
-- -----------------------------------------------------

CREATE TABLE "movements_type" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(10) NOT NULL);

-- -----------------------------------------------------
-- Table "actions_type"
-- -----------------------------------------------------

CREATE TABLE "actions_type" (
  "id" VARCHAR(5) PRIMARY KEY,
  "name" VARCHAR(256) NOT NULL,
  "description" TEXT NULL,
  "id_movement_type" INT NOT NULL,
  CONSTRAINT "fk_movement_type"
    FOREIGN KEY ("id_movement_type")
    REFERENCES "movements_type" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "actions"
-- -----------------------------------------------------

CREATE TABLE "actions" (
  "id" SERIAL PRIMARY KEY ,
  "id_action_type" VARCHAR(5) NOT NULL,
  "id_player" INT  NOT NULL,
  "id_zone" VARCHAR(3) NOT NULL,
  "id_game" INT NOT NULL,
  "is_positive" INT NOT NULL,
  "x1" FLOAT NOT NULL,
  "y1" FLOAT NOT NULL,
  "x2" FLOAT NOT NULL,
  "y2" FLOAT NOT NULL,
  "time" TIMESTAMP NOT NULL,
  "home_score" INT NOT NULL DEFAULT 0,
  "guest_score" INT NOT NULL DEFAULT 0,
  CONSTRAINT "fk_action_position"
    FOREIGN KEY ("id_zone")
    REFERENCES "zones" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_action_name"
    FOREIGN KEY ("id_action_type")
    REFERENCES "actions_type" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_action_game"
    FOREIGN KEY ("id_game")
    REFERENCES "games" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_action_player"
    FOREIGN KEY ("id_player")
    REFERENCES "players" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "player_team"
-- -----------------------------------------------------

CREATE TABLE "player_team" (
  "id" SERIAL PRIMARY KEY ,
  "id_player" INT NOT NULL,
  "id_team" INT NOT NULL,
  CONSTRAINT "fk_player"
    FOREIGN KEY ("id_player")
    REFERENCES "players" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_team"
    FOREIGN KEY ("id_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "coaches"
-- -----------------------------------------------------

CREATE TABLE "coaches" (
  "id" SERIAL PRIMARY KEY ,
  "fname" VARCHAR(256) NOT NULL,
  "lname" VARCHAR(256) NOT NULL,
  "email" VARCHAR(256) NULL,
  "pass_hash" VARCHAR(256) NULL,
  "token_request" VARCHAR(256) NULL,
  "token_reset" VARCHAR(256) NULL,
  "token_login" VARCHAR(256) NULL);

-- -----------------------------------------------------
-- Table "coach_team"
-- -----------------------------------------------------

CREATE TABLE "coach_team" (
  "id" SERIAL PRIMARY KEY ,
  "id_coach" INT NOT NULL,
  "id_team" INT NOT NULL,
  CONSTRAINT "fk_mandat_coach"
    FOREIGN KEY ("id_coach")
    REFERENCES "coaches" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_mandat_team"
    FOREIGN KEY ("id_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table "metrics"
-- -----------------------------------------------------

CREATE TABLE "metrics" (
  "name" VARCHAR(256) NOT NULL,
  "equation" TEXT NOT NULL,
  "id_team" INT NOT NULL,
  PRIMARY KEY ("name", "equation"),
  CONSTRAINT "fk_metric_team"
    FOREIGN KEY ("id_team")
    REFERENCES "teams" ("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE);