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 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
| CREATE TABLE user_table(
id_user INT AUTO_INCREMENT,
sesa INT NOT NULL,
firstname VARCHAR(255),
lastname VARCHAR(255),
email VARCHAR(255),
company VARCHAR(50),
buunitname VARCHAR(50),
id_user_manager INT,
id_location INT NOT NULL,
PRIMARY KEY(id_user),
UNIQUE(sesa),
FOREIGN KEY(id_user_manager) REFERENCES user_table(id_user),
FOREIGN KEY(id_location) REFERENCES location(id_location)
);
CREATE TABLE application(
id_application INT AUTO_INCREMENT,
appli_name VARCHAR(50) NOT NULL,
platform_owner VARCHAR(50),
platform VARCHAR(50),
publisher VARCHAR(50),
id_user INT NOT NULL,
PRIMARY KEY(id_application),
UNIQUE(appli_name),
FOREIGN KEY(id_user) REFERENCES user_table(id_user)
);
CREATE TABLE license(
id_license INT AUTO_INCREMENT,
activate_date DATE,
deactivate_date DATE,
id_application_appl INT NOT NULL,
PRIMARY KEY(id_license),
FOREIGN KEY(id_application_appl) REFERENCES application(id_application)
);
CREATE TABLE ticket(
id_ticket INT AUTO_INCREMENT,
num_ticket BIGINT NOT NULL,
assigned_group VARCHAR(30),
submitted_date DATE,
last_resolved_date DATE,
summary VARCHAR(200),
priority VARCHAR(6),
status VARCHAR(13),
type_incident VARCHAR(10),
source VARCHAR(12),
first_country VARCHAR(30),
id_user INT NOT NULL,
id_application INT NOT NULL,
PRIMARY KEY(id_ticket),
UNIQUE(num_ticket),
FOREIGN KEY(id_user) REFERENCES user_table(id_user),
FOREIGN KEY(id_application) REFERENCES application(id_application)
);
CREATE TABLE license_error(
id_license INT AUTO_INCREMENT,
activate_date DATE,
deactivate_date DATE,
id_application_appl INT NOT NULL,
PRIMARY KEY(id_license),
FOREIGN KEY(id_application_appl) REFERENCES application(id_application)
);
CREATE TABLE ticket_error(
id_ticket INT AUTO_INCREMENT,
num_ticket BIGINT,
assigned_group VARCHAR(30),
submitted_date DATE,
last_resolved_date DATE,
summary VARCHAR(200),
priority VARCHAR(6),
status VARCHAR(13),
type_incident VARCHAR(10),
source VARCHAR(12),
first_country VARCHAR(30),
id_application INT NOT NULL,
id_user INT NOT NULL,
PRIMARY KEY(id_ticket),
UNIQUE(num_ticket),
FOREIGN KEY(id_application) REFERENCES application(id_application),
FOREIGN KEY(id_user) REFERENCES user_table(id_user)
); |
Partager