· 4 years ago · Jul 08, 2021, 03:54 PM
1-- MySQL Script generated by MySQL Workbench
2-- Thu Jul 8 18:48:34 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-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `mydb` ;
14
15-- -----------------------------------------------------
16-- Schema mydb
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
19USE `mydb` ;
20
21-- -----------------------------------------------------
22-- Table `mydb`.`Delivieres`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `mydb`.`Delivieres` ;
25
26CREATE TABLE IF NOT EXISTS `mydb`.`Delivieres` (
27 `order_id` INT NOT NULL,
28 `shop_id` INT NOT NULL,
29 `adress` VARCHAR(255) NULL,
30 `time` VARCHAR(45) NULL,
31 `date` DATE NULL,
32 `confirm` TINYINT(1) NULL,
33 PRIMARY KEY (`order_id`, `shop_id`),
34 INDEX `delivieres_to_shop_idx` (`shop_id` ASC) VISIBLE,
35 CONSTRAINT `delivieres_to_shop`
36 FOREIGN KEY (`shop_id`)
37 REFERENCES `mydb`.`shops` (`id`)
38 ON DELETE NO ACTION
39 ON UPDATE NO ACTION)
40ENGINE = InnoDB;
41
42
43-- -----------------------------------------------------
44-- Table `mydb`.`shops`
45-- -----------------------------------------------------
46DROP TABLE IF EXISTS `mydb`.`shops` ;
47
48CREATE TABLE IF NOT EXISTS `mydb`.`shops` (
49 `id` INT NOT NULL AUTO_INCREMENT,
50 `name` VARCHAR(255) NULL,
51 `adress` VARCHAR(255) NULL,
52 `tel` VARCHAR(100) NULL,
53 `site` VARCHAR(100) NULL,
54 `site` VARCHAR(45) NULL,
55 `email` VARCHAR(100) NULL,
56 `email` VARCHAR(45) NULL,
57 PRIMARY KEY (`id`),
58 CONSTRAINT `shop_to_delivieres`
59 FOREIGN KEY (`id`)
60 REFERENCES `mydb`.`Delivieres` (`order_id`)
61 ON DELETE NO ACTION
62 ON UPDATE NO ACTION)
63ENGINE = InnoDB;
64
65
66-- -----------------------------------------------------
67-- Table `mydb`.`product_type`
68-- -----------------------------------------------------
69DROP TABLE IF EXISTS `mydb`.`product_type` ;
70
71CREATE TABLE IF NOT EXISTS `mydb`.`product_type` (
72 `id` INT NOT NULL AUTO_INCREMENT,
73 `name` VARCHAR(255) NULL,
74 PRIMARY KEY (`id`))
75ENGINE = InnoDB;
76
77
78-- -----------------------------------------------------
79-- Table `mydb`.`products`
80-- -----------------------------------------------------
81DROP TABLE IF EXISTS `mydb`.`products` ;
82
83CREATE TABLE IF NOT EXISTS `mydb`.`products` (
84 `id` INT NOT NULL AUTO_INCREMENT,
85 `shop_id` INT NOT NULL,
86 `type_id` INT NOT NULL,
87 `brand` VARCHAR(255) NULL,
88 `model` VARCHAR(255) NULL,
89 `data` TINYTEXT NULL,
90 `img` VARCHAR(255) NULL,
91 `price` VARCHAR(45) NULL,
92 `warranty` VARCHAR(45) NULL,
93 PRIMARY KEY (`id`, `shop_id`, `type_id`),
94 INDEX `product_to_type_idx` (`type_id` ASC) VISIBLE,
95 INDEX `shop_to_products_idx` (`shop_id` ASC) VISIBLE,
96 CONSTRAINT `shop_to_products`
97 FOREIGN KEY (`shop_id`)
98 REFERENCES `mydb`.`shops` (`id`)
99 ON DELETE NO ACTION
100 ON UPDATE NO ACTION,
101 CONSTRAINT `product_to_type`
102 FOREIGN KEY (`type_id`)
103 REFERENCES `mydb`.`product_type` (`id`)
104 ON DELETE NO ACTION
105 ON UPDATE NO ACTION)
106ENGINE = InnoDB;
107
108
109-- -----------------------------------------------------
110-- Table `mydb`.`orders`
111-- -----------------------------------------------------
112DROP TABLE IF EXISTS `mydb`.`orders` ;
113
114CREATE TABLE IF NOT EXISTS `mydb`.`orders` (
115 `id` INT NOT NULL AUTO_INCREMENT,
116 `shop_id` INT NOT NULL,
117 `product_id` INT NOT NULL,
118 `fio` INT NOT NULL,
119 PRIMARY KEY (`id`, `shop_id`, `product_id`, `fio`),
120 CONSTRAINT `orders_to_shop`
121 FOREIGN KEY ()
122 REFERENCES `mydb`.`shops` ()
123 ON DELETE NO ACTION
124 ON UPDATE NO ACTION,
125 CONSTRAINT `orders_to_product`
126 FOREIGN KEY ()
127 REFERENCES `mydb`.`products` ()
128 ON DELETE NO ACTION
129 ON UPDATE NO ACTION)
130ENGINE = InnoDB;
131
132
133-- -----------------------------------------------------
134-- Table `mydb`.`users`
135-- -----------------------------------------------------
136DROP TABLE IF EXISTS `mydb`.`users` ;
137
138CREATE TABLE IF NOT EXISTS `mydb`.`users` (
139 `id` INT NOT NULL AUTO_INCREMENT,
140 `fio` VARCHAR(255) NOT NULL,
141 `userscol` VARCHAR(255) NULL,
142 `login` VARCHAR(255) NULL,
143 `password` VARCHAR(255) NULL,
144 `email` VARCHAR(255) NULL,
145 `type` VARCHAR(45) NULL,
146 PRIMARY KEY (`id`, `fio`),
147 UNIQUE INDEX `userscol_UNIQUE` (`userscol` ASC) VISIBLE,
148 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
149 CONSTRAINT `uesers_to_orders`
150 FOREIGN KEY ()
151 REFERENCES `mydb`.`orders` ()
152 ON DELETE NO ACTION
153 ON UPDATE NO ACTION)
154ENGINE = InnoDB;
155
156
157-- -----------------------------------------------------
158-- Table `mydb`.`settings`
159-- -----------------------------------------------------
160DROP TABLE IF EXISTS `mydb`.`settings` ;
161
162CREATE TABLE IF NOT EXISTS `mydb`.`settings` (
163 `id` INT NOT NULL AUTO_INCREMENT,
164 `host` VARCHAR(45) NULL,
165 `db` VARCHAR(45) NULL,
166 `user` VARCHAR(45) NULL,
167 `password` VARCHAR(45) NULL,
168 PRIMARY KEY (`id`))
169ENGINE = InnoDB;
170
171
172SET SQL_MODE=@OLD_SQL_MODE;
173SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
174SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
175