· 8 years ago · Mar 28, 2018, 10:46 AM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15-- -----------------------------------------------------
16-- Schema test
17-- -----------------------------------------------------
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`Addresses`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`Addresses` (
24 `idAddress` INT NOT NULL AUTO_INCREMENT,
25 `post_code` VARCHAR(45) NOT NULL,
26 `street` VARCHAR(45) NOT NULL,
27 `number` INT NOT NULL,
28 `addtionnal informations` VARCHAR(45) NULL,
29 PRIMARY KEY (`idAddress`),
30 UNIQUE INDEX `idAddress_UNIQUE` (`idAddress` ASC))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `mydb`.`Users`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `mydb`.`Users` (
38 `idUser` INT NOT NULL AUTO_INCREMENT,
39 `first_name` VARCHAR(45) NOT NULL,
40 `last_name` VARCHAR(45) NOT NULL,
41 `idAddress` INT NOT NULL,
42 PRIMARY KEY (`idUser`),
43 UNIQUE INDEX `idUser_UNIQUE` (`idUser` ASC),
44 INDEX `fk_users_addresses_idx` (`idAddress` ASC),
45 CONSTRAINT `fk_users_addresses`
46 FOREIGN KEY (`idAddress`)
47 REFERENCES `mydb`.`Addresses` (`idAddress`)
48 ON DELETE NO ACTION
49 ON UPDATE NO ACTION)
50ENGINE = InnoDB;
51
52
53-- -----------------------------------------------------
54-- Table `mydb`.`Requests`
55-- -----------------------------------------------------
56CREATE TABLE IF NOT EXISTS `mydb`.`Requests` (
57 `idRequest` INT NOT NULL AUTO_INCREMENT,
58 `idFrom` INT NOT NULL,
59 `idTo` INT NOT NULL,
60 `state` VARCHAR(45) NOT NULL DEFAULT 'pending',
61 `payment_status` TINYINT NOT NULL DEFAULT 0,
62 `total_price` INT NOT NULL,
63 `created_on` DATETIME NOT NULL,
64 `validity_days` INT NOT NULL,
65 PRIMARY KEY (`idRequest`),
66 UNIQUE INDEX `idRequest_UNIQUE` (`idRequest` ASC),
67 INDEX `fk_requests_users_idx` (`idFrom` ASC),
68 INDEX `fk_requests_users_idx1` (`idTo` ASC),
69 CONSTRAINT `fk_requests_users_from`
70 FOREIGN KEY (`idFrom`)
71 REFERENCES `mydb`.`Users` (`idUser`)
72 ON DELETE NO ACTION
73 ON UPDATE NO ACTION,
74 CONSTRAINT `fk_requests_users_to`
75 FOREIGN KEY (`idTo`)
76 REFERENCES `mydb`.`Users` (`idUser`)
77 ON DELETE NO ACTION
78 ON UPDATE NO ACTION)
79ENGINE = InnoDB;
80
81
82-- -----------------------------------------------------
83-- Table `mydb`.`Shops`
84-- -----------------------------------------------------
85CREATE TABLE IF NOT EXISTS `mydb`.`Shops` (
86 `idShop` INT NOT NULL AUTO_INCREMENT,
87 `idAddress` INT NOT NULL,
88 `name` VARCHAR(45) NOT NULL,
89 `type` VARCHAR(45) NULL,
90 PRIMARY KEY (`idShop`),
91 UNIQUE INDEX `idShop_UNIQUE` (`idShop` ASC),
92 INDEX `fk_shops_addresses_idx` (`idAddress` ASC),
93 CONSTRAINT `fk_shops_addresses`
94 FOREIGN KEY (`idAddress`)
95 REFERENCES `mydb`.`Addresses` (`idAddress`)
96 ON DELETE NO ACTION
97 ON UPDATE NO ACTION)
98ENGINE = InnoDB;
99
100
101-- -----------------------------------------------------
102-- Table `mydb`.`Products`
103-- -----------------------------------------------------
104CREATE TABLE IF NOT EXISTS `mydb`.`Products` (
105 `idProduct` INT NOT NULL AUTO_INCREMENT,
106 `name` VARCHAR(45) NOT NULL,
107 `generic_name` VARCHAR(45) NOT NULL,
108 `manufacturer` VARCHAR(45) NOT NULL,
109 PRIMARY KEY (`idProduct`),
110 UNIQUE INDEX `idProduct_UNIQUE` (`idProduct` ASC))
111ENGINE = InnoDB;
112
113
114-- -----------------------------------------------------
115-- Table `mydb`.`Contains`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `mydb`.`Contains` (
118 `idRequest` INT NOT NULL,
119 `idProduct` INT NOT NULL,
120 `idShop` INT NULL,
121 `quantity` INT NOT NULL,
122 INDEX `fk_contains_requests_idx` (`idRequest` ASC),
123 INDEX `fk_contains_shops_idx` (`idShop` ASC),
124 INDEX `fk_contains_products_idx` (`idProduct` ASC),
125 CONSTRAINT `fk_contains_requests`
126 FOREIGN KEY (`idRequest`)
127 REFERENCES `mydb`.`Requests` (`idRequest`)
128 ON DELETE NO ACTION
129 ON UPDATE NO ACTION,
130 CONSTRAINT `fk_contains_shops`
131 FOREIGN KEY (`idShop`)
132 REFERENCES `mydb`.`Shops` (`idShop`)
133 ON DELETE NO ACTION
134 ON UPDATE NO ACTION,
135 CONSTRAINT `fk_contains_products`
136 FOREIGN KEY (`idProduct`)
137 REFERENCES `mydb`.`Products` (`idProduct`)
138 ON DELETE NO ACTION
139 ON UPDATE NO ACTION)
140ENGINE = InnoDB;
141
142
143-- -----------------------------------------------------
144-- Table `mydb`.`Prices`
145-- -----------------------------------------------------
146CREATE TABLE IF NOT EXISTS `mydb`.`Prices` (
147 `idProduct` INT NOT NULL,
148 `idShop` INT NOT NULL,
149 `price` DOUBLE NOT NULL,
150 INDEX `fk_prices_products_idx` (`idProduct` ASC),
151 INDEX `fk_prices_shops_idx` (`idShop` ASC),
152 CONSTRAINT `fk_prices_products`
153 FOREIGN KEY (`idProduct`)
154 REFERENCES `mydb`.`Products` (`idProduct`)
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION,
157 CONSTRAINT `fk_prices_shops`
158 FOREIGN KEY (`idShop`)
159 REFERENCES `mydb`.`Shops` (`idShop`)
160 ON DELETE NO ACTION
161 ON UPDATE NO ACTION)
162ENGINE = InnoDB;
163
164
165-- -----------------------------------------------------
166-- Table `mydb`.`UserRating`
167-- -----------------------------------------------------
168CREATE TABLE IF NOT EXISTS `mydb`.`UserRating` (
169 `idRatingUser` INT NOT NULL,
170 `idRatedUser` INT NOT NULL,
171 `rating` INT(1) NOT NULL,
172 `commentary` MEDIUMTEXT NULL,
173 INDEX `fk_userrating_users_idx` (`idRatingUser` ASC),
174 INDEX `fk_userrating_users_rated_idx` (`idRatedUser` ASC),
175 CONSTRAINT `fk_userrating_users_rating`
176 FOREIGN KEY (`idRatingUser`)
177 REFERENCES `mydb`.`Users` (`idUser`)
178 ON DELETE NO ACTION
179 ON UPDATE NO ACTION,
180 CONSTRAINT `fk_userrating_users_rated`
181 FOREIGN KEY (`idRatedUser`)
182 REFERENCES `mydb`.`Users` (`idUser`)
183 ON DELETE NO ACTION
184 ON UPDATE NO ACTION)
185ENGINE = InnoDB;
186
187
188-- -----------------------------------------------------
189-- Table `mydb`.`Friends`
190-- -----------------------------------------------------
191CREATE TABLE IF NOT EXISTS `mydb`.`Friends` (
192 `idUser` INT NOT NULL,
193 `idFriend` INT NOT NULL,
194 `isFriend` TINYINT NOT NULL DEFAULT 0,
195 `request_date` DATETIME NOT NULL,
196 INDEX `fk_friends_users_userA_idx` (`idUser` ASC),
197 INDEX `fk_friends_users_userB_idx` (`idFriend` ASC),
198 CONSTRAINT `fk_friends_users_userA`
199 FOREIGN KEY (`idUser`)
200 REFERENCES `mydb`.`Users` (`idUser`)
201 ON DELETE NO ACTION
202 ON UPDATE NO ACTION,
203 CONSTRAINT `fk_friends_users_userB`
204 FOREIGN KEY (`idFriend`)
205 REFERENCES `mydb`.`Users` (`idUser`)
206 ON DELETE NO ACTION
207 ON UPDATE NO ACTION)
208ENGINE = InnoDB;
209
210
211-- -----------------------------------------------------
212-- Table `mydb`.`FavoriteProductLists`
213-- -----------------------------------------------------
214CREATE TABLE IF NOT EXISTS `mydb`.`FavoriteProductLists` (
215 `idList` INT NOT NULL AUTO_INCREMENT,
216 `idUser` INT NOT NULL,
217 PRIMARY KEY (`idList`),
218 UNIQUE INDEX `idList_UNIQUE` (`idList` ASC),
219 INDEX `fk_favoriteproductlists_users_idx` (`idUser` ASC),
220 CONSTRAINT `fk_favoriteproductlists_users`
221 FOREIGN KEY (`idUser`)
222 REFERENCES `mydb`.`Users` (`idUser`)
223 ON DELETE NO ACTION
224 ON UPDATE NO ACTION)
225ENGINE = InnoDB;
226
227
228-- -----------------------------------------------------
229-- Table `mydb`.`ProductListDetails`
230-- -----------------------------------------------------
231CREATE TABLE IF NOT EXISTS `mydb`.`ProductListDetails` (
232 `idProductList` INT NOT NULL,
233 `idProduct` INT NOT NULL,
234 INDEX `fk_productlistdetails_favoriteproductlists_idx` (`idProductList` ASC),
235 INDEX `fk_productlistdetails_products_idx` (`idProduct` ASC),
236 CONSTRAINT `fk_productlistdetails_favoriteproductlists`
237 FOREIGN KEY (`idProductList`)
238 REFERENCES `mydb`.`FavoriteProductLists` (`idList`)
239 ON DELETE NO ACTION
240 ON UPDATE NO ACTION,
241 CONSTRAINT `fk_productlistdetails_products`
242 FOREIGN KEY (`idProduct`)
243 REFERENCES `mydb`.`Products` (`idProduct`)
244 ON DELETE NO ACTION
245 ON UPDATE NO ACTION)
246ENGINE = InnoDB;
247
248
249-- -----------------------------------------------------
250-- Table `mydb`.`Users_has_Users`
251-- -----------------------------------------------------
252CREATE TABLE IF NOT EXISTS `mydb`.`Users_has_Users` (
253 `Users_idUser` INT NOT NULL,
254 `Users_idUser1` INT NOT NULL,
255 PRIMARY KEY (`Users_idUser`, `Users_idUser1`),
256 INDEX `fk_Users_has_Users_Users2_idx` (`Users_idUser1` ASC),
257 INDEX `fk_Users_has_Users_Users1_idx` (`Users_idUser` ASC),
258 CONSTRAINT `fk_Users_has_Users_Users1`
259 FOREIGN KEY (`Users_idUser`)
260 REFERENCES `mydb`.`Users` (`idUser`)
261 ON DELETE NO ACTION
262 ON UPDATE NO ACTION,
263 CONSTRAINT `fk_Users_has_Users_Users2`
264 FOREIGN KEY (`Users_idUser1`)
265 REFERENCES `mydb`.`Users` (`idUser`)
266 ON DELETE NO ACTION
267 ON UPDATE NO ACTION)
268ENGINE = InnoDB;
269
270
271SET SQL_MODE=@OLD_SQL_MODE;
272SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
273SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;