Scouting.sql

-- MySQL Script generated by MySQL Workbench
-- 01/14/16 20:45:05
-- 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 scouting
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `scouting` ;

-- -----------------------------------------------------
-- Schema scouting
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `scouting` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `scouting` ;

-- -----------------------------------------------------
-- Table `scouting`.`DATATYPE`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `scouting`.`DATATYPE` ;

CREATE TABLE IF NOT EXISTS `scouting`.`DATATYPE` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `Type` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

CREATE UNIQUE INDEX `ID_UNIQUE` ON `scouting`.`DATATYPE` (`ID` ASC);


-- -----------------------------------------------------
-- Table `scouting`.`ITEM`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `scouting`.`ITEM` ;

CREATE TABLE IF NOT EXISTS `scouting`.`ITEM` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NOT NULL,
  `Active` BOOL NOT NULL DEFAULT 1,
  `DATATYPE_ID` INT NOT NULL,
  PRIMARY KEY (`ID`, `DATATYPE_ID`),
  CONSTRAINT `fk_ITEM_DATATYPE`
    FOREIGN KEY (`DATATYPE_ID`)
    REFERENCES `scouting`.`DATATYPE` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE UNIQUE INDEX `ID_UNIQUE` ON `scouting`.`ITEM` (`ID` ASC);

CREATE INDEX `fk_ITEM_DATATYPE_idx` ON `scouting`.`ITEM` (`DATATYPE_ID` ASC);

-- -----------------------------------------------------
-- Table `scouting`.`REPORT`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `scouting`.`REPORT` ;

CREATE TABLE IF NOT EXISTS `scouting`.`REPORT` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `MatchNum` INT(11) NULL,
  `TeamNum` INT(11) NOT NULL,
  `TabletNum` INT(11) NOT NULL,
  `ScoutName` VARCHAR(50) NOT NULL,
  `FormType` BOOL NOT NULL DEFAULT 1,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;

CREATE UNIQUE INDEX `ID_UNIQUE` ON `scouting`.`REPORT` (`ID` ASC)  ;

-- -----------------------------------------------------
-- Table `scouting`.`RECORD`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `scouting`.`RECORD` ;

CREATE TABLE IF NOT EXISTS `scouting`.`RECORD` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `Value` VARCHAR(500) NULL,
   `ITEM_ID` INT(11) NOT NULL,
   `REPORT_ID` INT(11) NOT NULL,
 PRIMARY KEY (`ID`, `ITEM_ID`, `REPORT_ID`),
  CONSTRAINT `fk_RECORD_ITEM1`
    FOREIGN KEY (`ITEM_ID`)
    REFERENCES `scouting`.`ITEM` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_RECORD_REPORT1`
    FOREIGN KEY (`REPORT_ID`)
    REFERENCES `scouting`.`REPORT` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE UNIQUE INDEX `ID_UNIQUE` ON `scouting`.`RECORD` (`ID` ASC)  ;

CREATE INDEX `fk_RECORD_ITEM1_idx` ON `scouting`.`RECORD` (`ITEM_ID` ASC)  ;
CREATE INDEX `fk_RECORD_REPORT1_idx` ON `scouting`.`RECORD` (`REPORT_ID` ASC)  ;

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

insert datatype(Type) values ('integer');
insert datatype(Type) values ('boolean');
insert datatype(Type) values ('string');