mybd.sql - BACKEND - valieva/project

mybd.sql
174 строки5 KB
valieva
upload files
20 июн 2025, 13:04
20 июн 2025, 13:045463839
-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `mydb`.`category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`category` (
  `idcategory` INT NOT NULL,
  `idproduct` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`idcategory`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`characteristics`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`characteristics` (
  `idcharacteristics` INT NOT NULL,
  `product_id` INT NOT NULL,
  `key` VARCHAR(50) NOT NULL,
  `value` VARCHAR(100) NOT NULL,
  `unit` VARCHAR(20) NULL,
  PRIMARY KEY (`idcharacteristics`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`price`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`price` (
  `idprice` INT NOT NULL,
  `product_id` INT NOT NULL,
  `shop_id` INT NOT NULL,
  `value` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`idprice`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`leftovers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`leftovers` (
  `idleftovers` INT NOT NULL,
  `product_id` INT NOT NULL,
  `shop_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  `last_updated` VARCHAR(45) NULL,
  `leftoverscol` TIMESTAMP(3) NULL,
  PRIMARY KEY (`idleftovers`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`product` (
  `idproduct` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(140) NOT NULL,
  `price` INT NOT NULL,
  `category` VARCHAR(70) NOT NULL,
  `rating` INT NOT NULL,
  `stockquantity` INT NOT NULL,
  PRIMARY KEY (`idproduct`),
  CONSTRAINT `fk_product_category`
    FOREIGN KEY (`idproduct`)
    REFERENCES `mydb`.`category` (`idcategory`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_product_characteristics`
    FOREIGN KEY (`idproduct`)
    REFERENCES `mydb`.`characteristics` (`idcharacteristics`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_product_price`
    FOREIGN KEY (`idproduct`)
    REFERENCES `mydb`.`price` (`idprice`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_product_leftovers`
    FOREIGN KEY (`idproduct`)
    REFERENCES `mydb`.`leftovers` (`idleftovers`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`shop`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`shop` (
  `idshop` INT NOT NULL,
  `idproduct` INT NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `description` VARCHAR(50) NOT NULL,
  `raiting` INT NOT NULL,
  `contactinfo` VARCHAR(60) NOT NULL,
  PRIMARY KEY (`idshop`),
  CONSTRAINT `shop`
    FOREIGN KEY (`idproduct`)
    REFERENCES `mydb`.`product` (`idproduct`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`order` (
  `idorder` INT NOT NULL,
  `iduser` INT NOT NULL,
  `orderdate` DATE NOT NULL,
  `orderstatus` VARCHAR(30) NOT NULL,
  `totalamount` INT NOT NULL,
  PRIMARY KEY (`idorder`),
  CONSTRAINT `order`
    FOREIGN KEY (`idorder`)
    REFERENCES `mydb`.`shop` (`idshop`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`user` (
  `iduser` INT NOT NULL,
  `nickname` VARCHAR(15) NOT NULL,
  `mail` TEXT(100) NOT NULL,
  `password` TEXT(20) NOT NULL,
  `adress` VARCHAR(45) NOT NULL,
  `phonenumber` VARCHAR(11) NOT NULL,
  PRIMARY KEY (`iduser`),
  CONSTRAINT `user`
    FOREIGN KEY (`iduser`)
    REFERENCES `mydb`.`order` (`idorder`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`action`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`action` (
  `idaction` INT NOT NULL,
  `product_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `text` LONGTEXT NOT NULL,
  PRIMARY KEY (`idaction`))
ENGINE = InnoDB;


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