· 7 years ago · Oct 01, 2018, 09:02 AM
1-- MySQL Script generated by MySQL Workbench
2-- 10/01/18 11:58:18
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema betapress
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema betapress
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `betapress` DEFAULT CHARACTER SET utf8 ;
18-- -----------------------------------------------------
19-- Schema new_schema1
20-- -----------------------------------------------------
21USE `betapress` ;
22
23-- -----------------------------------------------------
24-- Table `betapress`.`vacancy`
25-- -----------------------------------------------------
26CREATE TABLE IF NOT EXISTS `betapress`.`vacancy` (
27 `id` INT NOT NULL AUTO_INCREMENT,
28 `v_name` VARCHAR(255) NULL,
29 `v_otrasl` VARCHAR(45) NULL,
30 PRIMARY KEY (`id`))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `betapress`.`phones`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `betapress`.`phones` (
38 `id` INT NOT NULL AUTO_INCREMENT,
39 `phone_number` VARCHAR(255) NULL,
40 PRIMARY KEY (`id`));
41
42
43-- -----------------------------------------------------
44-- Table `betapress`.`clients`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `betapress`.`clients` (
47 `id` INT NOT NULL AUTO_INCREMENT,
48 `client_name` VARCHAR(255) NULL,
49 PRIMARY KEY (`id`));
50
51
52-- -----------------------------------------------------
53-- Table `betapress`.`projects`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `betapress`.`projects` (
56 `id` INT NOT NULL AUTO_INCREMENT,
57 `project_name` VARCHAR(255) NULL,
58 `clients_id` INT NOT NULL,
59 `datestart` DATE NULL,
60 `dateend` DATE NULL,
61 `order` VARCHAR(45) NULL,
62 `budget` FLOAT NULL,
63 PRIMARY KEY (`id`, `clients_id`),
64 INDEX `fk_projects_clients1_idx` (`clients_id` ASC),
65 CONSTRAINT `fk_projects_clients1`
66 FOREIGN KEY (`clients_id`)
67 REFERENCES `betapress`.`clients` (`id`)
68 ON DELETE NO ACTION
69 ON UPDATE NO ACTION);
70
71
72-- -----------------------------------------------------
73-- Table `betapress`.`sites`
74-- -----------------------------------------------------
75CREATE TABLE IF NOT EXISTS `betapress`.`sites` (
76 `id` INT NOT NULL AUTO_INCREMENT,
77 `site_url` VARCHAR(255) NULL,
78 `projects_id` INT NOT NULL,
79 PRIMARY KEY (`id`, `projects_id`),
80 INDEX `fk_sites_projects1_idx` (`projects_id` ASC),
81 CONSTRAINT `fk_sites_projects1`
82 FOREIGN KEY (`projects_id`)
83 REFERENCES `betapress`.`projects` (`id`)
84 ON DELETE NO ACTION
85 ON UPDATE NO ACTION);
86
87
88-- -----------------------------------------------------
89-- Table `betapress`.`sources`
90-- -----------------------------------------------------
91CREATE TABLE IF NOT EXISTS `betapress`.`sources` (
92 `id` INT NOT NULL AUTO_INCREMENT,
93 `source_name` VARCHAR(255) NULL,
94 `source_group` VARCHAR(45) NULL,
95 PRIMARY KEY (`id`))
96ENGINE = InnoDB
97DEFAULT CHARACTER SET = big5;
98
99
100-- -----------------------------------------------------
101-- Table `betapress`.`sorces_links`
102-- -----------------------------------------------------
103CREATE TABLE IF NOT EXISTS `betapress`.`sorces_links` (
104 `id` INT NOT NULL AUTO_INCREMENT,
105 `link` VARCHAR(255) NULL,
106 `sorces_id` INT NOT NULL,
107 PRIMARY KEY (`id`, `sorces_id`),
108 INDEX `fk_sources_links1_idx` (`sorces_id` ASC),
109 CONSTRAINT `fk_sources_links1`
110 FOREIGN KEY (`sorces_id`)
111 REFERENCES `betapress`.`sources` (`id`)
112 ON DELETE NO ACTION
113 ON UPDATE NO ACTION);
114
115
116-- -----------------------------------------------------
117-- Table `betapress`.`mt_calls`
118-- -----------------------------------------------------
119CREATE TABLE IF NOT EXISTS `betapress`.`mt_calls` (
120 `phones_id` INT NOT NULL,
121 `date` DATE NULL,
122 `calls` INT NULL,
123 `answered` INT NULL,
124 `declined` INT NULL,
125 `source_id` INT NOT NULL,
126 `callstart` DATETIME NULL,
127 `callend` DATETIME NULL,
128 `callernumber` VARCHAR(50) NULL,
129 `sourcenumber(50)` VARCHAR(45) NULL,
130 `status` VARCHAR(45) NULL,
131 `type` VARCHAR(45) NULL,
132 `delay` TIME NULL,
133 `approved` VARCHAR(45) NULL,
134 `projects_id` INT NOT NULL,
135 `projects_clients_id` INT NOT NULL,
136 `sorces_links_id` INT NOT NULL,
137 `sorces_links_sorces_id` INT NOT NULL,
138 PRIMARY KEY (`phones_id`, `source_id`, `projects_id`, `projects_clients_id`, `sorces_links_id`, `sorces_links_sorces_id`),
139 INDEX `fk_phones_has_sites_phones1_idx` (`phones_id` ASC),
140 INDEX `fk_phones_has_sites_projects1_idx` (`projects_id` ASC, `projects_clients_id` ASC),
141 INDEX `fk_mt_calls_sorces_links1_idx` (`sorces_links_id` ASC, `sorces_links_sorces_id` ASC),
142 CONSTRAINT `fk_phones_has_sites_phones1`
143 FOREIGN KEY (`phones_id`)
144 REFERENCES `betapress`.`phones` (`id`)
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION,
147 CONSTRAINT `fk_phones_has_sites_projects1`
148 FOREIGN KEY (`projects_id` , `projects_clients_id`)
149 REFERENCES `betapress`.`projects` (`id` , `clients_id`)
150 ON DELETE NO ACTION
151 ON UPDATE NO ACTION,
152 CONSTRAINT `fk_mt_calls_sorces_links1`
153 FOREIGN KEY (`sorces_links_id` , `sorces_links_sorces_id`)
154 REFERENCES `betapress`.`sorces_links` (`id` , `sorces_id`)
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION);
157
158
159-- -----------------------------------------------------
160-- Table `betapress`.`oblast`
161-- -----------------------------------------------------
162CREATE TABLE IF NOT EXISTS `betapress`.`oblast` (
163 `id` INT NOT NULL AUTO_INCREMENT,
164 `oblast_name` VARCHAR(255) NULL,
165 `okrug` VARCHAR(255) NOT NULL,
166 PRIMARY KEY (`id`));
167
168
169-- -----------------------------------------------------
170-- Table `betapress`.`city`
171-- -----------------------------------------------------
172CREATE TABLE IF NOT EXISTS `betapress`.`city` (
173 `id` INT NOT NULL AUTO_INCREMENT,
174 `city_name` VARCHAR(255) NULL,
175 `oblast_id` INT NOT NULL,
176 PRIMARY KEY (`id`, `oblast_id`),
177 INDEX `fk_gorog_oblast1_idx` (`oblast_id` ASC),
178 CONSTRAINT `fk_gorog_oblast1`
179 FOREIGN KEY (`oblast_id`)
180 REFERENCES `betapress`.`oblast` (`id`)
181 ON DELETE NO ACTION
182 ON UPDATE NO ACTION);
183
184
185-- -----------------------------------------------------
186-- Table `betapress`.`district`
187-- -----------------------------------------------------
188CREATE TABLE IF NOT EXISTS `betapress`.`district` (
189 `id` INT NOT NULL AUTO_INCREMENT,
190 `district_name` VARCHAR(255) NULL,
191 `city_id` INT NOT NULL,
192 PRIMARY KEY (`id`, `city_id`),
193 INDEX `fk_district_city1_idx` (`city_id` ASC),
194 CONSTRAINT `fk_district_city1`
195 FOREIGN KEY (`city_id`)
196 REFERENCES `betapress`.`city` (`id`)
197 ON DELETE NO ACTION
198 ON UPDATE NO ACTION);
199
200
201-- -----------------------------------------------------
202-- Table `betapress`.`mt_metrics`
203-- -----------------------------------------------------
204CREATE TABLE IF NOT EXISTS `betapress`.`mt_metrics` (
205 `id` INT NOT NULL AUTO_INCREMENT,
206 `yandex_clicks` INT NULL,
207 `yandex_visits` INT NULL,
208 `date` DATE NULL,
209 `yandex_sessionTime` TIME NULL,
210 `yandex_otkaz` FLOAT NULL,
211 `google_visits` INT NULL,
212 `google_users` INT NULL,
213 `google_otkaz` FLOAT NULL,
214 `google_sessionTime` TIME NULL,
215 `google_cid` FLOAT NULL,
216 `yandex_cid` FLOAT NULL,
217 `yandex_goal_id` INT NULL,
218 `google_goal_id` INT NULL,
219 `sources_links_id` INT NOT NULL,
220 `district_id` INT NOT NULL,
221 `projects_id` INT NOT NULL,
222 `goal_form_send_ya` INT NULL,
223 `goal_chat_ya` INT NULL,
224 `goal_phone_copy_ya` INT NULL,
225 `goal_phone_click_ya` INT NULL,
226 `goal_email_copy_ya` INT NULL,
227 `goal_email_click_ya` INT NULL,
228 `goal_chat_register_ya` INT NULL,
229 `goal_linkout_ya` INT NULL,
230 `goal_form_send_gl` INT NULL,
231 `goal_chat_ya_gl` INT NULL,
232 `goal_phone_copy_gl` INT NULL,
233 `goal_phone_click_gl` INT NULL,
234 `goal_email_copy_gl` INT NULL,
235 `goal_email_click_gl` INT NULL,
236 `goal_chat_register_gl` INT NULL,
237 `goal_linkout_gl` INT NULL,
238 PRIMARY KEY (`id`, `sources_links_id`, `district_id`, `projects_id`),
239 INDEX `fk_metrics_Sources1_idx` (`sources_links_id` ASC),
240 INDEX `fk_metrics_district1_idx` (`district_id` ASC),
241 INDEX `fk_metrics_projects1_idx` (`projects_id` ASC),
242 CONSTRAINT `fk_metrics_src_links`
243 FOREIGN KEY (`sources_links_id`)
244 REFERENCES `betapress`.`sorces_links` (`id`)
245 ON DELETE NO ACTION
246 ON UPDATE NO ACTION,
247 CONSTRAINT `fk_metrics_district`
248 FOREIGN KEY (`district_id`)
249 REFERENCES `betapress`.`district` (`id`)
250 ON DELETE NO ACTION
251 ON UPDATE NO ACTION,
252 CONSTRAINT `fk_metrics_projects`
253 FOREIGN KEY (`projects_id`)
254 REFERENCES `betapress`.`projects` (`id`)
255 ON DELETE NO ACTION
256 ON UPDATE NO ACTION);
257
258
259-- -----------------------------------------------------
260-- Table `betapress`.`mt_leads`
261-- -----------------------------------------------------
262CREATE TABLE IF NOT EXISTS `betapress`.`mt_leads` (
263 `id` INT NOT NULL AUTO_INCREMENT,
264 `date` DATE NULL,
265 `isPhone` TINYINT(1) NULL,
266 `sources_id` INT NOT NULL,
267 `projects_id` INT NOT NULL,
268 `leadQuantyty` INT NULL,
269 `district_id` INT NOT NULL,
270 PRIMARY KEY (`id`, `sources_id`, `projects_id`, `district_id`),
271 INDEX `fk_leads_sources1_idx` (`sources_id` ASC),
272 INDEX `fk_leads_projects1_idx` (`projects_id` ASC),
273 INDEX `fk_leads_district1_idx` (`district_id` ASC),
274 CONSTRAINT `fk_leads_sources1`
275 FOREIGN KEY (`sources_id`)
276 REFERENCES `betapress`.`sorces_links` (`id`)
277 ON DELETE NO ACTION
278 ON UPDATE NO ACTION,
279 CONSTRAINT `fk_leads_projects1`
280 FOREIGN KEY (`projects_id`)
281 REFERENCES `betapress`.`projects` (`id`)
282 ON DELETE NO ACTION
283 ON UPDATE NO ACTION,
284 CONSTRAINT `fk_leads_district1`
285 FOREIGN KEY (`district_id`)
286 REFERENCES `betapress`.`district` (`id`)
287 ON DELETE NO ACTION
288 ON UPDATE NO ACTION);
289
290
291-- -----------------------------------------------------
292-- Table `betapress`.`goals`
293-- -----------------------------------------------------
294CREATE TABLE IF NOT EXISTS `betapress`.`goals` (
295 `id` INT NOT NULL AUTO_INCREMENT,
296 `name` VARCHAR(255) NULL,
297 `counter` VARCHAR(45) NULL,
298 `goal_id` VARCHAR(45) NULL,
299 PRIMARY KEY (`id`));
300
301
302-- -----------------------------------------------------
303-- Table `betapress`.`keywords`
304-- -----------------------------------------------------
305CREATE TABLE IF NOT EXISTS `betapress`.`keywords` (
306 `id` INT NOT NULL AUTO_INCREMENT,
307 `name` VARCHAR(255) NULL,
308 PRIMARY KEY (`id`));
309
310
311-- -----------------------------------------------------
312-- Table `betapress`.`mt_adw`
313-- -----------------------------------------------------
314CREATE TABLE IF NOT EXISTS `betapress`.`mt_adw` (
315 `id` INT NOT NULL AUTO_INCREMENT,
316 `date` DATE NULL,
317 `clicks` INT NULL,
318 `impressions` INT NULL,
319 `costs` FLOAT NULL,
320 `keywords_id` INT NOT NULL,
321 `district_id` INT NOT NULL,
322 `sources_id` INT NOT NULL,
323 `projects_id` INT NOT NULL,
324 `position` VARCHAR(45) NULL,
325 `adformat` VARCHAR(45) NULL,
326 PRIMARY KEY (`id`, `keywords_id`, `district_id`, `sources_id`, `projects_id`),
327 INDEX `fk_ppc_keywords1_idx` (`keywords_id` ASC),
328 INDEX `fk_ppc_district1_idx` (`district_id` ASC),
329 INDEX `fk_ppc_sources2_idx` (`sources_id` ASC),
330 INDEX `fk_ppc_projects1_idx` (`projects_id` ASC),
331 CONSTRAINT `fk_ppc_keywords1`
332 FOREIGN KEY (`keywords_id`)
333 REFERENCES `betapress`.`keywords` (`id`)
334 ON DELETE NO ACTION
335 ON UPDATE NO ACTION,
336 CONSTRAINT `fk_ppc_district1`
337 FOREIGN KEY (`district_id`)
338 REFERENCES `betapress`.`district` (`id`)
339 ON DELETE NO ACTION
340 ON UPDATE NO ACTION,
341 CONSTRAINT `fk_ppc_sources2`
342 FOREIGN KEY (`sources_id`)
343 REFERENCES `betapress`.`sorces_links` (`id`)
344 ON DELETE NO ACTION
345 ON UPDATE NO ACTION,
346 CONSTRAINT `fk_ppc_projects1`
347 FOREIGN KEY (`projects_id`)
348 REFERENCES `betapress`.`projects` (`id`)
349 ON DELETE NO ACTION
350 ON UPDATE NO ACTION);
351
352
353-- -----------------------------------------------------
354-- Table `betapress`.`vacancy_projects`
355-- -----------------------------------------------------
356CREATE TABLE IF NOT EXISTS `betapress`.`vacancy_projects` (
357 `vacancy_id` INT NOT NULL,
358 `projects_id` INT NOT NULL,
359 PRIMARY KEY (`vacancy_id`, `projects_id`),
360 INDEX `fk_vacancy_has_projects_projects1_idx` (`projects_id` ASC),
361 INDEX `fk_vacancy_has_projects_vacancy1_idx` (`vacancy_id` ASC),
362 CONSTRAINT `fk_vacancy_has_projects_vacancy1`
363 FOREIGN KEY (`vacancy_id`)
364 REFERENCES `betapress`.`vacancy` (`id`)
365 ON DELETE NO ACTION
366 ON UPDATE NO ACTION,
367 CONSTRAINT `fk_vacancy_has_projects_projects1`
368 FOREIGN KEY (`projects_id`)
369 REFERENCES `betapress`.`projects` (`id`)
370 ON DELETE NO ACTION
371 ON UPDATE NO ACTION)
372ENGINE = InnoDB;
373
374
375-- -----------------------------------------------------
376-- Table `betapress`.`project_links`
377-- -----------------------------------------------------
378CREATE TABLE IF NOT EXISTS `betapress`.`project_links` (
379 `id` INT NOT NULL AUTO_INCREMENT,
380 `project_id` INT NULL,
381 `links_id` INT NULL,
382 `budget` FLOAT NULL,
383 PRIMARY KEY (`id`));
384
385
386-- -----------------------------------------------------
387-- Table `betapress`.`mt_spends`
388-- -----------------------------------------------------
389CREATE TABLE IF NOT EXISTS `betapress`.`mt_spends` (
390 `id` INT NOT NULL AUTO_INCREMENT,
391 `date` DATE NULL,
392 `sum` FLOAT NULL,
393 `links_id` INT NOT NULL,
394 `projects_id` INT NOT NULL,
395 `district_id` INT NOT NULL,
396 PRIMARY KEY (`id`, `links_id`, `projects_id`, `district_id`),
397 INDEX `fk_spends_links1_idx` (`links_id` ASC),
398 INDEX `fk_spends_projects1_idx` (`projects_id` ASC),
399 INDEX `fk_spends_district1_idx` (`district_id` ASC),
400 CONSTRAINT `fk_spends_links1`
401 FOREIGN KEY (`links_id`)
402 REFERENCES `betapress`.`sorces_links` (`id`)
403 ON DELETE NO ACTION
404 ON UPDATE NO ACTION,
405 CONSTRAINT `fk_spends_projects1`
406 FOREIGN KEY (`projects_id`)
407 REFERENCES `betapress`.`projects` (`id`)
408 ON DELETE NO ACTION
409 ON UPDATE NO ACTION,
410 CONSTRAINT `fk_spends_district1`
411 FOREIGN KEY (`district_id`)
412 REFERENCES `betapress`.`district` (`id`)
413 ON DELETE NO ACTION
414 ON UPDATE NO ACTION);
415
416
417SET SQL_MODE=@OLD_SQL_MODE;
418SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
419SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;