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