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';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`car_makes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`car_makes` (
  `car_make_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`car_make_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`car_models`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`car_models` (
  `car_model_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `car_make_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(45) NULL,
  PRIMARY KEY (`car_model_id`),
  UNIQUE INDEX `car_model_id_UNIQUE` (`car_model_id` ASC),
  INDEX `car_make_ref_idx` (`car_make_id` ASC),
  CONSTRAINT `car_make_ref`
    FOREIGN KEY (`car_make_id`)
    REFERENCES `mydb`.`car_makes` (`car_make_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`properties`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`properties` (
  `property_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NULL,
  PRIMARY KEY (`property_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`posts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`posts` (
  `post_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `car_make_id` INT UNSIGNED NOT NULL,
  `car_model_id` INT UNSIGNED NOT NULL,
  `description` TEXT NULL,
  PRIMARY KEY (`post_id`),
  INDEX `car_model_identifier_idx` (`car_model_id` ASC),
  CONSTRAINT `car_make_identifier`
    FOREIGN KEY (`car_model_id`)
    REFERENCES `mydb`.`car_makes` (`car_make_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `car_model_identifier`
    FOREIGN KEY (`car_model_id`)
    REFERENCES `mydb`.`car_models` (`car_model_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`properties_posts_rel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`properties_posts_rel` (
  `idproperties_posts_rel` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id` INT UNSIGNED NOT NULL,
  `property_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`idproperties_posts_rel`),
  INDEX `rel_to_post_idx` (`post_id` ASC),
  INDEX `rel_to_property_idx` (`property_id` ASC),
  CONSTRAINT `rel_to_post`
    FOREIGN KEY (`post_id`)
    REFERENCES `mydb`.`posts` (`post_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `rel_to_property`
    FOREIGN KEY (`property_id`)
    REFERENCES `mydb`.`properties` (`property_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;