· last year · May 23, 2024, 01:55 PM
1-- MySQL Script generated by MySQL Workbench
2-- Thu May 23 16:49:08 2024
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`.`product_type`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`product_type` (
24 `id` INT NOT NULL,
25 `name` VARCHAR(255) NULL,
26 PRIMARY KEY (`id`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`shops`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`shops` (
34 `id` INT NOT NULL,
35 `name` VARCHAR(255) NULL,
36 `address` VARCHAR(255) NULL,
37 `tel` VARCHAR(100) NULL,
38 `site` VARCHAR(100) NULL,
39 `email` VARCHAR(100) NULL,
40 PRIMARY KEY (`id`),
41 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
42ENGINE = InnoDB;
43
44
45-- -----------------------------------------------------
46-- Table `mydb`.`products`
47-- -----------------------------------------------------
48CREATE TABLE IF NOT EXISTS `mydb`.`products` (
49 `id` INT NOT NULL,
50 `shop_id` INT NOT NULL,
51 `type_id` INT NOT NULL,
52 `brand` VARCHAR(255) NULL,
53 `model` VARCHAR(255) NULL,
54 `data` TINYTEXT NULL,
55 `img` VARCHAR(255) NULL,
56 `price` VARCHAR(45) NULL,
57 `warranty` VARCHAR(45) NULL,
58 PRIMARY KEY (`id`, `shop_id`, `type_id`),
59 INDEX `products_to_prod_type_idx` (`type_id` ASC) VISIBLE,
60 INDEX `products_to_shops_idx` (`shop_id` ASC) VISIBLE,
61 CONSTRAINT `products_to_prod_type`
62 FOREIGN KEY (`type_id`)
63 REFERENCES `mydb`.`product_type` (`id`)
64 ON DELETE NO ACTION
65 ON UPDATE NO ACTION,
66 CONSTRAINT `products_to_shops`
67 FOREIGN KEY (`shop_id`)
68 REFERENCES `mydb`.`shops` (`id`)
69 ON DELETE NO ACTION
70 ON UPDATE NO ACTION)
71ENGINE = InnoDB;
72
73
74-- -----------------------------------------------------
75-- Table `mydb`.`users`
76-- -----------------------------------------------------
77CREATE TABLE IF NOT EXISTS `mydb`.`users` (
78 `id` INT NOT NULL,
79 `fio` VARCHAR(255) NOT NULL,
80 `login` VARCHAR(255) NULL,
81 `password` VARCHAR(255) NULL,
82 `e_mail` VARCHAR(255) NULL,
83 `type` VARCHAR(45) NULL,
84 PRIMARY KEY (`id`, `fio`),
85 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
86 UNIQUE INDEX `login_UNIQUE` (`login` ASC) VISIBLE)
87ENGINE = InnoDB;
88
89
90-- -----------------------------------------------------
91-- Table `mydb`.`orders`
92-- -----------------------------------------------------
93CREATE TABLE IF NOT EXISTS `mydb`.`orders` (
94 `id` INT NOT NULL,
95 `shop_id` INT NOT NULL,
96 `product_id` INT NOT NULL,
97 `fio` INT NOT NULL,
98 `date` DATE NULL,
99 `quantity` TINYINT NULL,
100 `tel` VARCHAR(100) NULL,
101 `confirm` TINYINT NULL,
102 PRIMARY KEY (`id`, `shop_id`, `product_id`, `fio`),
103 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
104 INDEX `orders_to_products_idx` (`product_id` ASC) VISIBLE,
105 INDEX `orders_to_users_idx` (`fio` ASC) VISIBLE,
106 INDEX `orders_to_shops_idx` (`shop_id` ASC) VISIBLE,
107 CONSTRAINT `orders_to_products`
108 FOREIGN KEY (`product_id`)
109 REFERENCES `mydb`.`products` (`id`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION,
112 CONSTRAINT `orders_to_users`
113 FOREIGN KEY (`fio`)
114 REFERENCES `mydb`.`users` (`id`)
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION,
117 CONSTRAINT `orders_to_shops`
118 FOREIGN KEY (`shop_id`)
119 REFERENCES `mydb`.`shops` (`id`)
120 ON DELETE NO ACTION
121 ON UPDATE NO ACTION)
122ENGINE = InnoDB;
123
124
125-- -----------------------------------------------------
126-- Table `mydb`.`deliveries`
127-- -----------------------------------------------------
128CREATE TABLE IF NOT EXISTS `mydb`.`deliveries` (
129 `order_id` INT NOT NULL,
130 `fio` INT NOT NULL,
131 `address` VARCHAR(255) NULL,
132 `deliveriescol` VARCHAR(45) NULL,
133 `date` DATE NULL,
134 `confirm` TINYINT NULL,
135 PRIMARY KEY (`order_id`, `fio`),
136 INDEX `del_to_orders_idx` (`fio` ASC) VISIBLE,
137 CONSTRAINT `deliveries_to_orders`
138 FOREIGN KEY (`order_id`)
139 REFERENCES `mydb`.`orders` (`id`)
140 ON DELETE CASCADE
141 ON UPDATE CASCADE,
142 CONSTRAINT `del_to_orders`
143 FOREIGN KEY (`fio`)
144 REFERENCES `mydb`.`orders` (`fio`)
145 ON DELETE CASCADE
146 ON UPDATE CASCADE)
147ENGINE = InnoDB;
148
149
150-- -----------------------------------------------------
151-- Table `mydb`.`settings`
152-- -----------------------------------------------------
153CREATE TABLE IF NOT EXISTS `mydb`.`settings` (
154 `id` INT NOT NULL,
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