· 4 years ago · Feb 25, 2021, 01:02 PM
1-- MySQL Script generated by MySQL Workbench
2-- Thu Feb 25 15:53:17 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 habr_db
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema habr_db
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `habr_db` DEFAULT CHARACTER SET utf8 ;
18USE `habr_db` ;
19
20-- -----------------------------------------------------
21-- Table `habr_db`.`users`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `habr_db`.`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 `habr_db`.`settings`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `habr_db`.`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 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
46 PRIMARY KEY (`id`))
47ENGINE = InnoDB;
48
49
50-- -----------------------------------------------------
51-- Table `habr_db`.`Shops`
52-- -----------------------------------------------------
53CREATE TABLE IF NOT EXISTS `habr_db`.`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 `e_mail` VARCHAR(100) NULL,
60 PRIMARY KEY (`id`),
61 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
62ENGINE = InnoDB;
63
64
65-- -----------------------------------------------------
66-- Table `habr_db`.`product_type`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `habr_db`.`product_type` (
69 `id` INT NOT NULL AUTO_INCREMENT,
70 `name` VARCHAR(255) NULL,
71 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
72 PRIMARY KEY (`id`))
73ENGINE = InnoDB;
74
75
76-- -----------------------------------------------------
77-- Table `habr_db`.`Products`
78-- -----------------------------------------------------
79CREATE TABLE IF NOT EXISTS `habr_db`.`Products` (
80 `id` INT NOT NULL AUTO_INCREMENT,
81 `shops_id` INT NOT NULL,
82 `type_id` INT NOT NULL,
83 `brand` VARCHAR(255) NULL,
84 `model` VARCHAR(255) NULL,
85 `data` TINYTEXT NULL,
86 `img` VARCHAR(255) NULL,
87 `price` VARCHAR(45) NULL,
88 `warranty` VARCHAR(45) NULL,
89 PRIMARY KEY (`id`, `shops_id`, `type_id`),
90 INDEX `shop_to_products_idx` (`shops_id` ASC) VISIBLE,
91 CONSTRAINT `shop_to_products`
92 FOREIGN KEY (`shops_id`)
93 REFERENCES `habr_db`.`Shops` (`id`)
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION,
96 CONSTRAINT `product_to_type`
97 FOREIGN KEY (`type_id`)
98 REFERENCES `habr_db`.`product_type` (`id`)
99 ON DELETE NO ACTION
100 ON UPDATE NO ACTION)
101ENGINE = InnoDB;
102
103
104-- -----------------------------------------------------
105-- Table `habr_db`.`Orders`
106-- -----------------------------------------------------
107CREATE TABLE IF NOT EXISTS `habr_db`.`Orders` (
108 `id` INT NOT NULL AUTO_INCREMENT,
109 `shop_id` INT NOT NULL,
110 `product_id` INT NOT NULL,
111 `fio` INT NOT NULL,
112 `date` DATE NULL,
113 `quantity` TINYINT NULL,
114 `tel` VARCHAR(100) NULL,
115 `confirm` TINYINT NULL,
116 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
117 PRIMARY KEY (`id`, `shop_id`, `product_id`, `fio`),
118 INDEX `shops_to_orders_idx` (`shop_id` ASC) VISIBLE,
119 INDEX `product_to_orders_idx` (`product_id` ASC) VISIBLE,
120 INDEX `usersID_to_fio_idx` (`fio` ASC) VISIBLE,
121 CONSTRAINT `shop_to_orders`
122 FOREIGN KEY (`shop_id`)
123 REFERENCES `habr_db`.`Shops` (`id`)
124 ON DELETE NO ACTION
125 ON UPDATE NO ACTION,
126 CONSTRAINT `product_to_orders`
127 FOREIGN KEY (`product_id`)
128 REFERENCES `habr_db`.`Products` (`id`)
129 ON DELETE NO ACTION
130 ON UPDATE NO ACTION,
131 CONSTRAINT `usersID_to_fio`
132 FOREIGN KEY (`fio`)
133 REFERENCES `habr_db`.`users` (`id`)
134 ON DELETE NO ACTION
135 ON UPDATE NO ACTION)
136ENGINE = InnoDB;
137
138
139-- -----------------------------------------------------
140-- Table `habr_db`.`Deliveries`
141-- -----------------------------------------------------
142CREATE TABLE IF NOT EXISTS `habr_db`.`Deliveries` (
143 `order_id` INT NOT NULL AUTO_INCREMENT,
144 `fio` INT NOT NULL,
145 `address` VARCHAR(255) NULL,
146 `time` VARCHAR(45) NULL,
147 `date` DATE NULL,
148 `confirm` TINYINT NULL,
149 PRIMARY KEY (`order_id`, `fio`),
150 UNIQUE INDEX `order_id_UNIQUE` (`order_id` ASC) VISIBLE,
151 INDEX `fioOrder_to_fioDeliveries_idx` (`fio` ASC) VISIBLE,
152 CONSTRAINT `orders_to_deliveries`
153 FOREIGN KEY (`order_id`)
154 REFERENCES `habr_db`.`Orders` (`id`)
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION,
157 CONSTRAINT `fioOrder_to_fioDeliveries`
158 FOREIGN KEY (`fio`)
159 REFERENCES `habr_db`.`Orders` (`fio`)
160 ON DELETE NO ACTION
161 ON UPDATE NO ACTION)
162ENGINE = InnoDB;
163
164
165SET SQL_MODE=@OLD_SQL_MODE;
166SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
167SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
168