phonelist.sql

-- MySQL Script generated by MySQL Workbench
-- ma 18 aug 2014 18:25:58 CEST
-- Model: New Model    Version: 1.0
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
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `phonelist`.`phone`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `phonelist`.`phone` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `internal_id` INT NOT NULL,
  `price` INT NOT NULL,
  `brand` VARCHAR(45) NOT NULL,
  `title` VARCHAR(45) NOT NULL,
  `color` VARCHAR(45) NOT NULL,
  `ean` VARCHAR(13) NOT NULL,
  `phonecol` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `internal_id_UNIQUE` (`internal_id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `phonelist`.`image`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `phonelist`.`image` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `phone_id` INT NOT NULL,
  `url` VARCHAR(255) NOT NULL,
  INDEX `fk_image_phone_idx` (`phone_id` ASC),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_image_phone`
    FOREIGN KEY (`phone_id`)
    REFERENCES `phonelist`.`phone` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `phonelist`.`feature`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `phonelist`.`feature` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `category_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `category_name_UNIQUE` (`category_name` ASC, `name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `phonelist`.`feature_has_phone`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `phonelist`.`feature_has_phone` (
  `feature_id` INT NOT NULL,
  `phone_id` INT NOT NULL,
  `value` VARCHAR(255) NULL,
  `bool_value` TINYINT NULL,
  `float_value` FLOAT NULL,
  PRIMARY KEY (`feature_id`, `phone_id`),
  INDEX `fk_feature_has_phone_phone1_idx` (`phone_id` ASC),
  INDEX `fk_feature_has_phone_feature1_idx` (`feature_id` ASC),
  CONSTRAINT `fk_feature_has_phone_feature1`
    FOREIGN KEY (`feature_id`)
    REFERENCES `phonelist`.`feature` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_feature_has_phone_phone1`
    FOREIGN KEY (`phone_id`)
    REFERENCES `phonelist`.`phone` (`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;