· 9 years ago · Dec 23, 2016, 04:12 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 mobility
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mobility
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mobility` DEFAULT CHARACTER SET utf8 ;
15USE `mobility` ;
16
17-- -----------------------------------------------------
18-- Table `mobility`.`country`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mobility`.`country` (
21 `id` CHAR(2) NOT NULL,
22 `description` VARCHAR(500) NOT NULL,
23 PRIMARY KEY (`id`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `mobility`.`address`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `mobility`.`address` (
31 `id` INT NOT NULL AUTO_INCREMENT,
32 `street` VARCHAR(500) NOT NULL,
33 `street_extra` VARCHAR(500) NULL,
34 `postalcode` VARCHAR(10) NOT NULL,
35 `city` VARCHAR(500) NOT NULL,
36 `country_id` CHAR(2) NOT NULL,
37 PRIMARY KEY (`id`),
38 INDEX `fk_address_country1_idx` (`country_id` ASC),
39 CONSTRAINT `fk_address_country1`
40 FOREIGN KEY (`country_id`)
41 REFERENCES `mobility`.`country` (`id`)
42 ON DELETE NO ACTION
43 ON UPDATE NO ACTION)
44ENGINE = InnoDB;
45
46
47-- -----------------------------------------------------
48-- Table `mobility`.`institution`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `mobility`.`institution` (
51 `id` INT NOT NULL AUTO_INCREMENT,
52 `description` VARCHAR(45) NOT NULL,
53 PRIMARY KEY (`id`))
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `mobility`.`session`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `mobility`.`session` (
61 `id` INT NOT NULL AUTO_INCREMENT,
62 `description` VARCHAR(500) NOT NULL,
63 PRIMARY KEY (`id`))
64ENGINE = InnoDB;
65
66
67-- -----------------------------------------------------
68-- Table `mobility`.`user`
69-- -----------------------------------------------------
70CREATE TABLE IF NOT EXISTS `mobility`.`user` (
71 `id` INT NOT NULL AUTO_INCREMENT,
72 `email` VARCHAR(500) NOT NULL,
73 `title` VARCHAR(40) NULL,
74 `firstname` VARCHAR(500) NOT NULL,
75 `lastname` VARCHAR(500) NOT NULL,
76 `jobtitle` VARCHAR(500) NOT NULL,
77 `institution_name` VARCHAR(500) NOT NULL,
78 `vat` VARCHAR(50) NULL,
79 `breakout` TINYINT(1) NOT NULL,
80 `reception` TINYINT(1) NOT NULL,
81 `invoice_type` CHAR(1) NULL,
82 `delivery_id` INT NULL,
83 `billing_id` INT NULL,
84 `institution_id` INT NOT NULL,
85 `session_id` INT NULL,
86 `user_id` INT NULL,
87 PRIMARY KEY (`id`),
88 INDEX `fk_user_address2_idx` (`delivery_id` ASC),
89 INDEX `fk_user_facility1_idx` (`institution_id` ASC),
90 INDEX `fk_user_session1_idx` (`session_id` ASC),
91 INDEX `fk_user_address1_idx` (`billing_id` ASC),
92 INDEX `fk_user_user1_idx` (`user_id` ASC),
93 CONSTRAINT `fk_user_address2`
94 FOREIGN KEY (`delivery_id`)
95 REFERENCES `mobility`.`address` (`id`)
96 ON DELETE NO ACTION
97 ON UPDATE NO ACTION,
98 CONSTRAINT `fk_user_facility1`
99 FOREIGN KEY (`institution_id`)
100 REFERENCES `mobility`.`institution` (`id`)
101 ON DELETE NO ACTION
102 ON UPDATE NO ACTION,
103 CONSTRAINT `fk_user_session1`
104 FOREIGN KEY (`session_id`)
105 REFERENCES `mobility`.`session` (`id`)
106 ON DELETE NO ACTION
107 ON UPDATE NO ACTION,
108 CONSTRAINT `fk_user_address1`
109 FOREIGN KEY (`billing_id`)
110 REFERENCES `mobility`.`address` (`id`)
111 ON DELETE NO ACTION
112 ON UPDATE NO ACTION,
113 CONSTRAINT `fk_user_user1`
114 FOREIGN KEY (`user_id`)
115 REFERENCES `mobility`.`user` (`id`)
116 ON DELETE NO ACTION
117 ON UPDATE NO ACTION)
118ENGINE = InnoDB;
119
120
121-- -----------------------------------------------------
122-- Table `mobility`.`tickettype`
123-- -----------------------------------------------------
124CREATE TABLE IF NOT EXISTS `mobility`.`tickettype` (
125 `id` INT NOT NULL AUTO_INCREMENT,
126 `description` VARCHAR(500) NOT NULL,
127 `price` DECIMAL(10,2) NOT NULL,
128 PRIMARY KEY (`id`))
129ENGINE = InnoDB;
130
131
132-- -----------------------------------------------------
133-- Table `mobility`.`ticket`
134-- -----------------------------------------------------
135CREATE TABLE IF NOT EXISTS `mobility`.`ticket` (
136 `id` INT NOT NULL AUTO_INCREMENT,
137 `user_id` INT NOT NULL,
138 `tickettype_id` INT NOT NULL,
139 `reserved` DATETIME NOT NULL,
140 `paid` DATETIME NULL,
141 PRIMARY KEY (`id`),
142 INDEX `fk_ticket_user_idx` (`user_id` ASC),
143 INDEX `fk_ticket_tickettype1_idx` (`tickettype_id` ASC),
144 CONSTRAINT `fk_ticket_user`
145 FOREIGN KEY (`user_id`)
146 REFERENCES `mobility`.`user` (`id`)
147 ON DELETE NO ACTION
148 ON UPDATE NO ACTION,
149 CONSTRAINT `fk_ticket_tickettype1`
150 FOREIGN KEY (`tickettype_id`)
151 REFERENCES `mobility`.`tickettype` (`id`)
152 ON DELETE NO ACTION
153 ON UPDATE NO ACTION)
154ENGINE = InnoDB;
155
156
157-- -----------------------------------------------------
158-- Table `mobility`.`promocode`
159-- -----------------------------------------------------
160CREATE TABLE IF NOT EXISTS `mobility`.`promocode` (
161 `id` VARCHAR(8) NOT NULL,
162 `used` DATETIME NULL,
163 `ticket_id` INT NULL,
164 `tickettype_id` INT NOT NULL,
165 PRIMARY KEY (`id`),
166 INDEX `fk_promocode_ticket1_idx` (`ticket_id` ASC),
167 INDEX `fk_promocode_tickettype1_idx` (`tickettype_id` ASC),
168 CONSTRAINT `fk_promocode_ticket1`
169 FOREIGN KEY (`ticket_id`)
170 REFERENCES `mobility`.`ticket` (`id`)
171 ON DELETE NO ACTION
172 ON UPDATE NO ACTION,
173 CONSTRAINT `fk_promocode_tickettype1`
174 FOREIGN KEY (`tickettype_id`)
175 REFERENCES `mobility`.`tickettype` (`id`)
176 ON DELETE NO ACTION
177 ON UPDATE NO ACTION)
178ENGINE = InnoDB;
179
180
181-- -----------------------------------------------------
182-- Table `mobility`.`news`
183-- -----------------------------------------------------
184CREATE TABLE IF NOT EXISTS `mobility`.`news` (
185 `id` INT NOT NULL AUTO_INCREMENT,
186 `picturepath` VARCHAR(500) NULL,
187 `content` TEXT NOT NULL,
188 `title` TEXT NOT NULL,
189 `shorttext` TEXT NOT NULL,
190 `published` DATETIME NULL,
191 PRIMARY KEY (`id`))
192ENGINE = InnoDB;
193
194
195-- -----------------------------------------------------
196-- Table `mobility`.`speaker`
197-- -----------------------------------------------------
198CREATE TABLE IF NOT EXISTS `mobility`.`speaker` (
199 `id` INT NOT NULL AUTO_INCREMENT,
200 `firstname` VARCHAR(500) NOT NULL,
201 `lastname` VARCHAR(500) NOT NULL,
202 `description` TEXT NOT NULL,
203 `picturepath` VARCHAR(500) NULL,
204 `website` VARCHAR(500) NULL,
205 `company` VARCHAR(500) NOT NULL,
206 PRIMARY KEY (`id`))
207ENGINE = InnoDB;
208
209
210-- -----------------------------------------------------
211-- Table `mobility`.`adminuser`
212-- -----------------------------------------------------
213CREATE TABLE IF NOT EXISTS `mobility`.`adminuser` (
214 `id` INT NOT NULL AUTO_INCREMENT,
215 `username` VARCHAR(50) NOT NULL,
216 `password` VARCHAR(200) NOT NULL,
217 `remember_token` VARCHAR(100) NULL,
218 PRIMARY KEY (`id`))
219ENGINE = InnoDB;
220
221
222-- -----------------------------------------------------
223-- Table `mobility`.`venue`
224-- -----------------------------------------------------
225CREATE TABLE IF NOT EXISTS `mobility`.`venue` (
226 `id` INT NOT NULL AUTO_INCREMENT,
227 `name` VARCHAR(500) NOT NULL,
228 `description` TEXT NOT NULL,
229 PRIMARY KEY (`id`))
230ENGINE = InnoDB;
231
232
233-- -----------------------------------------------------
234-- Table `mobility`.`talk`
235-- -----------------------------------------------------
236CREATE TABLE IF NOT EXISTS `mobility`.`talk` (
237 `id` INT NOT NULL AUTO_INCREMENT,
238 `day` DATE NOT NULL,
239 `from` TIME NOT NULL,
240 `to` TIME NOT NULL,
241 `title` TEXT NOT NULL,
242 `description` TEXT NOT NULL,
243 `breakout` TINYINT(1) NOT NULL,
244 `venue_id` INT NOT NULL,
245 PRIMARY KEY (`id`),
246 INDEX `fk_talk_venue1_idx` (`venue_id` ASC),
247 CONSTRAINT `fk_talk_venue1`
248 FOREIGN KEY (`venue_id`)
249 REFERENCES `mobility`.`venue` (`id`)
250 ON DELETE NO ACTION
251 ON UPDATE NO ACTION)
252ENGINE = InnoDB;
253
254
255-- -----------------------------------------------------
256-- Table `mobility`.`speaker_talk`
257-- -----------------------------------------------------
258CREATE TABLE IF NOT EXISTS `mobility`.`speaker_talk` (
259 `talk_id` INT NOT NULL,
260 `speaker_id` INT NOT NULL,
261 PRIMARY KEY (`talk_id`, `speaker_id`),
262 INDEX `fk_talk_has_speaker_speaker1_idx` (`speaker_id` ASC),
263 INDEX `fk_talk_has_speaker_talk1_idx` (`talk_id` ASC),
264 CONSTRAINT `fk_talk_has_speaker_talk1`
265 FOREIGN KEY (`talk_id`)
266 REFERENCES `mobility`.`talk` (`id`)
267 ON DELETE NO ACTION
268 ON UPDATE NO ACTION,
269 CONSTRAINT `fk_talk_has_speaker_speaker1`
270 FOREIGN KEY (`speaker_id`)
271 REFERENCES `mobility`.`speaker` (`id`)
272 ON DELETE NO ACTION
273 ON UPDATE NO ACTION)
274ENGINE = InnoDB;
275
276
277SET SQL_MODE=@OLD_SQL_MODE;
278SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
279SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;