· 9 years ago · Oct 12, 2016, 12:12 PM
1-- MySQL Script generated by MySQL Workbench
2-- 10/12/16 14:05:34
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6
7
8-- -----------------------------------------------------
9-- Schema narwal
10-- -----------------------------------------------------
11
12
13-- -----------------------------------------------------
14-- Table `narwal`.`role`
15-- -----------------------------------------------------
16CREATE TABLE IF NOT EXISTS `narwal`.`role` (
17 `role_id` VARCHAR(36) NOT NULL,
18 `role_name` VARCHAR(45) NOT NULL,
19 `role_may_moderate` TINYINT NOT NULL,
20 `role_may_administrate` TINYINT NOT NULL,
21 `role_may_view_agenda` TINYINT NOT NULL,
22 `role_may_view_companies` TINYINT NOT NULL,
23 PRIMARY KEY (`role_id`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `narwal`.`person`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `narwal`.`person` (
31 `pers_id` VARCHAR(36) NOT NULL,
32 `pers_name` VARCHAR(45) NOT NULL,
33 `pers_prefix` VARCHAR(15) NULL,
34 `pers_surname` VARCHAR(45) NOT NULL,
35 `pers_street` VARCHAR(64) NULL,
36 `pers_house_num` INT NULL,
37 `pers_city` VARCHAR(64) NULL,
38 `pers_country` VARCHAR(64) NULL,
39 `pers_email` VARCHAR(254) NOT NULL,
40 `pers_birthdate` DATE NULL,
41 `role_id` VARCHAR(36) NOT NULL,
42 `disabled` TINYINT(1) NULL,
43 PRIMARY KEY (`pers_id`),
44 INDEX `fk_role_idx` (`role_id` ASC),
45 CONSTRAINT `fk_p_role`
46 FOREIGN KEY (`role_id`)
47 REFERENCES `narwal`.`role` (`role_id`)
48 ON DELETE RESTRICT
49 ON UPDATE CASCADE)
50ENGINE = InnoDB;
51
52
53-- -----------------------------------------------------
54-- Table `narwal`.`login`
55-- -----------------------------------------------------
56CREATE TABLE IF NOT EXISTS `narwal`.`login` (
57 `pers_id` VARCHAR(36) NOT NULL,
58 `login_username` VARCHAR(45) NOT NULL,
59 `login_password_hash` VARCHAR(64) NOT NULL,
60 PRIMARY KEY (`login_username`, `pers_id`),
61 UNIQUE INDEX `pers_id_UNIQUE` (`pers_id` ASC),
62 UNIQUE INDEX `login_username_UNIQUE` (`login_username` ASC),
63 CONSTRAINT `fk_l_persoon`
64 FOREIGN KEY (`pers_id`)
65 REFERENCES `narwal`.`person` (`pers_id`)
66 ON DELETE CASCADE
67 ON UPDATE CASCADE)
68ENGINE = InnoDB;
69
70
71-- -----------------------------------------------------
72-- Table `narwal`.`media`
73-- -----------------------------------------------------
74CREATE TABLE IF NOT EXISTS `narwal`.`media` (
75 `med_name` VARCHAR(45) NOT NULL,
76 PRIMARY KEY (`med_name`))
77ENGINE = InnoDB;
78
79
80-- -----------------------------------------------------
81-- Table `narwal`.`person_media`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `narwal`.`person_media` (
84 `pers_id` VARCHAR(36) NOT NULL,
85 `med_name` VARCHAR(45) NOT NULL,
86 `med_url` VARCHAR(254) NOT NULL,
87 INDEX `fk_media_idx` (`med_name` ASC),
88 PRIMARY KEY (`med_url`, `pers_id`, `med_name`),
89 CONSTRAINT `fk_pm_person`
90 FOREIGN KEY (`pers_id`)
91 REFERENCES `narwal`.`person` (`pers_id`)
92 ON DELETE CASCADE
93 ON UPDATE CASCADE,
94 CONSTRAINT `fk_pm_media`
95 FOREIGN KEY (`med_name`)
96 REFERENCES `narwal`.`media` (`med_name`)
97 ON DELETE CASCADE
98 ON UPDATE CASCADE)
99ENGINE = InnoDB;
100
101
102-- -----------------------------------------------------
103-- Table `narwal`.`note`
104-- -----------------------------------------------------
105CREATE TABLE IF NOT EXISTS `narwal`.`note` (
106 `note_id` VARCHAR(36) NOT NULL,
107 `manager_id` VARCHAR(36) NOT NULL,
108 `note_creation_date` DATETIME NOT NULL,
109 `note_title` VARCHAR(64) NOT NULL,
110 `note_content` TEXT NOT NULL,
111 `disabled` TINYINT(1) NULL,
112 INDEX `fk_manager_idx` (`manager_id` ASC),
113 PRIMARY KEY (`note_id`, `manager_id`),
114 CONSTRAINT `fk_n_manager`
115 FOREIGN KEY (`manager_id`)
116 REFERENCES `narwal`.`person` (`pers_id`)
117 ON DELETE CASCADE
118 ON UPDATE CASCADE)
119ENGINE = InnoDB;
120
121
122-- -----------------------------------------------------
123-- Table `narwal`.`file`
124-- -----------------------------------------------------
125CREATE TABLE IF NOT EXISTS `narwal`.`file` (
126 `note_id` VARCHAR(36) NOT NULL,
127 `file_name` VARCHAR(255) NOT NULL,
128 `file_type` VARCHAR(255) NOT NULL,
129 `file_data` BLOB NOT NULL,
130 `file_descr` TEXT NULL,
131 PRIMARY KEY (`file_name`, `file_type`, `note_id`),
132 INDEX `fk_note_idx` (`note_id` ASC),
133 CONSTRAINT `fk_note`
134 FOREIGN KEY (`note_id`)
135 REFERENCES `narwal`.`note` (`note_id`)
136 ON DELETE CASCADE
137 ON UPDATE CASCADE)
138ENGINE = InnoDB;
139
140
141-- -----------------------------------------------------
142-- Table `narwal`.`appointment`
143-- -----------------------------------------------------
144CREATE TABLE IF NOT EXISTS `narwal`.`appointment` (
145 `apm_id` VARCHAR(36) NOT NULL,
146 `apm_datetime` DATETIME NOT NULL,
147 `apm_description` TEXT NOT NULL,
148 `apm_location` VARCHAR(128) NULL,
149 `disabled` TINYINT(1) NULL,
150 `appointmentcol` VARCHAR(45) NULL,
151 PRIMARY KEY (`apm_id`))
152ENGINE = InnoDB;
153
154
155-- -----------------------------------------------------
156-- Table `narwal`.`appointment_person`
157-- -----------------------------------------------------
158CREATE TABLE IF NOT EXISTS `narwal`.`appointment_person` (
159 `apm_id` VARCHAR(36) NOT NULL,
160 `pers_id` VARCHAR(36) NOT NULL,
161 INDEX `fk_person_idx` (`pers_id` ASC),
162 PRIMARY KEY (`pers_id`, `apm_id`),
163 CONSTRAINT `fk_ap_apm`
164 FOREIGN KEY (`apm_id`)
165 REFERENCES `narwal`.`appointment` (`apm_id`)
166 ON DELETE CASCADE
167 ON UPDATE CASCADE,
168 CONSTRAINT `fk_ap_person`
169 FOREIGN KEY (`pers_id`)
170 REFERENCES `narwal`.`person` (`pers_id`)
171 ON DELETE NO ACTION
172 ON UPDATE CASCADE)
173ENGINE = InnoDB;
174
175
176-- -----------------------------------------------------
177-- Table `narwal`.`company`
178-- -----------------------------------------------------
179CREATE TABLE IF NOT EXISTS `narwal`.`company` (
180 `comp_id` VARCHAR(36) NOT NULL,
181 `comp_name` VARCHAR(64) NOT NULL,
182 `comp_street` VARCHAR(64) NOT NULL,
183 `comp_house_num` INT(6) NOT NULL,
184 `comp_city` VARCHAR(64) NOT NULL,
185 `comp_country` VARCHAR(64) NOT NULL,
186 `comp_phonenumber` VARCHAR(45) NOT NULL,
187 `comp_email` VARCHAR(254) NOT NULL,
188 `disabled` TINYINT(1) NULL,
189 PRIMARY KEY (`comp_id`))
190ENGINE = InnoDB;
191
192
193-- -----------------------------------------------------
194-- Table `narwal`.`company_contact`
195-- -----------------------------------------------------
196CREATE TABLE IF NOT EXISTS `narwal`.`company_contact` (
197 `comp_id` VARCHAR(36) NOT NULL,
198 `pers_id` VARCHAR(36) NOT NULL,
199 INDEX `fk_pers_idx` (`pers_id` ASC),
200 PRIMARY KEY (`comp_id`, `pers_id`),
201 CONSTRAINT `fk_cc_comp`
202 FOREIGN KEY (`comp_id`)
203 REFERENCES `narwal`.`company` (`comp_id`)
204 ON DELETE CASCADE
205 ON UPDATE CASCADE,
206 CONSTRAINT `fk_cc_pers`
207 FOREIGN KEY (`pers_id`)
208 REFERENCES `narwal`.`person` (`pers_id`)
209 ON DELETE CASCADE
210 ON UPDATE CASCADE)
211ENGINE = InnoDB;
212
213
214-- -----------------------------------------------------
215-- Table `narwal`.`tag`
216-- -----------------------------------------------------
217CREATE TABLE IF NOT EXISTS `narwal`.`tag` (
218 `tag_name` VARCHAR(64) NOT NULL,
219 `tag_description` TEXT NOT NULL,
220 PRIMARY KEY (`tag_name`))
221ENGINE = InnoDB;
222
223
224-- -----------------------------------------------------
225-- Table `narwal`.`tag_person`
226-- -----------------------------------------------------
227CREATE TABLE IF NOT EXISTS `narwal`.`tag_person` (
228 `tag_name` VARCHAR(64) NOT NULL,
229 `pers_id` VARCHAR(36) NOT NULL,
230 INDEX `fk_pers_idx` (`pers_id` ASC),
231 PRIMARY KEY (`tag_name`, `pers_id`),
232 CONSTRAINT `fk_tp_tag`
233 FOREIGN KEY (`tag_name`)
234 REFERENCES `narwal`.`tag` (`tag_name`)
235 ON DELETE CASCADE
236 ON UPDATE CASCADE,
237 CONSTRAINT `fk_tp_pers`
238 FOREIGN KEY (`pers_id`)
239 REFERENCES `narwal`.`person` (`pers_id`)
240 ON DELETE CASCADE
241 ON UPDATE CASCADE)
242ENGINE = InnoDB;
243
244
245-- -----------------------------------------------------
246-- Table `narwal`.`tag_company`
247-- -----------------------------------------------------
248CREATE TABLE IF NOT EXISTS `narwal`.`tag_company` (
249 `tag_name` VARCHAR(64) NOT NULL,
250 `comp_id` VARCHAR(36) NOT NULL,
251 INDEX `fk_comp_idx` (`comp_id` ASC),
252 PRIMARY KEY (`tag_name`, `comp_id`),
253 CONSTRAINT `fk_tc_tag`
254 FOREIGN KEY (`tag_name`)
255 REFERENCES `narwal`.`tag` (`tag_name`)
256 ON DELETE CASCADE
257 ON UPDATE CASCADE,
258 CONSTRAINT `fk_tc_comp`
259 FOREIGN KEY (`comp_id`)
260 REFERENCES `narwal`.`company` (`comp_id`)
261 ON DELETE CASCADE
262 ON UPDATE CASCADE)
263ENGINE = InnoDB;
264
265
266-- -----------------------------------------------------
267-- Table `narwal`.`company_client`
268-- -----------------------------------------------------
269CREATE TABLE IF NOT EXISTS `narwal`.`company_client` (
270 `comp_id` VARCHAR(36) NOT NULL,
271 `pers_id` VARCHAR(36) NOT NULL,
272 `note_id` VARCHAR(36) NOT NULL,
273 INDEX `fk_pers_idx` (`pers_id` ASC),
274 PRIMARY KEY (`comp_id`, `pers_id`),
275 INDEX `fk_note_idx` (`note_id` ASC),
276 CONSTRAINT `fk_ccl_comp`
277 FOREIGN KEY (`comp_id`)
278 REFERENCES `narwal`.`company` (`comp_id`)
279 ON DELETE CASCADE
280 ON UPDATE CASCADE,
281 CONSTRAINT `fk_ccl_pers`
282 FOREIGN KEY (`pers_id`)
283 REFERENCES `narwal`.`person` (`pers_id`)
284 ON DELETE CASCADE
285 ON UPDATE CASCADE,
286 CONSTRAINT `fk_ccl_note`
287 FOREIGN KEY (`note_id`)
288 REFERENCES `narwal`.`note` (`note_id`)
289 ON DELETE CASCADE
290 ON UPDATE CASCADE)
291ENGINE = InnoDB;
292
293
294-- -----------------------------------------------------
295-- Table `narwal`.`person_note`
296-- -----------------------------------------------------
297CREATE TABLE IF NOT EXISTS `narwal`.`person_note` (
298 `note_id` VARCHAR(36) NOT NULL,
299 `person_id` VARCHAR(36) NOT NULL,
300 PRIMARY KEY (`note_id`, `person_id`),
301 INDEX `fk_person_idx` (`person_id` ASC),
302 CONSTRAINT `fk_pn_note`
303 FOREIGN KEY (`note_id`)
304 REFERENCES `narwal`.`note` (`note_id`)
305 ON DELETE NO ACTION
306 ON UPDATE NO ACTION,
307 CONSTRAINT `fk_pn_person`
308 FOREIGN KEY (`person_id`)
309 REFERENCES `narwal`.`person` (`pers_id`)
310 ON DELETE NO ACTION
311 ON UPDATE NO ACTION)
312ENGINE = InnoDB;
313
314
315-- -----------------------------------------------------
316-- Table `narwal`.`company_note`
317-- -----------------------------------------------------
318CREATE TABLE IF NOT EXISTS `narwal`.`company_note` (
319 `company_id` VARCHAR(36) NOT NULL,
320 `note_id` VARCHAR(36) NOT NULL,
321 PRIMARY KEY (`company_id`, `note_id`),
322 INDEX `fk_note_idx` (`note_id` ASC),
323 CONSTRAINT `fk_cn_note`
324 FOREIGN KEY (`note_id`)
325 REFERENCES `narwal`.`note` (`note_id`)
326 ON DELETE CASCADE
327 ON UPDATE CASCADE,
328 CONSTRAINT `fk_cn_company`
329 FOREIGN KEY (`company_id`)
330 REFERENCES `narwal`.`company` (`comp_id`)
331 ON DELETE CASCADE
332 ON UPDATE CASCADE)
333ENGINE = InnoDB;
334
335
336SET SQL_MODE=@OLD_SQL_MODE;
337SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
338SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;