· 4 years ago · Feb 26, 2021, 11:08 AM
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`))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `internet_sales`.`product_type`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `internet_sales`.`product_type` (
38 `id` INT NOT NULL,
39 `name` VARCHAR(255) NULL,
40 PRIMARY KEY (`id`))
41ENGINE = InnoDB;
42
43
44-- -----------------------------------------------------
45-- Table `internet_sales`.`products`
46-- -----------------------------------------------------
47CREATE TABLE IF NOT EXISTS `internet_sales`.`products` (
48 `id` INT NOT NULL AUTO_INCREMENT,
49 `shop_id` INT NOT NULL,
50 `type_id` INT NOT NULL,
51 `brand` VARCHAR(255) NULL,
52 `model` VARCHAR(255) NULL,
53 `data` TINYTEXT NOT NULL,
54 `img` VARCHAR(255) NULL,
55 `price` VARCHAR(45) NULL,
56 `warranty` VARCHAR(45) NULL,
57 PRIMARY KEY (`id`, `shop_id`, `type_id`, `data`),
58 INDEX `shop_to_products_idx` (`shop_id` ASC) VISIBLE,
59 INDEX `product_to_type_idx` (`type_id` ASC) VISIBLE,
60 CONSTRAINT `products_to_shop`
61 FOREIGN KEY (`shop_id`)
62 REFERENCES `internet_sales`.`shops` (`id`)
63 ON DELETE NO ACTION
64 ON UPDATE NO ACTION,
65 CONSTRAINT `product_to_type`
66 FOREIGN KEY (`type_id`)
67 REFERENCES `internet_sales`.`product_type` (`id`)
68 ON DELETE NO ACTION
69 ON UPDATE NO ACTION)
70ENGINE = InnoDB;
71
72
73-- -----------------------------------------------------
74-- Table `internet_sales`.`orders`
75-- -----------------------------------------------------
76CREATE TABLE IF NOT EXISTS `internet_sales`.`orders` (
77 `id` INT NOT NULL,
78 `product_id` INT NOT NULL,
79 `fio` VARCHAR(255) NOT NULL,
80 `shop_id` INT NOT NULL,
81 `date` DATE NULL,
82 `quantity` TINYINT NULL,
83 `tel` VARCHAR(100) NULL,
84 `confirm` TINYINT NULL,
85 PRIMARY KEY (`id`, `product_id`, `fio`, `shop_id`),
86 INDEX `shop_id_to_orders_idx` (`shop_id` ASC) VISIBLE,
87 INDEX `product_id_to_orders_idx` (`product_id` ASC) VISIBLE,
88 CONSTRAINT `shop_id_to_orders`
89 FOREIGN KEY (`shop_id`)
90 REFERENCES `internet_sales`.`shops` (`id`)
91 ON DELETE NO ACTION
92 ON UPDATE NO ACTION,
93 CONSTRAINT `product_id_to_orders`
94 FOREIGN KEY (`product_id`)
95 REFERENCES `internet_sales`.`products` (`id`)
96 ON DELETE NO ACTION
97 ON UPDATE NO ACTION)
98ENGINE = InnoDB;
99
100
101-- -----------------------------------------------------
102-- Table `internet_sales`.`users`
103-- -----------------------------------------------------
104CREATE TABLE IF NOT EXISTS `internet_sales`.`users` (
105 `id` INT NOT NULL,
106 `fio` VARCHAR(255) NOT NULL,
107 `login` VARCHAR(255) NULL,
108 `password` VARCHAR(255) NULL,
109 `e-mail` VARCHAR(255) NULL,
110 `type` VARCHAR(45) NULL,
111 PRIMARY KEY (`id`, `fio`),
112 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
113 UNIQUE INDEX `login_UNIQUE` (`login` ASC) VISIBLE,
114 INDEX `fio_to_orders_idx` (`fio` ASC) VISIBLE,
115 CONSTRAINT `fio_to_orders`
116 FOREIGN KEY (`fio`)
117 REFERENCES `internet_sales`.`orders` (`fio`)
118 ON DELETE NO ACTION
119 ON UPDATE NO ACTION)
120ENGINE = InnoDB;
121
122
123-- -----------------------------------------------------
124-- Table `internet_sales`.`settings`
125-- -----------------------------------------------------
126CREATE TABLE IF NOT EXISTS `internet_sales`.`settings` (
127 `id` INT NOT NULL AUTO_INCREMENT,
128 `host` VARCHAR(45) NULL,
129 `db` VARCHAR(45) NULL,
130 `user` VARCHAR(45) NULL,
131 `password` VARCHAR(45) NULL,
132 PRIMARY KEY (`id`))
133ENGINE = InnoDB;
134
135
136-- -----------------------------------------------------
137-- Table `internet_sales`.`deliveries`
138-- -----------------------------------------------------
139CREATE TABLE IF NOT EXISTS `internet_sales`.`deliveries` (
140 `order_id` INT NOT NULL,
141 `fio` VARCHAR(255) NOT NULL,
142 `adress` VARCHAR(255) NULL,
143 `time` VARCHAR(45) NULL,
144 `date` DATE NULL,
145 `confirm` TINYINT NULL,
146 PRIMARY KEY (`order_id`, `fio`),
147 INDEX `fio_to_orders_idx` (`fio` ASC) VISIBLE,
148 CONSTRAINT `fio_to_orders`
149 FOREIGN KEY (`fio`)
150 REFERENCES `internet_sales`.`orders` (`fio`)
151 ON DELETE NO ACTION
152 ON UPDATE NO ACTION,
153 CONSTRAINT `order_id_to_deliver`
154 FOREIGN KEY (`order_id`)
155 REFERENCES `internet_sales`.`orders` (`id`)
156 ON DELETE NO ACTION
157 ON UPDATE NO ACTION)
158ENGINE = InnoDB;
159
160
161SET SQL_MODE=@OLD_SQL_MODE;
162SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
163SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
164