1 DROP TABLE IF EXISTS profile_job_entreprise_term
;
2 DROP TABLE IF EXISTS profile_mentor_term
;
3 DROP TABLE IF EXISTS profile_job_term
;
4 DROP TABLE IF EXISTS profile_job_term_search
;
5 DROP TABLE IF EXISTS profile_job_term_relation
;
6 DROP TABLE IF EXISTS profile_job_term_enum
;
8 CREATE TABLE `profile_job_term_enum`
(
9 `jtid`
int unsigned
NOT NULL AUTO_INCREMENT
COMMENT 'term id',
10 `
name`
varchar(255) NOT NULL COMMENT 'name used in hierarchical context',
11 `full_name`
varchar(255) NOT NULL COMMENT 'name to use whithout context',
13 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT='job terms';
15 CREATE TABLE `profile_job_term_relation`
(
16 `jtid_1`
int unsigned
NOT NULL COMMENT 'first term id',
17 `jtid_2`
int unsigned
NOT NULL COMMENT 'second term id',
18 `rel`
enum('narrower','related') NOT NULL DEFAULT 'narrower' COMMENT 'relation between the second to the first term (second is narrower than first)',
19 `computed`
enum('original','computed') NOT NULL DEFAULT 'original' COMMENT 'relations can be computed from two original relations',
20 PRIMARY KEY (`jtid_1`
, `computed`
, `jtid_2`
),
21 FOREIGN KEY (`jtid_1`
) REFERENCES `profile_job_term_enum`
(`jtid`
) ON DELETE CASCADE ON UPDATE CASCADE,
22 INDEX `jtid_2`
(`jtid_2`
),
23 FOREIGN KEY (`jtid_2`
) REFERENCES `profile_job_term_enum`
(`jtid`
) ON DELETE CASCADE ON UPDATE CASCADE
24 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT='job terms relations';
26 CREATE TABLE `profile_job_term_search`
(
27 `
search`
varchar(50) NOT NULL COMMENT 'search token for a term',
28 `jtid`
int unsigned
NOT NULL AUTO_INCREMENT
COMMENT 'term id',
29 PRIMARY KEY (`
search`
, `jtid`
),
30 INDEX `jtid`
(`jtid`
),
31 FOREIGN KEY (`jtid`
) REFERENCES `profile_job_term_enum`
(`jtid`
) ON DELETE CASCADE ON UPDATE CASCADE
32 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT='search tokens of job terms';
34 CREATE TABLE `profile_job_term`
(
35 `pid`
INT(11) UNSIGNED
DEFAULT NULL COMMENT 'profile id',
36 `jid`
TINYINT(1) UNSIGNED
DEFAULT NULL COMMENT 'job id in profile',
37 `jtid`
INT UNSIGNED
DEFAULT NULL COMMENT 'term id',
38 `computed`
enum('original','computed') NOT NULL DEFAULT 'original' COMMENT 'terms can be added by user or computed from entreprise',
39 PRIMARY KEY (`pid`
, `jid`
, `jtid`
),
40 INDEX `jtid`
(`jtid`
),
41 FOREIGN KEY (`pid`
, `jid`
) REFERENCES `profile_job`
(`pid`
, `
id`
) ON DELETE CASCADE ON UPDATE CASCADE,
42 FOREIGN KEY (`jtid`
) REFERENCES `profile_job_term_enum`
(`jtid`
) ON DELETE CASCADE ON UPDATE CASCADE
43 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT='job terms for jobs in profiles';
45 CREATE TABLE `profile_mentor_term`
(
46 `pid`
INT(11) UNSIGNED
DEFAULT NULL COMMENT 'profile id',
47 `jtid`
int unsigned
NOT NULL COMMENT 'term id',
48 PRIMARY KEY (`pid`
, `jtid`
),
49 INDEX `jtid`
(`jtid`
),
50 FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE,
51 FOREIGN KEY (`jtid`
) REFERENCES `profile_job_term_enum`
(`jtid`
) ON DELETE CASCADE ON UPDATE CASCADE
52 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT='job terms for mentorship in profiles';
54 CREATE TABLE `profile_job_entreprise_term`
(
55 `eid`
int(6) unsigned
default NULL COMMENT 'entreprise id',
56 `jtid`
int unsigned
NOT NULL COMMENT 'term id',
57 PRIMARY KEY (`eid`
, `jtid`
),
58 INDEX `jtid`
(`jtid`
),
59 FOREIGN KEY (`eid`
) REFERENCES `profile_job_enum`
(`
id`
) ON DELETE CASCADE ON UPDATE CASCADE,
60 FOREIGN KEY (`jtid`
) REFERENCES `profile_job_term_enum`
(`jtid`
) ON DELETE CASCADE ON UPDATE CASCADE
61 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT='job terms associated to entreprises';
63 -- Adds the root term --
64 INSERT INTO `profile_job_term_enum`
(`jtid`
, `
name`
) VALUES (0, '');
65 UPDATE `profile_job_term_enum`
SET `jtid`
= 0 WHERE `
name`
= '';
67 -- vim:set syntax=mysql: