1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
| -- Table des groupes d'utilisateurs.
CREATE TABLE IF NOT EXISTS groups (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
added_date DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
)
ENGINE=INNODB DEFAULT CHARSET=utf8;
-- Table des métiers des utilisateurs.
CREATE TABLE IF NOT EXISTS jobs (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
added_date DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
)
ENGINE=INNODB DEFAULT CHARSET=utf8;
-- Tables des utilisateurs.
CREATE TABLE IF NOT EXISTS users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
job_id INT UNSIGNED DEFAULT NULL,
group_id INT UNSIGNED NOT NULL,
last_name VARCHAR(255) NOT NULL,
first_name VARCHAR(255) NOT NULL,
added_date DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (job_id) REFERENCES jobs(id),
FOREIGN KEY (group_id) REFERENCES groups(id)
)
ENGINE=INNODB DEFAULT CHARSET=utf8;
-- Tables des commentaires des utilisateurs.
CREATE TABLE IF NOT EXISTS comments (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
job_id INT UNSIGNED NOT NULL,
group_id INT UNSIGNED NOT NULL,
comment VARCHAR(5000),
added_date DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (job_id) REFERENCES jobs(id),
FOREIGN KEY (group_id) REFERENCES groups(id)
)
ENGINE=INNODB DEFAULT CHARSET=utf8; |
Partager