· 9 years ago · Oct 11, 2016, 12:22 PM
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 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`Address`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`Address` (
21 `idAddress` INT NOT NULL AUTO_INCREMENT,
22 `street` VARCHAR(100) NULL,
23 `address` VARCHAR(20) NULL,
24 `zipcode` VARCHAR(45) NULL,
25 `city` VARCHAR(100) NULL,
26 `lat` DECIMAL NULL,
27 `long` DECIMAL NULL,
28 `email` VARCHAR(200) NULL,
29 `phone` VARCHAR(30) NULL,
30 `Comment` VARCHAR(100) NULL,
31 PRIMARY KEY (`idAddress`),
32 UNIQUE INDEX `idAddress_UNIQUE` (`idAddress` ASC))
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `mydb`.`Role`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `mydb`.`Role` (
40 `idRole` INT UNSIGNED NOT NULL AUTO_INCREMENT,
41 `description` VARCHAR(45) NULL,
42 PRIMARY KEY (`idRole`))
43ENGINE = InnoDB;
44
45
46-- -----------------------------------------------------
47-- Table `mydb`.`User`
48-- -----------------------------------------------------
49CREATE TABLE IF NOT EXISTS `mydb`.`User` (
50 `idUser` INT NOT NULL AUTO_INCREMENT,
51 `username` VARCHAR(45) NOT NULL,
52 `password` VARCHAR(250) NOT NULL,
53 `firstname` VARCHAR(45) NOT NULL,
54 `namePrefixes` VARCHAR(45) NULL,
55 `lastname` VARCHAR(45) NOT NULL,
56 `active` BIT NULL,
57 `Address_idAddress` INT NOT NULL,
58 `availableAfter` DATETIME NULL,
59 `Role_idRole` INT UNSIGNED NOT NULL,
60 `Image` BLOB NULL,
61 `StartDate` DATETIME NULL,
62 PRIMARY KEY (`idUser`),
63 UNIQUE INDEX `idUsers_UNIQUE` (`idUser` ASC),
64 UNIQUE INDEX `username_UNIQUE` (`username` ASC),
65 INDEX `fk_Users_Address_idx` (`Address_idAddress` ASC),
66 INDEX `fk_User_Role1_idx` (`Role_idRole` ASC),
67 CONSTRAINT `fk_Users_Address`
68 FOREIGN KEY (`Address_idAddress`)
69 REFERENCES `mydb`.`Address` (`idAddress`)
70 ON DELETE NO ACTION
71 ON UPDATE NO ACTION,
72 CONSTRAINT `fk_User_Role1`
73 FOREIGN KEY (`Role_idRole`)
74 REFERENCES `mydb`.`Role` (`idRole`)
75 ON DELETE NO ACTION
76 ON UPDATE NO ACTION)
77ENGINE = InnoDB;
78
79
80-- -----------------------------------------------------
81-- Table `mydb`.`Region`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `mydb`.`Region` (
84 `idregion` INT NOT NULL AUTO_INCREMENT,
85 `description` VARCHAR(80) NOT NULL,
86 PRIMARY KEY (`idregion`),
87 UNIQUE INDEX `idregion_UNIQUE` (`idregion` ASC),
88 UNIQUE INDEX `description_UNIQUE` (`description` ASC))
89ENGINE = InnoDB;
90
91
92-- -----------------------------------------------------
93-- Table `mydb`.`KT_Region_User`
94-- -----------------------------------------------------
95CREATE TABLE IF NOT EXISTS `mydb`.`KT_Region_User` (
96 `Region_idregion` INT NOT NULL,
97 `Users_idUsers` INT NOT NULL,
98 PRIMARY KEY (`Region_idregion`, `Users_idUsers`),
99 INDEX `fk_KT_Region_Users_Users1_idx` (`Users_idUsers` ASC),
100 CONSTRAINT `fk_KT_Region_Users_Region1`
101 FOREIGN KEY (`Region_idregion`)
102 REFERENCES `mydb`.`Region` (`idregion`)
103 ON DELETE NO ACTION
104 ON UPDATE NO ACTION,
105 CONSTRAINT `fk_KT_Region_Users_Users1`
106 FOREIGN KEY (`Users_idUsers`)
107 REFERENCES `mydb`.`User` (`idUser`)
108 ON DELETE NO ACTION
109 ON UPDATE NO ACTION)
110ENGINE = InnoDB;
111
112
113-- -----------------------------------------------------
114-- Table `mydb`.`ParkingLotType`
115-- -----------------------------------------------------
116CREATE TABLE IF NOT EXISTS `mydb`.`ParkingLotType` (
117 `idParkingLotType` INT NOT NULL AUTO_INCREMENT,
118 `description` VARCHAR(80) NOT NULL,
119 PRIMARY KEY (`idParkingLotType`),
120 UNIQUE INDEX `idParkingLotType_UNIQUE` (`idParkingLotType` ASC))
121ENGINE = InnoDB;
122
123
124-- -----------------------------------------------------
125-- Table `mydb`.`Reason`
126-- -----------------------------------------------------
127CREATE TABLE IF NOT EXISTS `mydb`.`Reason` (
128 `idReason` INT NOT NULL AUTO_INCREMENT,
129 `reasonCode` VARCHAR(20) NOT NULL,
130 `description` VARCHAR(200) NOT NULL,
131 PRIMARY KEY (`idReason`),
132 UNIQUE INDEX `idReason_UNIQUE` (`idReason` ASC))
133ENGINE = InnoDB;
134
135
136-- -----------------------------------------------------
137-- Table `mydb`.`Survey`
138-- -----------------------------------------------------
139CREATE TABLE IF NOT EXISTS `mydb`.`Survey` (
140 `idQuestionnaire` INT NOT NULL AUTO_INCREMENT,
141 `version` INT NOT NULL,
142 `name` VARCHAR(100) NOT NULL,
143 `edited` BIT NULL,
144 `ParkingLotType_idParkingLotType` INT NOT NULL,
145 PRIMARY KEY (`idQuestionnaire`, `version`),
146 UNIQUE INDEX `idQuestionnaire_UNIQUE` (`idQuestionnaire` ASC),
147 UNIQUE INDEX `version_UNIQUE` (`version` ASC),
148 INDEX `fk_Survey_ParkingLotType1_idx` (`ParkingLotType_idParkingLotType` ASC),
149 CONSTRAINT `fk_Survey_ParkingLotType1`
150 FOREIGN KEY (`ParkingLotType_idParkingLotType`)
151 REFERENCES `mydb`.`ParkingLotType` (`idParkingLotType`)
152 ON DELETE NO ACTION
153 ON UPDATE NO ACTION)
154ENGINE = InnoDB;
155
156
157-- -----------------------------------------------------
158-- Table `mydb`.`Status`
159-- -----------------------------------------------------
160CREATE TABLE IF NOT EXISTS `mydb`.`Status` (
161 `idStatus` INT NOT NULL AUTO_INCREMENT,
162 `description` VARCHAR(60) NOT NULL,
163 PRIMARY KEY (`idStatus`),
164 UNIQUE INDEX `idStatus_UNIQUE` (`idStatus` ASC))
165ENGINE = InnoDB;
166
167
168-- -----------------------------------------------------
169-- Table `mydb`.`Frequency`
170-- -----------------------------------------------------
171CREATE TABLE IF NOT EXISTS `mydb`.`Frequency` (
172 `idFrequency` INT NOT NULL AUTO_INCREMENT,
173 `description` VARCHAR(45) NOT NULL,
174 PRIMARY KEY (`idFrequency`))
175ENGINE = InnoDB;
176
177
178-- -----------------------------------------------------
179-- Table `mydb`.`Client`
180-- -----------------------------------------------------
181CREATE TABLE IF NOT EXISTS `mydb`.`Client` (
182 `idClient` INT NOT NULL AUTO_INCREMENT,
183 `clientName` VARCHAR(200) NULL,
184 `clientCode` VARCHAR(10) NULL,
185 `clientContactPerson` VARCHAR(100) NULL,
186 `logo` BLOB NULL,
187 PRIMARY KEY (`idClient`),
188 UNIQUE INDEX `idClient_UNIQUE` (`idClient` ASC),
189 UNIQUE INDEX `clientCode_UNIQUE` (`clientCode` ASC))
190ENGINE = InnoDB;
191
192
193-- -----------------------------------------------------
194-- Table `mydb`.`locationType`
195-- -----------------------------------------------------
196CREATE TABLE IF NOT EXISTS `mydb`.`locationType` (
197 `type` VARCHAR(255) NOT NULL,
198 PRIMARY KEY (`type`))
199ENGINE = InnoDB;
200
201
202-- -----------------------------------------------------
203-- Table `mydb`.`location`
204-- -----------------------------------------------------
205CREATE TABLE IF NOT EXISTS `mydb`.`location` (
206 `idLocation` INT NOT NULL,
207 `Address_idAddress` INT NOT NULL,
208 `Client_idClient` INT NOT NULL,
209 `locationType_type` VARCHAR(255) NOT NULL,
210 PRIMARY KEY (`idLocation`),
211 INDEX `fk_location_Address1_idx` (`Address_idAddress` ASC),
212 INDEX `fk_location_Client1_idx` (`Client_idClient` ASC),
213 INDEX `fk_location_locationType1_idx` (`locationType_type` ASC),
214 CONSTRAINT `fk_location_Address1`
215 FOREIGN KEY (`Address_idAddress`)
216 REFERENCES `mydb`.`Address` (`idAddress`)
217 ON DELETE NO ACTION
218 ON UPDATE NO ACTION,
219 CONSTRAINT `fk_location_Client1`
220 FOREIGN KEY (`Client_idClient`)
221 REFERENCES `mydb`.`Client` (`idClient`)
222 ON DELETE NO ACTION
223 ON UPDATE NO ACTION,
224 CONSTRAINT `fk_location_locationType1`
225 FOREIGN KEY (`locationType_type`)
226 REFERENCES `mydb`.`locationType` (`type`)
227 ON DELETE NO ACTION
228 ON UPDATE NO ACTION)
229ENGINE = InnoDB;
230
231
232-- -----------------------------------------------------
233-- Table `mydb`.`Inspection`
234-- -----------------------------------------------------
235CREATE TABLE IF NOT EXISTS `mydb`.`Inspection` (
236 `idInspection` INT NOT NULL AUTO_INCREMENT,
237 `description` TEXT NULL,
238 `startDate` DATETIME NOT NULL,
239 `endDate` DATETIME NULL,
240 `Region_idregion` INT NOT NULL,
241 `ParkingLotType_idParkingLotType` INT NOT NULL,
242 `Reason_idReason` INT NOT NULL,
243 `Survey_idQuestionnaire` INT NOT NULL,
244 `Survey_version` INT NOT NULL,
245 `Status_idStatus` INT NOT NULL,
246 `amountOfSpots` INT NULL,
247 `amountOfSpotsForDisabled` INT NULL,
248 `Frequency_idFrequency` INT NOT NULL,
249 `location_idLocation` INT NOT NULL,
250 `expectedTime` TIME NULL,
251 PRIMARY KEY (`idInspection`),
252 INDEX `fk_Inspection_Region1_idx` (`Region_idregion` ASC),
253 INDEX `fk_Inspection_ParkingLotType1_idx` (`ParkingLotType_idParkingLotType` ASC),
254 INDEX `fk_Inspection_Reason1_idx` (`Reason_idReason` ASC),
255 INDEX `fk_Inspection_Survey1_idx` (`Survey_idQuestionnaire` ASC, `Survey_version` ASC),
256 INDEX `fk_Inspection_Status1_idx` (`Status_idStatus` ASC),
257 INDEX `fk_Inspection_Frequency1_idx` (`Frequency_idFrequency` ASC),
258 INDEX `fk_Inspection_location1_idx` (`location_idLocation` ASC),
259 CONSTRAINT `fk_Inspection_Region1`
260 FOREIGN KEY (`Region_idregion`)
261 REFERENCES `mydb`.`Region` (`idregion`)
262 ON DELETE NO ACTION
263 ON UPDATE NO ACTION,
264 CONSTRAINT `fk_Inspection_ParkingLotType1`
265 FOREIGN KEY (`ParkingLotType_idParkingLotType`)
266 REFERENCES `mydb`.`ParkingLotType` (`idParkingLotType`)
267 ON DELETE NO ACTION
268 ON UPDATE NO ACTION,
269 CONSTRAINT `fk_Inspection_Reason1`
270 FOREIGN KEY (`Reason_idReason`)
271 REFERENCES `mydb`.`Reason` (`idReason`)
272 ON DELETE NO ACTION
273 ON UPDATE NO ACTION,
274 CONSTRAINT `fk_Inspection_Survey1`
275 FOREIGN KEY (`Survey_idQuestionnaire` , `Survey_version`)
276 REFERENCES `mydb`.`Survey` (`idQuestionnaire` , `version`)
277 ON DELETE NO ACTION
278 ON UPDATE NO ACTION,
279 CONSTRAINT `fk_Inspection_Status1`
280 FOREIGN KEY (`Status_idStatus`)
281 REFERENCES `mydb`.`Status` (`idStatus`)
282 ON DELETE NO ACTION
283 ON UPDATE NO ACTION,
284 CONSTRAINT `fk_Inspection_Frequency1`
285 FOREIGN KEY (`Frequency_idFrequency`)
286 REFERENCES `mydb`.`Frequency` (`idFrequency`)
287 ON DELETE NO ACTION
288 ON UPDATE NO ACTION,
289 CONSTRAINT `fk_Inspection_location1`
290 FOREIGN KEY (`location_idLocation`)
291 REFERENCES `mydb`.`location` (`idLocation`)
292 ON DELETE NO ACTION
293 ON UPDATE NO ACTION)
294ENGINE = InnoDB;
295
296
297-- -----------------------------------------------------
298-- Table `mydb`.`Images`
299-- -----------------------------------------------------
300CREATE TABLE IF NOT EXISTS `mydb`.`Images` (
301 `idImages` INT NOT NULL AUTO_INCREMENT,
302 `image` BLOB NOT NULL,
303 `Inspection_idInspection` INT NOT NULL,
304 PRIMARY KEY (`idImages`),
305 UNIQUE INDEX `idImages_UNIQUE` (`idImages` ASC),
306 INDEX `fk_Images_Inspection1_idx` (`Inspection_idInspection` ASC),
307 CONSTRAINT `fk_Images_Inspection1`
308 FOREIGN KEY (`Inspection_idInspection`)
309 REFERENCES `mydb`.`Inspection` (`idInspection`)
310 ON DELETE NO ACTION
311 ON UPDATE NO ACTION)
312ENGINE = InnoDB;
313
314
315-- -----------------------------------------------------
316-- Table `mydb`.`QuestionType`
317-- -----------------------------------------------------
318CREATE TABLE IF NOT EXISTS `mydb`.`QuestionType` (
319 `idQuestionType` INT NULL AUTO_INCREMENT,
320 `questionType` VARCHAR(100) NOT NULL,
321 PRIMARY KEY (`idQuestionType`),
322 UNIQUE INDEX `idQuestionType_UNIQUE` (`idQuestionType` ASC))
323ENGINE = InnoDB;
324
325
326-- -----------------------------------------------------
327-- Table `mydb`.`SurveyQuestion`
328-- -----------------------------------------------------
329CREATE TABLE IF NOT EXISTS `mydb`.`SurveyQuestion` (
330 `Question_idQuestion` INT NOT NULL,
331 `Survey_idQuestionnaire` INT NOT NULL,
332 `Survey_version` INT NOT NULL,
333 `Question` VARCHAR(300) NOT NULL,
334 `QuestionType_idQuestionType` INT NOT NULL,
335 PRIMARY KEY (`Question_idQuestion`, `Survey_idQuestionnaire`, `Survey_version`),
336 INDEX `fk_KT_Survey_Question_Survey1_idx` (`Survey_idQuestionnaire` ASC, `Survey_version` ASC),
337 INDEX `fk_KT_Survey_Question_QuestionType1_idx` (`QuestionType_idQuestionType` ASC),
338 CONSTRAINT `fk_KT_Survey_Question_Survey1`
339 FOREIGN KEY (`Survey_idQuestionnaire` , `Survey_version`)
340 REFERENCES `mydb`.`Survey` (`idQuestionnaire` , `version`)
341 ON DELETE NO ACTION
342 ON UPDATE NO ACTION,
343 CONSTRAINT `fk_KT_Survey_Question_QuestionType1`
344 FOREIGN KEY (`QuestionType_idQuestionType`)
345 REFERENCES `mydb`.`QuestionType` (`idQuestionType`)
346 ON DELETE NO ACTION
347 ON UPDATE NO ACTION)
348ENGINE = InnoDB;
349
350
351-- -----------------------------------------------------
352-- Table `mydb`.`KT_SurveyQuestion_Answer`
353-- -----------------------------------------------------
354CREATE TABLE IF NOT EXISTS `mydb`.`KT_SurveyQuestion_Answer` (
355 `KT_ktSQ_Question_idQuestion` INT NOT NULL,
356 `KT_ktSQ_Survey_idQuestionnaire` INT NOT NULL,
357 `KT_ktSQ_Survey_version` INT NOT NULL,
358 `SuggestionAnswer_idGivenAnswer` INT NULL,
359 PRIMARY KEY (`KT_ktSQ_Question_idQuestion`, `KT_ktSQ_Survey_idQuestionnaire`, `KT_ktSQ_Survey_version`),
360 CONSTRAINT `fk_KT_Survey_Question_Answer_KT_Survey_Question1`
361 FOREIGN KEY (`KT_ktSQ_Question_idQuestion` , `KT_ktSQ_Survey_idQuestionnaire` , `KT_ktSQ_Survey_version`)
362 REFERENCES `mydb`.`SurveyQuestion` (`Question_idQuestion` , `Survey_idQuestionnaire` , `Survey_version`)
363 ON DELETE NO ACTION
364 ON UPDATE NO ACTION)
365ENGINE = InnoDB;
366
367
368-- -----------------------------------------------------
369-- Table `mydb`.`SuggestionAnswer`
370-- -----------------------------------------------------
371CREATE TABLE IF NOT EXISTS `mydb`.`SuggestionAnswer` (
372 `idGivenAnswer` INT NOT NULL AUTO_INCREMENT,
373 `description` VARCHAR(150) NOT NULL,
374 `KT_SurveyQuestion_Answer_KT_ktSQ_Question_idQuestion` INT NOT NULL,
375 `KT_SurveyQuestion_Answer_KT_ktSQ_Survey_idQuestionnaire` INT NOT NULL,
376 `KT_SurveyQuestion_Answer_KT_ktSQ_Survey_version` INT NOT NULL,
377 PRIMARY KEY (`idGivenAnswer`),
378 UNIQUE INDEX `idGivenAnswer_UNIQUE` (`idGivenAnswer` ASC),
379 INDEX `fk_SuggestionAnswer_KT_SurveyQuestion_Answer1_idx` (`KT_SurveyQuestion_Answer_KT_ktSQ_Question_idQuestion` ASC, `KT_SurveyQuestion_Answer_KT_ktSQ_Survey_idQuestionnaire` ASC, `KT_SurveyQuestion_Answer_KT_ktSQ_Survey_version` ASC),
380 CONSTRAINT `fk_SuggestionAnswer_KT_SurveyQuestion_Answer1`
381 FOREIGN KEY (`KT_SurveyQuestion_Answer_KT_ktSQ_Question_idQuestion` , `KT_SurveyQuestion_Answer_KT_ktSQ_Survey_idQuestionnaire` , `KT_SurveyQuestion_Answer_KT_ktSQ_Survey_version`)
382 REFERENCES `mydb`.`KT_SurveyQuestion_Answer` (`KT_ktSQ_Question_idQuestion` , `KT_ktSQ_Survey_idQuestionnaire` , `KT_ktSQ_Survey_version`)
383 ON DELETE NO ACTION
384 ON UPDATE NO ACTION)
385ENGINE = InnoDB;
386
387
388-- -----------------------------------------------------
389-- Table `mydb`.`Answer`
390-- -----------------------------------------------------
391CREATE TABLE IF NOT EXISTS `mydb`.`Answer` (
392 `KT_ktSQ_Answer_KT_ktSQ_Question_idQuestion` INT NOT NULL,
393 `KT_ktSQ_Answer_idQuestionnaire` INT NOT NULL,
394 `KT_ktSQ_Answer_KT_ktSQ_Survey_version` INT NOT NULL,
395 `answer` VARCHAR(200) NULL,
396 `SuggestionAnswer_idGivenAnswer` INT NULL,
397 PRIMARY KEY (`KT_ktSQ_Answer_KT_ktSQ_Question_idQuestion`, `KT_ktSQ_Answer_idQuestionnaire`, `KT_ktSQ_Answer_KT_ktSQ_Survey_version`),
398 INDEX `fk_Answer_SuggestionAnswer1_idx` (`SuggestionAnswer_idGivenAnswer` ASC),
399 CONSTRAINT `fk_Answer_KT_ktSQ_Answer1`
400 FOREIGN KEY (`KT_ktSQ_Answer_KT_ktSQ_Question_idQuestion` , `KT_ktSQ_Answer_idQuestionnaire` , `KT_ktSQ_Answer_KT_ktSQ_Survey_version`)
401 REFERENCES `mydb`.`KT_SurveyQuestion_Answer` (`KT_ktSQ_Question_idQuestion` , `KT_ktSQ_Survey_idQuestionnaire` , `KT_ktSQ_Survey_version`)
402 ON DELETE NO ACTION
403 ON UPDATE NO ACTION,
404 CONSTRAINT `fk_Answer_SuggestionAnswer1`
405 FOREIGN KEY (`SuggestionAnswer_idGivenAnswer`)
406 REFERENCES `mydb`.`SuggestionAnswer` (`idGivenAnswer`)
407 ON DELETE NO ACTION
408 ON UPDATE NO ACTION)
409ENGINE = InnoDB;
410
411
412-- -----------------------------------------------------
413-- Table `mydb`.`KT_Inspection_Users`
414-- -----------------------------------------------------
415CREATE TABLE IF NOT EXISTS `mydb`.`KT_Inspection_Users` (
416 `Inspection_idInspection` INT NOT NULL,
417 `Users_idUsers` INT NOT NULL,
418 `exspectedTime` TIME NULL,
419 INDEX `fk_KT_Inspection_Users_Inspection1_idx` (`Inspection_idInspection` ASC),
420 INDEX `fk_KT_Inspection_Users_Users1_idx` (`Users_idUsers` ASC),
421 PRIMARY KEY (`Inspection_idInspection`, `Users_idUsers`),
422 CONSTRAINT `fk_KT_Inspection_Users_Inspection1`
423 FOREIGN KEY (`Inspection_idInspection`)
424 REFERENCES `mydb`.`Inspection` (`idInspection`)
425 ON DELETE NO ACTION
426 ON UPDATE NO ACTION,
427 CONSTRAINT `fk_KT_Inspection_Users_Users1`
428 FOREIGN KEY (`Users_idUsers`)
429 REFERENCES `mydb`.`User` (`idUser`)
430 ON DELETE NO ACTION
431 ON UPDATE NO ACTION)
432ENGINE = InnoDB;
433
434
435-- -----------------------------------------------------
436-- Table `mydb`.`Inspection_times`
437-- -----------------------------------------------------
438CREATE TABLE IF NOT EXISTS `mydb`.`Inspection_times` (
439 `Inspection_idInspection` INT NOT NULL,
440 `inspection_time` DATETIME NULL,
441 `Users_idUsers` INT NOT NULL,
442 INDEX `fk_TimeTODODENNIS_Inspection1_idx` (`Inspection_idInspection` ASC),
443 INDEX `fk_Inspection_times_Users1_idx` (`Users_idUsers` ASC),
444 CONSTRAINT `fk_TimeTODODENNIS_Inspection1`
445 FOREIGN KEY (`Inspection_idInspection`)
446 REFERENCES `mydb`.`Inspection` (`idInspection`)
447 ON DELETE NO ACTION
448 ON UPDATE NO ACTION,
449 CONSTRAINT `fk_Inspection_times_Users1`
450 FOREIGN KEY (`Users_idUsers`)
451 REFERENCES `mydb`.`User` (`idUser`)
452 ON DELETE NO ACTION
453 ON UPDATE NO ACTION)
454ENGINE = InnoDB;
455
456
457-- -----------------------------------------------------
458-- Table `mydb`.`address_type`
459-- -----------------------------------------------------
460CREATE TABLE IF NOT EXISTS `mydb`.`address_type` (
461 `type` VARCHAR(255) NOT NULL,
462 PRIMARY KEY (`type`))
463ENGINE = InnoDB;
464
465
466-- -----------------------------------------------------
467-- Table `mydb`.`Client_has_Address`
468-- -----------------------------------------------------
469CREATE TABLE IF NOT EXISTS `mydb`.`Client_has_Address` (
470 `Client_idClient` INT NOT NULL,
471 `Address_idAddress` INT NOT NULL,
472 `address_type_type` VARCHAR(255) NOT NULL,
473 PRIMARY KEY (`Client_idClient`, `Address_idAddress`),
474 INDEX `fk_Client_has_Address_Address1_idx` (`Address_idAddress` ASC),
475 INDEX `fk_Client_has_Address_Client1_idx` (`Client_idClient` ASC),
476 INDEX `fk_Client_has_Address_address_type1_idx` (`address_type_type` ASC),
477 CONSTRAINT `fk_Client_has_Address_Client1`
478 FOREIGN KEY (`Client_idClient`)
479 REFERENCES `mydb`.`Client` (`idClient`)
480 ON DELETE NO ACTION
481 ON UPDATE NO ACTION,
482 CONSTRAINT `fk_Client_has_Address_Address1`
483 FOREIGN KEY (`Address_idAddress`)
484 REFERENCES `mydb`.`Address` (`idAddress`)
485 ON DELETE NO ACTION
486 ON UPDATE NO ACTION,
487 CONSTRAINT `fk_Client_has_Address_address_type1`
488 FOREIGN KEY (`address_type_type`)
489 REFERENCES `mydb`.`address_type` (`type`)
490 ON DELETE NO ACTION
491 ON UPDATE NO ACTION)
492ENGINE = InnoDB;
493
494
495-- -----------------------------------------------------
496-- Table `mydb`.`inspectionNotes`
497-- -----------------------------------------------------
498CREATE TABLE IF NOT EXISTS `mydb`.`inspectionNotes` (
499 `idNote` INT NOT NULL,
500 `note` TEXT(5000) NULL,
501 `resolved` TINYINT(1) NULL,
502 `resolvedBy` INT NOT NULL,
503 `Inspection_idInspection` INT NOT NULL,
504 PRIMARY KEY (`idNote`),
505 INDEX `fk_inspectionNotes_User1_idx` (`resolvedBy` ASC),
506 INDEX `fk_inspectionNotes_Inspection1_idx` (`Inspection_idInspection` ASC),
507 CONSTRAINT `fk_inspectionNotes_User1`
508 FOREIGN KEY (`resolvedBy`)
509 REFERENCES `mydb`.`User` (`idUser`)
510 ON DELETE NO ACTION
511 ON UPDATE NO ACTION,
512 CONSTRAINT `fk_inspectionNotes_Inspection1`
513 FOREIGN KEY (`Inspection_idInspection`)
514 REFERENCES `mydb`.`Inspection` (`idInspection`)
515 ON DELETE NO ACTION
516 ON UPDATE NO ACTION)
517ENGINE = InnoDB;
518
519
520SET SQL_MODE=@OLD_SQL_MODE;
521SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
522SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;