· 4 years ago · Jun 24, 2021, 02:56 AM
1-- MySQL Script generated by MySQL Workbench
2-- Thu Jun 24 05:49:09 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,
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(255) NULL,
56 `address` VARCHAR(255) NULL,
57 `tel` VARCHAR(100) NULL,
58 `site` VARCHAR(100) NULL,
59 `email` VARCHAR(100) 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 AUTO_INCREMENT,
70 `name` VARCHAR(255) NULL,
71 PRIMARY KEY (`id`),
72 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
73 UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE)
74ENGINE = InnoDB;
75
76
77-- -----------------------------------------------------
78-- Table `mydb`.`products`
79-- -----------------------------------------------------
80CREATE TABLE IF NOT EXISTS `mydb`.`products` (
81 `id` INT NOT NULL AUTO_INCREMENT,
82 `shop_id` INT NOT NULL,
83 `type_id` INT NOT NULL,
84 `brand` VARCHAR(255) NULL,
85 `model` VARCHAR(255) NULL,
86 `data` TINYTEXT NULL,
87 `img` VARCHAR(255) NULL,
88 `price` VARCHAR(45) NULL,
89 `warranty` VARCHAR(45) NULL,
90 PRIMARY KEY (`id`, `shop_id`, `type_id`),
91 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
92 INDEX `product_to_type_idx` (`type_id` ASC) VISIBLE,
93 INDEX `shop_to_product_idx` (`shop_id` ASC) VISIBLE,
94 CONSTRAINT `product_to_type`
95 FOREIGN KEY (`type_id`)
96 REFERENCES `mydb`.`product_type` (`id`)
97 ON DELETE NO ACTION
98 ON UPDATE NO ACTION,
99 CONSTRAINT `shop_to_product`
100 FOREIGN KEY (`shop_id`)
101 REFERENCES `mydb`.`shops` (`id`)
102 ON DELETE NO ACTION
103 ON UPDATE NO ACTION)
104ENGINE = InnoDB;
105
106
107-- -----------------------------------------------------
108-- Table `mydb`.`orders`
109-- -----------------------------------------------------
110CREATE TABLE IF NOT EXISTS `mydb`.`orders` (
111 `id` INT NOT NULL AUTO_INCREMENT,
112 `shop_id` INT NOT NULL,
113 `product_id` INT NOT NULL,
114 `fio` INT NOT NULL,
115 `date` DATE NULL,
116 `quantity` INT NULL,
117 `tel` VARCHAR(255) NULL,
118 `confirm` TINYINT(1) NULL,
119 PRIMARY KEY (`id`, `shop_id`, `product_id`, `fio`),
120 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
121 INDEX `orders_to_product_idx` (`product_id` ASC) VISIBLE,
122 INDEX `orders_to_shop_idx` (`shop_id` ASC) VISIBLE,
123 INDEX `orders_to_user_idx` (`fio` ASC) VISIBLE,
124 CONSTRAINT `orders_to_product`
125 FOREIGN KEY (`product_id`)
126 REFERENCES `mydb`.`products` (`id`)
127 ON DELETE NO ACTION
128 ON UPDATE NO ACTION,
129 CONSTRAINT `orders_to_shop`
130 FOREIGN KEY (`shop_id`)
131 REFERENCES `mydb`.`shops` (`id`)
132 ON DELETE NO ACTION
133 ON UPDATE NO ACTION,
134 CONSTRAINT `orders_to_user`
135 FOREIGN KEY (`fio`)
136 REFERENCES `mydb`.`users` (`id`)
137 ON DELETE NO ACTION
138 ON UPDATE NO ACTION)
139ENGINE = InnoDB;
140
141
142-- -----------------------------------------------------
143-- Table `mydb`.`deliveries`
144-- -----------------------------------------------------
145CREATE TABLE IF NOT EXISTS `mydb`.`deliveries` (
146 `order_id` INT NOT NULL AUTO_INCREMENT,
147 `fio` INT NOT NULL,
148 `address` VARCHAR(255) NULL,
149 `time` VARCHAR(255) NULL,
150 `date` DATE NULL,
151 `confirm` TINYINT(1) NULL,
152 PRIMARY KEY (`order_id`, `fio`),
153 UNIQUE INDEX `order_id_UNIQUE` (`order_id` ASC) VISIBLE,
154 INDEX `fio_fio_idx` (`fio` ASC) VISIBLE,
155 CONSTRAINT `deliver_order`
156 FOREIGN KEY (`order_id`)
157 REFERENCES `mydb`.`orders` (`id`)
158 ON DELETE NO ACTION
159 ON UPDATE NO ACTION,
160 CONSTRAINT `fio_fio`
161 FOREIGN KEY (`fio`)
162 REFERENCES `mydb`.`orders` (`fio`)
163 ON DELETE NO ACTION
164 ON UPDATE NO ACTION)
165ENGINE = InnoDB;
166
167
168SET SQL_MODE=@OLD_SQL_MODE;
169SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
170SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
171