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