Groups | Search | Server Info | Login | Register

Groups > no.it.diverse > #7

mwb to .env

Show all headers | View raw

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

-- -----------------------------------------------------
-- Table `mydb`.`address`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`address` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_line` VARCHAR(45) NOT NULL,
  `second_line` VARCHAR(45) NOT NULL,
  `street` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`profile_img`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`profile_img` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`country`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`country` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`city`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`city` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`customer` (
  `passport_number` INT NOT NULL,
  `nic` VARCHAR(45) NOT NULL,
  `first_name` VARCHAR(45) NULL,
  `birthday` DATE NULL,
  `address_id` INT NOT NULL,
  `profile_img_id` INT NOT NULL,
  `country_id` INT NOT NULL,
  `city_id` INT NOT NULL,
  PRIMARY KEY (`passport_number`),
  INDEX `fk_customer_address1_idx` (`address_id` ASC) VISIBLE,
  INDEX `fk_customer_profile_img1_idx` (`profile_img_id` ASC) VISIBLE,
  INDEX `fk_customer_country1_idx` (`country_id` ASC) VISIBLE,
  INDEX `fk_customer_city1_idx` (`city_id` ASC) VISIBLE,
  CONSTRAINT `fk_customer_address1`
    FOREIGN KEY (`address_id`)
    REFERENCES `mydb`.`address` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_customer_profile_img1`
    FOREIGN KEY (`profile_img_id`)
    REFERENCES `mydb`.`profile_img` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_customer_country1`
    FOREIGN KEY (`country_id`)
    REFERENCES `mydb`.`country` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_customer_city1`
    FOREIGN KEY (`city_id`)
    REFERENCES `mydb`.`city` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`contact_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`contact_details` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mobile_number` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `whatsapp_number` VARCHAR(45) NOT NULL,
  `customer_passport_number` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_contact_details_customer_idx` (`customer_passport_number` ASC) VISIBLE,
  CONSTRAINT `fk_contact_details_customer`
    FOREIGN KEY (`customer_passport_number`)
    REFERENCES `mydb`.`customer` (`passport_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`location` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `google_location_code` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`visiting_place`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`visiting_place` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `country_id` INT NOT NULL,
  `location_id` INT NOT NULL,
  `city_id` INT NOT NULL,
  `address_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_visiting_place_country1_idx` (`country_id` ASC) VISIBLE,
  INDEX `fk_visiting_place_location1_idx` (`location_id` ASC) VISIBLE,
  INDEX `fk_visiting_place_city1_idx` (`city_id` ASC) VISIBLE,
  INDEX `fk_visiting_place_address1_idx` (`address_id` ASC) VISIBLE,
  CONSTRAINT `fk_visiting_place_country1`
    FOREIGN KEY (`country_id`)
    REFERENCES `mydb`.`country` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_visiting_place_location1`
    FOREIGN KEY (`location_id`)
    REFERENCES `mydb`.`location` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_visiting_place_city1`
    FOREIGN KEY (`city_id`)
    REFERENCES `mydb`.`city` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_visiting_place_address1`
    FOREIGN KEY (`address_id`)
    REFERENCES `mydb`.`address` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.` package`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.` package` (
  `id` INT NOT NULL AUTO_INCREMENT,
  ` package_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Price`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Price` (
  `id` INT NOT NULL AUTO_INCREMENT,
  ` price` VARCHAR(45) NOT NULL,
  ` package_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Price_ package1_idx` (` package_id` ASC) VISIBLE,
  CONSTRAINT `fk_Price_ package1`
    FOREIGN KEY (` package_id`)
    REFERENCES `mydb`.` package` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`travel_expenses`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`travel_expenses` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `price` VARCHAR(45) NOT NULL,
  `From this country` INT NOT NULL,
  `to this country` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_travel_expenses_country1_idx` (`From this country` ASC) VISIBLE,
  INDEX `fk_travel_expenses_country2_idx` (`to this country` ASC) VISIBLE,
  CONSTRAINT `fk_travel_expenses_country1`
    FOREIGN KEY (`From this country`)
    REFERENCES `mydb`.`country` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_travel_expenses_country2`
    FOREIGN KEY (`to this country`)
    REFERENCES `mydb`.`country` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Cost_within_the_country.`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Cost_within_the_country.` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `price` VARCHAR(45) NOT NULL,
  `from_place` INT NOT NULL,
  `to_place` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Cost_within_the_country._visiting_place1_idx` (`from_place` ASC) VISIBLE,
  INDEX `fk_Cost_within_the_country._visiting_place2_idx` (`to_place` ASC) VISIBLE,
  CONSTRAINT `fk_Cost_within_the_country._visiting_place1`
    FOREIGN KEY (`from_place`)
    REFERENCES `mydb`.`visiting_place` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Cost_within_the_country._visiting_place2`
    FOREIGN KEY (`to_place`)
    REFERENCES `mydb`.`visiting_place` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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


-- -----------------------------------------------------
-- Table `mydb`.`Skill_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Skill_types` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`skils_to_price`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`skils_to_price` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `count` VARCHAR(45) NOT NULL,
  `price` VARCHAR(45) NOT NULL,
  `customer_passport_number` INT NOT NULL,
  `skills_id` INT NOT NULL,
  ` package_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_skils_to_price_customer1_idx` (`customer_passport_number` ASC) VISIBLE,
  INDEX `fk_skils_to_price_skills1_idx` (`skills_id` ASC) VISIBLE,
  INDEX `fk_skils_to_price_ package1_idx` (` package_id` ASC) VISIBLE,
  CONSTRAINT `fk_skils_to_price_customer1`
    FOREIGN KEY (`customer_passport_number`)
    REFERENCES `mydb`.`customer` (`passport_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_skils_to_price_skills1`
    FOREIGN KEY (`skills_id`)
    REFERENCES `mydb`.`Skill_types` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_skils_to_price_ package1`
    FOREIGN KEY (` package_id`)
    REFERENCES `mydb`.` package` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`adming`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`adming` (
  `nic` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NULL,
  `last_name` VARCHAR(45) NULL,
  `passport_num` VARCHAR(45) NULL,
  `address_id` INT NOT NULL,
  `profile_img_id` INT NOT NULL,
  `contact_details_id` INT NOT NULL,
  `country_id` INT NOT NULL,
  PRIMARY KEY (`nic`, `profile_img_id`),
  INDEX `fk_adming_address1_idx` (`address_id` ASC) VISIBLE,
  INDEX `fk_adming_profile_img1_idx` (`profile_img_id` ASC) VISIBLE,
  INDEX `fk_adming_contact_details1_idx` (`contact_details_id` ASC) VISIBLE,
  INDEX `fk_adming_country1_idx` (`country_id` ASC) VISIBLE,
  CONSTRAINT `fk_adming_address1`
    FOREIGN KEY (`address_id`)
    REFERENCES `mydb`.`address` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_adming_profile_img1`
    FOREIGN KEY (`profile_img_id`)
    REFERENCES `mydb`.`profile_img` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_adming_contact_details1`
    FOREIGN KEY (`contact_details_id`)
    REFERENCES `mydb`.`contact_details` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_adming_country1`
    FOREIGN KEY (`country_id`)
    REFERENCES `mydb`.`country` (`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

Back to no.it.diverse | Previous | Find similar

Thread

mwb to .env Sahan Taraka jayasundara <sahantharakasahan500@gmail.com> - 2023-12-10 11:17 -0800

csiph-web