-- 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 mydb
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `mydb`.`types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`types` (
  `type_id` INT NOT NULL AUTO_INCREMENT,
  `type_name` VARCHAR(64) NOT NULL,
  `type_canonical` VARCHAR(64) NULL,
  PRIMARY KEY (`type_id`),
  UNIQUE INDEX `type_name_UNIQUE` (`type_name` ASC),
  UNIQUE INDEX `type_canonical_UNIQUE` (`type_canonical` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`attribute_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`attribute_types` (
  `attribute_type_id` INT NOT NULL,
  `attribute_type_name` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`attribute_type_id`),
  UNIQUE INDEX `attribute_type_name_UNIQUE` (`attribute_type_name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`attributes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`attributes` (
  `attribute_id` INT UNSIGNED NOT NULL,
  `types_type_id` INT UNSIGNED NOT NULL,
  `attribute_type` INT NOT NULL,
  `attribute_max_length` INT UNSIGNED NULL,
  PRIMARY KEY (`attribute_id`, `types_type_id`),
  INDEX `fk_attributes_types_idx` (`types_type_id` ASC),
  INDEX `fk_attributes_attribute_types1_idx` (`attribute_type` ASC),
  CONSTRAINT `fk_attributes_types`
    FOREIGN KEY (`types_type_id`)
    REFERENCES `mydb`.`types` (`type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_attributes_attribute_types1`
    FOREIGN KEY (`attribute_type`)
    REFERENCES `mydb`.`attribute_types` (`attribute_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`resources`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`resources` (
  `resource_id` INT NOT NULL,
  `resource_type` INT NOT NULL,
  `resource_name` VARCHAR(64) NULL,
  `recource_canonical` VARCHAR(64) NULL,
  PRIMARY KEY (`resource_id`),
  UNIQUE INDEX `recource_canonical_UNIQUE` (`recource_canonical` ASC),
  INDEX `fk_resources_types1_idx` (`resource_type` ASC),
  CONSTRAINT `fk_resources_types1`
    FOREIGN KEY (`resource_type`)
    REFERENCES `mydb`.`types` (`type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`resource_attributes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`resource_attributes` (
  `res_atr_id` INT UNSIGNED NOT NULL,
  `res_atr_resource` INT NOT NULL,
  `res_atr_value` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`res_atr_id`, `res_atr_resource`),
  INDEX `fk_resource_attributes_resources1_idx` (`res_atr_resource` ASC),
  INDEX `fk_resource_attributes_attributes1_idx` (`res_atr_id` ASC),
  CONSTRAINT `fk_resource_attributes_resources1`
    FOREIGN KEY (`res_atr_resource`)
    REFERENCES `mydb`.`resources` (`resource_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_resource_attributes_attributes1`
    FOREIGN KEY (`res_atr_id`)
    REFERENCES `mydb`.`attributes` (`attribute_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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