· 8 years ago · Jun 14, 2018, 04: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 attendance_new
9-- -----------------------------------------------------
10DROP SCHEMA IF EXISTS `attendance_new` ;
11
12-- -----------------------------------------------------
13-- Schema attendance_new
14-- -----------------------------------------------------
15CREATE SCHEMA IF NOT EXISTS `attendance_new` DEFAULT CHARACTER SET utf8 ;
16USE `attendance_new` ;
17
18-- -----------------------------------------------------
19-- Table `attendance_new`.`notification`
20-- -----------------------------------------------------
21CREATE TABLE IF NOT EXISTS `attendance_new`.`notification` (
22 `id` INT(11) NOT NULL AUTO_INCREMENT,
23 `title` VARCHAR(128) NOT NULL,
24 `description` VARCHAR(256) NOT NULL,
25 `link` VARCHAR(256) NULL DEFAULT NULL,
26 `shown` TINYINT(1) NOT NULL DEFAULT '0',
27 PRIMARY KEY (`id`),
28 UNIQUE INDEX `notification_id_uindex` (`id` ASC))
29ENGINE = InnoDB
30DEFAULT CHARACTER SET = utf8;
31
32
33-- -----------------------------------------------------
34-- Table `attendance_new`.`user`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `attendance_new`.`user` (
37 `id` INT(11) NOT NULL AUTO_INCREMENT,
38 `orion_login` VARCHAR(128) NOT NULL,
39 `name` VARCHAR(128) NULL DEFAULT NULL,
40 `last_name` VARCHAR(128) NULL DEFAULT NULL,
41 `honorific_prefix` VARCHAR(45) NULL DEFAULT NULL,
42 `honorific_suffix` VARCHAR(45) NULL DEFAULT NULL,
43 `authority` ENUM('USER', 'SECRETARY') NULL DEFAULT NULL,
44 `is_active` TINYINT NULL DEFAULT 1,
45 `main_work_station` ENUM('KIV', 'NTIS') NULL DEFAULT NULL,
46 PRIMARY KEY (`id`),
47 UNIQUE INDEX `orion_login_UNIQUE` (`orion_login` ASC))
48ENGINE = InnoDB
49DEFAULT CHARACTER SET = utf8;
50
51
52-- -----------------------------------------------------
53-- Table `attendance_new`.`user_holiday`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `attendance_new`.`user_holiday` (
56 `id` INT(11) NOT NULL AUTO_INCREMENT,
57 `user_id` INT(11) NOT NULL,
58 `date` DATE NOT NULL,
59 `type` VARCHAR(45) NOT NULL,
60 PRIMARY KEY (`id`, `user_id`),
61 INDEX `fk_user_vacation_user1_idx` (`user_id` ASC),
62 CONSTRAINT `fk_user_vacation_user1`
63 FOREIGN KEY (`user_id`)
64 REFERENCES `attendance_new`.`user` (`id`)
65 ON DELETE CASCADE
66 ON UPDATE NO ACTION)
67ENGINE = InnoDB
68AUTO_INCREMENT = 2
69DEFAULT CHARACTER SET = utf8;
70
71
72-- -----------------------------------------------------
73-- Table `attendance_new`.`day_time_sheet`
74-- -----------------------------------------------------
75CREATE TABLE IF NOT EXISTS `attendance_new`.`day_time_sheet` (
76 `user_id` INT(11) NOT NULL,
77 `date` DATE NOT NULL,
78 `first_part_from` TIME NULL,
79 `first_part_to` TIME NULL,
80 `second_part_from` TIME NULL,
81 `second_part_to` TIME NULL,
82 `day_type` VARCHAR(128) NULL DEFAULT NULL,
83 PRIMARY KEY (`user_id`, `date`),
84 INDEX `fk_day_time_sheet_user1_idx` (`user_id` ASC),
85 CONSTRAINT `fk_day_time_sheet_user1`
86 FOREIGN KEY (`user_id`)
87 REFERENCES `attendance_new`.`user` (`id`)
88 ON DELETE CASCADE
89 ON UPDATE NO ACTION)
90ENGINE = InnoDB;
91
92
93-- -----------------------------------------------------
94-- Table `attendance_new`.`project`
95-- -----------------------------------------------------
96CREATE TABLE IF NOT EXISTS `attendance_new`.`project` (
97 `id` INT(11) NOT NULL AUTO_INCREMENT,
98 `project_name` VARCHAR(200) NOT NULL,
99 `project_name_short` VARCHAR(15) NOT NULL,
100 `description` TEXT NULL,
101 PRIMARY KEY (`id`),
102 UNIQUE INDEX `project_id_uindex` (`id` ASC))
103ENGINE = InnoDB
104DEFAULT CHARACTER SET = utf8;
105
106
107-- -----------------------------------------------------
108-- Table `attendance_new`.`user_sharing_timesheet`
109-- -----------------------------------------------------
110CREATE TABLE IF NOT EXISTS `attendance_new`.`user_sharing_timesheet` (
111 `sharing_user_id` INT(11) NOT NULL,
112 `share_to_id` INT(11) NOT NULL,
113 PRIMARY KEY (`sharing_user_id`, `share_to_id`),
114 INDEX `fk_user_has_user_user4_idx` (`share_to_id` ASC),
115 INDEX `fk_user_has_user_user3_idx` (`sharing_user_id` ASC),
116 CONSTRAINT `fk_user_has_user_user3`
117 FOREIGN KEY (`sharing_user_id`)
118 REFERENCES `attendance_new`.`user` (`id`)
119 ON DELETE CASCADE
120 ON UPDATE NO ACTION,
121 CONSTRAINT `fk_user_has_user_user4`
122 FOREIGN KEY (`share_to_id`)
123 REFERENCES `attendance_new`.`user` (`id`)
124 ON DELETE CASCADE
125 ON UPDATE NO ACTION)
126ENGINE = InnoDB
127DEFAULT CHARACTER SET = utf8;
128
129
130-- -----------------------------------------------------
131-- Table `attendance_new`.`attendance`
132-- -----------------------------------------------------
133CREATE TABLE IF NOT EXISTS `attendance_new`.`attendance` (
134 `id` INT NOT NULL AUTO_INCREMENT,
135 `user_id` INT(11) NOT NULL,
136 `active_from` DATE NOT NULL,
137 `day_in_week` INT(2) NOT NULL,
138 `enabled` TINYINT NOT NULL DEFAULT 1,
139 `first_part_from` TIME NOT NULL,
140 `first_part_to` TIME NOT NULL,
141 `second_part_from` TIME NOT NULL,
142 `second_part_to` TIME NOT NULL,
143 PRIMARY KEY (`id`, `user_id`, `active_from`, `day_in_week`),
144 INDEX `fk_day_time_sheet_user1_idx` (`user_id` ASC),
145 CONSTRAINT `fk_day_time_sheet_user10`
146 FOREIGN KEY (`user_id`)
147 REFERENCES `attendance_new`.`user` (`id`)
148 ON DELETE CASCADE
149 ON UPDATE NO ACTION)
150ENGINE = InnoDB;
151
152
153-- -----------------------------------------------------
154-- Table `attendance_new`.`user_contract`
155-- -----------------------------------------------------
156CREATE TABLE IF NOT EXISTS `attendance_new`.`user_contract` (
157 `user_id` INT(11) NOT NULL,
158 `active_from` DATE NOT NULL,
159 `active_to` DATE NULL DEFAULT NULL,
160 `obligation_KIV` DOUBLE NOT NULL DEFAULT 0,
161 `obligation_NTIS` DOUBLE NOT NULL DEFAULT 0,
162 INDEX `fk_user_contract_user2_idx` (`user_id` ASC),
163 PRIMARY KEY (`user_id`, `active_from`),
164 CONSTRAINT `fk_user_contract_user2`
165 FOREIGN KEY (`user_id`)
166 REFERENCES `attendance_new`.`user` (`id`)
167 ON DELETE CASCADE
168 ON UPDATE NO ACTION)
169ENGINE = InnoDB;
170
171
172-- -----------------------------------------------------
173-- Table `attendance_new`.`user_assigned_to_project`
174-- -----------------------------------------------------
175CREATE TABLE IF NOT EXISTS `attendance_new`.`user_assigned_to_project` (
176 `user_id` INT(11) NOT NULL,
177 `project_id` INT(11) NOT NULL,
178 `active_from` DATE NOT NULL,
179 `active_to` DATE NULL DEFAULT NULL,
180 `obligation` DOUBLE NOT NULL DEFAULT 0,
181 PRIMARY KEY (`user_id`, `project_id`, `active_from`),
182 INDEX `fk_project_has_user_contract_user_contract1_idx` (`user_id` ASC, `active_from` ASC),
183 INDEX `fk_project_has_user_contract_project1_idx` (`project_id` ASC),
184 CONSTRAINT `fk_project_has_user_contract_project1`
185 FOREIGN KEY (`project_id`)
186 REFERENCES `attendance_new`.`project` (`id`)
187 ON DELETE CASCADE
188 ON UPDATE NO ACTION,
189 CONSTRAINT `fk_project_has_user_contract_user_contract1`
190 FOREIGN KEY (`user_id` , `active_from`)
191 REFERENCES `attendance_new`.`user_contract` (`user_id` , `active_from`)
192 ON DELETE CASCADE
193 ON UPDATE NO ACTION)
194ENGINE = InnoDB
195DEFAULT CHARACTER SET = utf8;
196
197
198-- -----------------------------------------------------
199-- Table `attendance_new`.`user_holiday_settings`
200-- -----------------------------------------------------
201CREATE TABLE IF NOT EXISTS `attendance_new`.`user_holiday_settings` (
202 `user_id` INT(11) NOT NULL,
203 `year` INT NOT NULL,
204 `hours` INT NOT NULL,
205 INDEX `fk_user_holiday_settings_user1_idx` (`user_id` ASC),
206 CONSTRAINT `fk_user_holiday_settings_user1`
207 FOREIGN KEY (`user_id`)
208 REFERENCES `attendance_new`.`user` (`id`)
209 ON DELETE NO ACTION
210 ON UPDATE NO ACTION)
211ENGINE = InnoDB;
212
213
214SET SQL_MODE=@OLD_SQL_MODE;
215SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
216SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;