· 9 years ago · Oct 08, 2016, 02:50 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 DEFAULT GETDATE(),
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` BLOB 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 PRIMARY KEY (`idReport`),
69 INDEX `fk_report_picture1_idx` (`fk_idPictureRoof` ASC),
70 INDEX `fk_report_picture2_idx` (`fk_idPictureOuterRoof` ASC),
71 INDEX `fk_report_user1_idx` (`fk_idEmployee` ASC),
72 CONSTRAINT `fk_report_picture1`
73 FOREIGN KEY (`fk_idPictureRoof`)
74 REFERENCES `sundebygninger`.`picture` (`idPicture`)
75 ON DELETE NO ACTION
76 ON UPDATE NO ACTION,
77 CONSTRAINT `fk_report_picture2`
78 FOREIGN KEY (`fk_idPictureOuterRoof`)
79 REFERENCES `sundebygninger`.`picture` (`idPicture`)
80 ON DELETE NO ACTION
81 ON UPDATE NO ACTION,
82 CONSTRAINT `fk_report_user1`
83 FOREIGN KEY (`fk_idEmployee`)
84 REFERENCES `sundebygninger`.`user` (`idUser`)
85 ON DELETE NO ACTION
86 ON UPDATE NO ACTION)
87ENGINE = InnoDB;
88
89
90-- -----------------------------------------------------
91-- Table `sundebygninger`.`building`
92-- -----------------------------------------------------
93CREATE TABLE IF NOT EXISTS `sundebygninger`.`building` (
94 `idBuilding` INT NOT NULL AUTO_INCREMENT,
95 `address` VARCHAR(45) NOT NULL,
96 `cadastral` VARCHAR(45) NULL,
97 `builtYear` VARCHAR(45) NULL,
98 `area` VARCHAR(45) NULL,
99 `zipcode` VARCHAR(45) NOT NULL,
100 `city` VARCHAR(45) NOT NULL,
101 `condition` VARCHAR(45) NULL,
102 `service` VARCHAR(45) NULL,
103 `extraText` VARCHAR(2500) NULL,
104 `dateCreated` DATETIME NOT NULL DEFAULT GETDATE(),
105 `fk_idUser` INT NOT NULL,
106 `fk_idMainPicture` INT NULL,
107 `fk_idReport` INT NULL,
108 PRIMARY KEY (`idBuilding`),
109 INDEX `fk_building_user_idx` (`fk_idUser` ASC),
110 INDEX `fk_building_picture1_idx` (`fk_idMainPicture` ASC),
111 INDEX `fk_building_report1_idx` (`fk_idReport` ASC),
112 CONSTRAINT `fk_building_user`
113 FOREIGN KEY (`fk_idUser`)
114 REFERENCES `sundebygninger`.`user` (`idUser`)
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION,
117 CONSTRAINT `fk_building_picture1`
118 FOREIGN KEY (`fk_idMainPicture`)
119 REFERENCES `sundebygninger`.`picture` (`idPicture`)
120 ON DELETE NO ACTION
121 ON UPDATE NO ACTION,
122 CONSTRAINT `fk_building_report1`
123 FOREIGN KEY (`fk_idReport`)
124 REFERENCES `sundebygninger`.`report` (`idReport`)
125 ON DELETE NO ACTION
126 ON UPDATE NO ACTION)
127ENGINE = InnoDB;
128
129
130-- -----------------------------------------------------
131-- Table `sundebygninger`.`room`
132-- -----------------------------------------------------
133CREATE TABLE IF NOT EXISTS `sundebygninger`.`room` (
134 `idRoom` INT NOT NULL AUTO_INCREMENT,
135 `remarks` TINYINT(1) NOT NULL,
136 `damage` TINYINT(1) NULL,
137 `damageDate` DATETIME NULL,
138 `damageWhere` VARCHAR(500) NULL,
139 `damageHappened` VARCHAR(500) NULL,
140 `roomcol` VARCHAR(45) NULL,
141 `damageRepaired` VARCHAR(500) NULL,
142 `damageWater` TINYINT(1) NULL,
143 `damageRot` TINYINT(1) NULL,
144 `damageMold` TINYINT(1) NULL,
145 `damageFire` TINYINT(1) NULL,
146 `damageOther` VARCHAR(45) NULL,
147 `wallRemark` TINYINT(1) NULL,
148 `wallText` VARCHAR(500) NULL,
149 `fk_idWallPicture` INT NOT NULL,
150 `roofRemark` TINYINT(1) NULL,
151 `roofText` VARCHAR(500) NULL,
152 `fk_idRoofPicture` INT NOT NULL,
153 `floorRemark` TINYINT(1) NULL,
154 `floorText` VARCHAR(500) NULL,
155 `fk_idFloorPicture` INT NOT NULL,
156 `moistureScan` TINYINT(1) NULL,
157 `moistureScanText` VARCHAR(500) NULL,
158 `moistureScanMeasured` VARCHAR(45) NULL,
159 `conclusionText` VARCHAR(500) NULL,
160 `fk_idReport` INT NOT NULL,
161 PRIMARY KEY (`idRoom`),
162 INDEX `fk_room_picture1_idx` (`fk_idWallPicture` ASC),
163 INDEX `fk_room_picture2_idx` (`fk_idRoofPicture` ASC),
164 INDEX `fk_room_picture3_idx` (`fk_idFloorPicture` ASC),
165 INDEX `fk_room_report1_idx` (`fk_idReport` ASC),
166 CONSTRAINT `fk_room_picture1`
167 FOREIGN KEY (`fk_idWallPicture`)
168 REFERENCES `sundebygninger`.`picture` (`idPicture`)
169 ON DELETE NO ACTION
170 ON UPDATE NO ACTION,
171 CONSTRAINT `fk_room_picture2`
172 FOREIGN KEY (`fk_idRoofPicture`)
173 REFERENCES `sundebygninger`.`picture` (`idPicture`)
174 ON DELETE NO ACTION
175 ON UPDATE NO ACTION,
176 CONSTRAINT `fk_room_picture3`
177 FOREIGN KEY (`fk_idFloorPicture`)
178 REFERENCES `sundebygninger`.`picture` (`idPicture`)
179 ON DELETE NO ACTION
180 ON UPDATE NO ACTION,
181 CONSTRAINT `fk_room_report1`
182 FOREIGN KEY (`fk_idReport`)
183 REFERENCES `sundebygninger`.`report` (`idReport`)
184 ON DELETE NO ACTION
185 ON UPDATE NO ACTION)
186ENGINE = InnoDB;
187
188
189SET SQL_MODE=@OLD_SQL_MODE;
190SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
191SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;