· 9 years ago · Oct 09, 2016, 05:28 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 sundebygninger
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema sundebygninger
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `sundebygninger` DEFAULT CHARACTER SET utf8 ;
15USE `sundebygninger` ;
16
17-- -----------------------------------------------------
18-- Table `sundebygninger`.`user`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `sundebygninger`.`user` (
21 `idUser` INT NOT NULL AUTO_INCREMENT,
22 `email` VARCHAR(45) NOT NULL,
23 `password` VARCHAR(45) NOT NULL,
24 `businessName` VARCHAR(45) NULL,
25 `phone` VARCHAR(45) NULL,
26 `status` VARCHAR(45) NOT NULL,
27 `fullName` VARCHAR(45) NULL,
28 `createdDate` DATETIME NOT NULL,
29 PRIMARY KEY (`idUser`),
30 UNIQUE INDEX `email_UNIQUE` (`email` ASC))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `sundebygninger`.`picture`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `sundebygninger`.`picture` (
38 `idPicture` INT NOT NULL AUTO_INCREMENT,
39 `description` VARCHAR(500) NULL,
40 `type` VARCHAR(45) NULL,
41 `image` VARCHAR(255) NOT NULL,
42 `fk_idBuilding` INT NOT NULL,
43 PRIMARY KEY (`idPicture`),
44 INDEX `fk_picture_building1_idx` (`fk_idBuilding` ASC),
45 UNIQUE INDEX `image_UNIQUE` (`image` ASC),
46 CONSTRAINT `fk_picture_building1`
47 FOREIGN KEY (`fk_idBuilding`)
48 REFERENCES `sundebygninger`.`building` (`idBuilding`)
49 ON DELETE NO ACTION
50 ON UPDATE NO ACTION)
51ENGINE = InnoDB;
52
53
54-- -----------------------------------------------------
55-- Table `sundebygninger`.`report`
56-- -----------------------------------------------------
57CREATE TABLE IF NOT EXISTS `sundebygninger`.`report` (
58 `idReport` INT NOT NULL AUTO_INCREMENT,
59 `buildingUsage` VARCHAR(500) NULL,
60 `roofRemarks` TINYINT(1) NOT NULL,
61 `fk_idPictureRoof` INT NULL,
62 `roofText` VARCHAR(500) NULL,
63 `outerWallRemarks` TINYINT(1) NOT NULL,
64 `fk_idPictureOuterRoof` INT NULL,
65 `outerWallText` VARCHAR(500) NULL,
66 `fk_idEmployee` INT NOT NULL,
67 `buildingResponsible` VARCHAR(45) NULL,
68 `dateCreated` DATETIME NOT NULL,
69 PRIMARY KEY (`idReport`),
70 INDEX `fk_report_picture1_idx` (`fk_idPictureRoof` ASC),
71 INDEX `fk_report_picture2_idx` (`fk_idPictureOuterRoof` ASC),
72 INDEX `fk_report_user1_idx` (`fk_idEmployee` ASC),
73 CONSTRAINT `fk_report_picture1`
74 FOREIGN KEY (`fk_idPictureRoof`)
75 REFERENCES `sundebygninger`.`picture` (`idPicture`)
76 ON DELETE NO ACTION
77 ON UPDATE NO ACTION,
78 CONSTRAINT `fk_report_picture2`
79 FOREIGN KEY (`fk_idPictureOuterRoof`)
80 REFERENCES `sundebygninger`.`picture` (`idPicture`)
81 ON DELETE NO ACTION
82 ON UPDATE NO ACTION,
83 CONSTRAINT `fk_report_user1`
84 FOREIGN KEY (`fk_idEmployee`)
85 REFERENCES `sundebygninger`.`user` (`idUser`)
86 ON DELETE NO ACTION
87 ON UPDATE NO ACTION)
88ENGINE = InnoDB;
89
90
91-- -----------------------------------------------------
92-- Table `sundebygninger`.`building`
93-- -----------------------------------------------------
94CREATE TABLE IF NOT EXISTS `sundebygninger`.`building` (
95 `idBuilding` INT NOT NULL AUTO_INCREMENT,
96 `address` VARCHAR(45) NOT NULL,
97 `cadastral` VARCHAR(45) NULL,
98 `builtYear` VARCHAR(45) NULL,
99 `area` VARCHAR(45) NULL,
100 `zipcode` VARCHAR(45) NOT NULL,
101 `city` VARCHAR(45) NOT NULL,
102 `condition` VARCHAR(45) NULL,
103 `service` VARCHAR(45) NULL,
104 `extraText` VARCHAR(2500) NULL,
105 `dateCreated` DATETIME NOT NULL,
106 `fk_idUser` INT NOT NULL,
107 `fk_idMainPicture` INT NULL,
108 `fk_idReport` INT NULL,
109 PRIMARY KEY (`idBuilding`),
110 INDEX `fk_building_user_idx` (`fk_idUser` ASC),
111 INDEX `fk_building_picture1_idx` (`fk_idMainPicture` ASC),
112 INDEX `fk_building_report1_idx` (`fk_idReport` ASC),
113 CONSTRAINT `fk_building_user`
114 FOREIGN KEY (`fk_idUser`)
115 REFERENCES `sundebygninger`.`user` (`idUser`)
116 ON DELETE NO ACTION
117 ON UPDATE NO ACTION,
118 CONSTRAINT `fk_building_picture1`
119 FOREIGN KEY (`fk_idMainPicture`)
120 REFERENCES `sundebygninger`.`picture` (`idPicture`)
121 ON DELETE NO ACTION
122 ON UPDATE NO ACTION,
123 CONSTRAINT `fk_building_report1`
124 FOREIGN KEY (`fk_idReport`)
125 REFERENCES `sundebygninger`.`report` (`idReport`)
126 ON DELETE NO ACTION
127 ON UPDATE NO ACTION)
128ENGINE = InnoDB;
129
130
131-- -----------------------------------------------------
132-- Table `sundebygninger`.`room`
133-- -----------------------------------------------------
134CREATE TABLE IF NOT EXISTS `sundebygninger`.`room` (
135 `idRoom` INT NOT NULL AUTO_INCREMENT,
136 `remarks` TINYINT(1) NOT NULL,
137 `damage` TINYINT(1) NULL,
138 `damageDate` DATETIME NULL,
139 `damageWhere` VARCHAR(500) NULL,
140 `damageHappened` VARCHAR(500) NULL,
141 `roomcol` VARCHAR(45) NULL,
142 `damageRepaired` VARCHAR(500) NULL,
143 `damageWater` TINYINT(1) NULL,
144 `damageRot` TINYINT(1) NULL,
145 `damageMold` TINYINT(1) NULL,
146 `damageFire` TINYINT(1) NULL,
147 `damageOther` VARCHAR(45) NULL,
148 `wallRemark` TINYINT(1) NULL,
149 `wallText` VARCHAR(500) NULL,
150 `fk_idWallPicture` INT NOT NULL,
151 `roofRemark` TINYINT(1) NULL,
152 `roofText` VARCHAR(500) NULL,
153 `fk_idRoofPicture` INT NOT NULL,
154 `floorRemark` TINYINT(1) NULL,
155 `floorText` VARCHAR(500) NULL,
156 `fk_idFloorPicture` INT NOT NULL,
157 `moistureScan` TINYINT(1) NULL,
158 `moistureScanText` VARCHAR(500) NULL,
159 `moistureScanMeasured` VARCHAR(45) NULL,
160 `conclusionText` VARCHAR(500) NULL,
161 `fk_idReport` INT NOT NULL,
162 PRIMARY KEY (`idRoom`),
163 INDEX `fk_room_picture1_idx` (`fk_idWallPicture` ASC),
164 INDEX `fk_room_picture2_idx` (`fk_idRoofPicture` ASC),
165 INDEX `fk_room_picture3_idx` (`fk_idFloorPicture` ASC),
166 INDEX `fk_room_report1_idx` (`fk_idReport` ASC),
167 CONSTRAINT `fk_room_picture1`
168 FOREIGN KEY (`fk_idWallPicture`)
169 REFERENCES `sundebygninger`.`picture` (`idPicture`)
170 ON DELETE NO ACTION
171 ON UPDATE NO ACTION,
172 CONSTRAINT `fk_room_picture2`
173 FOREIGN KEY (`fk_idRoofPicture`)
174 REFERENCES `sundebygninger`.`picture` (`idPicture`)
175 ON DELETE NO ACTION
176 ON UPDATE NO ACTION,
177 CONSTRAINT `fk_room_picture3`
178 FOREIGN KEY (`fk_idFloorPicture`)
179 REFERENCES `sundebygninger`.`picture` (`idPicture`)
180 ON DELETE NO ACTION
181 ON UPDATE NO ACTION,
182 CONSTRAINT `fk_room_report1`
183 FOREIGN KEY (`fk_idReport`)
184 REFERENCES `sundebygninger`.`report` (`idReport`)
185 ON DELETE NO ACTION
186 ON UPDATE NO ACTION)
187ENGINE = InnoDB;
188
189
190SET SQL_MODE=@OLD_SQL_MODE;
191SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
192SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;