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