· 4 years ago · Feb 27, 2021, 09:56 PM
1-- MySQL Script generated by MySQL Workbench
2-- Fri Feb 26 14:01:11 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`.`shops`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `internet_sales`.`shops` (
24 `id` INT NOT NULL,
25 `name` VARCHAR(255) NULL,
26 `adress` 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 `internet_sales`.`product_type`
37-- -----------------------------------------------------
38CREATE TABLE IF NOT EXISTS `internet_sales`.`product_type` (
39 `id` INT NOT NULL,
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 `internet_sales`.`products`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `internet_sales`.`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 NOT NULL,
57 `img` VARCHAR(255) NULL,
58 `price` VARCHAR(45) NULL,
59 `warranty` VARCHAR(45) NULL,
60 PRIMARY KEY (`id`, `shop_id`, `type_id`, `data`),
61 INDEX `shop_to_products_idx` (`shop_id` ASC) VISIBLE,
62 INDEX `product_to_type_idx` (`type_id` ASC) VISIBLE,
63 CONSTRAINT `products_to_shop`
64 FOREIGN KEY (`shop_id`)
65 REFERENCES `internet_sales`.`shops` (`id`)
66 ON DELETE NO ACTION
67 ON UPDATE NO ACTION,
68 CONSTRAINT `product_to_type`
69 FOREIGN KEY (`type_id`)
70 REFERENCES `internet_sales`.`product_type` (`id`)
71 ON DELETE NO ACTION
72 ON UPDATE NO ACTION)
73ENGINE = InnoDB;
74
75
76-- -----------------------------------------------------
77-- Table `internet_sales`.`orders`
78-- -----------------------------------------------------
79CREATE TABLE IF NOT EXISTS `internet_sales`.`orders` (
80 `id` INT NOT NULL,
81 `product_id` INT NOT NULL,
82 `fio` VARCHAR(255) NOT NULL,
83 `shop_id` INT NOT NULL,
84 `date` DATE NULL,
85 `quantity` TINYINT NULL,
86 `tel` VARCHAR(100) NULL,
87 `confirm` TINYINT NULL,
88 PRIMARY KEY (`id`, `product_id`, `fio`, `shop_id`),
89 INDEX `shop_id_to_orders_idx` (`shop_id` ASC) VISIBLE,
90 INDEX `product_id_to_orders_idx` (`product_id` ASC) VISIBLE,
91 CONSTRAINT `shop_id_to_orders`
92 FOREIGN KEY (`shop_id`)
93 REFERENCES `internet_sales`.`shops` (`id`)
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION,
96 CONSTRAINT `product_id_to_orders`
97 FOREIGN KEY (`product_id`)
98 REFERENCES `internet_sales`.`products` (`id`)
99 ON DELETE NO ACTION
100 ON UPDATE NO ACTION)
101ENGINE = InnoDB;
102
103
104-- -----------------------------------------------------
105-- Table `internet_sales`.`users`
106-- -----------------------------------------------------
107CREATE TABLE IF NOT EXISTS `internet_sales`.`users` (
108 `id` INT NOT NULL,
109 `fio` VARCHAR(255) NOT NULL,
110 `login` VARCHAR(255) NULL,
111 `password` VARCHAR(255) NULL,
112 `e-mail` VARCHAR(255) NULL,
113 `type` VARCHAR(45) NULL,
114 PRIMARY KEY (`id`, `fio`),
115 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
116 UNIQUE INDEX `login_UNIQUE` (`login` ASC) VISIBLE,
117 INDEX `fio_to_orders_idx` (`fio` ASC) VISIBLE,
118 CONSTRAINT `fio_to_orders`
119 FOREIGN KEY (`fio`)
120 REFERENCES `internet_sales`.`orders` (`fio`)
121 ON DELETE NO ACTION
122 ON UPDATE NO ACTION)
123ENGINE = InnoDB;
124
125
126-- -----------------------------------------------------
127-- Table `internet_sales`.`settings`
128-- -----------------------------------------------------
129CREATE TABLE IF NOT EXISTS `internet_sales`.`settings` (
130 `id` INT NOT NULL AUTO_INCREMENT,
131 `host` VARCHAR(45) NULL,
132 `db` VARCHAR(45) NULL,
133 `user` VARCHAR(45) NULL,
134 `password` VARCHAR(45) NULL,
135 PRIMARY KEY (`id`))
136 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
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,
145 `fio` VARCHAR(255) NOT NULL,
146 `adress` VARCHAR(255) NULL,
147 `time` VARCHAR(45) NULL,
148 `date` DATE NULL,
149 `confirm` TINYINT NULL,
150 PRIMARY KEY (`order_id`, `fio`),
151 INDEX `fio_to_orders_idx` (`fio` ASC) VISIBLE,
152 CONSTRAINT `fio_to_orders`
153 FOREIGN KEY (`fio`)
154 REFERENCES `internet_sales`.`orders` (`fio`)
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION,
157 CONSTRAINT `order_id_to_deliver`
158 FOREIGN KEY (`order_id`)
159 REFERENCES `internet_sales`.`orders` (`id`)
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