· 4 years ago · Apr 27, 2021, 04:30 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue Apr 27 19:22:20 2021
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @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';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`users`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`users` (
24 `id` INT NOT NULL AUTO_INCREMENT,
25 `fio` VARCHAR(255) NOT NULL,
26 `login` VARCHAR(255) NULL,
27 `password` VARCHAR(255) NULL,
28 `e_mail` VARCHAR(255) NULL,
29 `type` VARCHAR(45) NULL,
30 PRIMARY KEY (`id`, `fio`),
31 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
32 UNIQUE INDEX `login_UNIQUE` (`login` ASC) VISIBLE)
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `mydb`.`settings`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `mydb`.`settings` (
40 `id` INT NOT NULL AUTO_INCREMENT,
41 `host` VARCHAR(45) NULL,
42 `db` VARCHAR(45) NULL,
43 `user` VARCHAR(45) NULL,
44 `password` VARCHAR(45) NULL,
45 PRIMARY KEY (`id`),
46 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
47ENGINE = InnoDB;
48
49
50-- -----------------------------------------------------
51-- Table `mydb`.`Shops`
52-- -----------------------------------------------------
53CREATE TABLE IF NOT EXISTS `mydb`.`Shops` (
54 `id` INT NOT NULL AUTO_INCREMENT,
55 `name` VARCHAR(45) NULL,
56 `address` VARCHAR(45) NULL,
57 `tel` VARCHAR(45) NULL,
58 `site` VARCHAR(45) NULL,
59 `email` VARCHAR(45) NULL,
60 PRIMARY KEY (`id`),
61 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
62ENGINE = InnoDB;
63
64
65-- -----------------------------------------------------
66-- Table `mydb`.`product_type`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `mydb`.`product_type` (
69 `id` INT NOT NULL,
70 `name` VARCHAR(255) BINARY NULL,
71 PRIMARY KEY (`id`))
72ENGINE = InnoDB;
73
74
75-- -----------------------------------------------------
76-- Table `mydb`.`product`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `mydb`.`product` (
79 `id` INT NOT NULL AUTO_INCREMENT,
80 `shop_id` INT NOT NULL,
81 `type_id` INT NOT NULL,
82 `brand` VARCHAR(255) NULL,
83 `model` VARCHAR(255) NULL,
84 `data` TINYTEXT NULL,
85 `img` VARCHAR(255) NULL,
86 `price` VARCHAR(45) NULL,
87 `warrantly` VARCHAR(45) NULL,
88 PRIMARY KEY (`id`, `shop_id`, `type_id`),
89 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
90 INDEX `shop_idx` (`shop_id` ASC) VISIBLE,
91 INDEX `type_idx` (`type_id` ASC) VISIBLE,
92 CONSTRAINT `shop`
93 FOREIGN KEY (`shop_id`)
94 REFERENCES `mydb`.`Shops` (`id`)
95 ON DELETE CASCADE
96 ON UPDATE CASCADE,
97 CONSTRAINT `type`
98 FOREIGN KEY (`type_id`)
99 REFERENCES `mydb`.`product_type` (`id`)
100 ON DELETE NO ACTION
101 ON UPDATE NO ACTION)
102ENGINE = InnoDB;
103
104
105-- -----------------------------------------------------
106-- Table `mydb`.`orders`
107-- -----------------------------------------------------
108CREATE TABLE IF NOT EXISTS `mydb`.`orders` (
109 `id` INT NOT NULL AUTO_INCREMENT,
110 `shop_id` INT NOT NULL,
111 `product_id` INT NOT NULL,
112 `fio` INT NOT NULL,
113 `date` DATE NULL,
114 `quantity` TINYINT NULL,
115 `tel` VARCHAR(100) NULL,
116 `confirm` TINYINT(1) NULL,
117 PRIMARY KEY (`id`, `shop_id`, `product_id`, `fio`),
118 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
119 INDEX `shop_idx` (`shop_id` ASC) VISIBLE,
120 INDEX `prod_idx` (`product_id` ASC) VISIBLE,
121 INDEX `fio_idx` (`fio` ASC) VISIBLE,
122 CONSTRAINT `shop`
123 FOREIGN KEY (`shop_id`)
124 REFERENCES `mydb`.`Shops` (`id`)
125 ON DELETE NO ACTION
126 ON UPDATE NO ACTION,
127 CONSTRAINT `prod`
128 FOREIGN KEY (`product_id`)
129 REFERENCES `mydb`.`product_type` (`id`)
130 ON DELETE NO ACTION
131 ON UPDATE NO ACTION,
132 CONSTRAINT `fio`
133 FOREIGN KEY (`fio`)
134 REFERENCES `mydb`.`users` (`id`)
135 ON DELETE NO ACTION
136 ON UPDATE NO ACTION)
137ENGINE = InnoDB;
138
139
140-- -----------------------------------------------------
141-- Table `mydb`.`deliveries`
142-- -----------------------------------------------------
143CREATE TABLE IF NOT EXISTS `mydb`.`deliveries` (
144 `order_id` INT NOT NULL AUTO_INCREMENT,
145 `fio` INT NOT NULL,
146 `adress` VARCHAR(255) NULL,
147 `time` VARCHAR(45) NULL,
148 `date` DATE NULL,
149 `confirm` TINYINT(1) NULL,
150 PRIMARY KEY (`order_id`, `fio`),
151 UNIQUE INDEX `order_id_UNIQUE` (`order_id` ASC) VISIBLE,
152 INDEX `fio_idx` (`fio` ASC) VISIBLE,
153 CONSTRAINT `fio`
154 FOREIGN KEY (`fio`)
155 REFERENCES `mydb`.`users` (`id`)
156 ON DELETE NO ACTION
157 ON UPDATE NO ACTION)
158ENGINE = InnoDB;
159
160
161SET SQL_MODE=@OLD_SQL_MODE;
162SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
163SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;