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 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267
| CREATE TABLE IF NOT EXISTS Participant (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
nom VARCHAR(40) NOT NULL,
prenom VARCHAR(40) NOT NULL,
tlphn VARCHAR(14) NOT NULL,
adresse VARCHAR(255) DEFAULT 'non comm',
date_naissance DATE NULL DEFAULT NULL,
commentaires VARCHAR(255) DEFAULT 'non comm',
certif DATE NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX ind_prenom (`prenom`),
INDEX ind_nom (`nom`)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Formation (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
libele_formation VARCHAR(50) NOT NULL,
commentaire VARCHAR(250) NULL,
PRIMARY KEY (`id`)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Formaparti (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
id_formation SMALLINT UNSIGNED NOT NULL ,
id_participant INT UNSIGNED NOT NULL,
commentaire VARCHAR(255) NULL DEFAULT 'non comm',
PRIMARY KEY (`id`),
CONSTRAINT formationformaparti
FOREIGN KEY (id_formation)
REFERENCES Formation(id),
CONSTRAINT formapartiparti
FOREIGN KEY (id_participant)
REFERENCES Participant(id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Grp_cat (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
libele_grp_cat VARCHAR(40) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Grp (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
id_grp_cat SMALLINT UNSIGNED NOT NULL,
libele_grp VARCHAR(40) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT grpgrpcat
FOREIGN KEY (id_grp_cat)
REFERENCES Grp_cat(id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Grpparti (
id_grp INT UNSIGNED NOT NULL,
id_participant INT UNSIGNED NOT NULL,
referent INT UNSIGNED NOT NULL,
PRIMARY KEY (`id_participant`,`id_grp`),
CONSTRAINT grpgrpparti
FOREIGN KEY (id_grp)
REFERENCES Grp(id),
CONSTRAINT grppartiparti1
FOREIGN KEY (id_participant)
REFERENCES Participant(id),
CONSTRAINT grppartiparti2
FOREIGN KEY (referent)
REFERENCES Participant(id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Qualif (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
libele_qualif VARCHAR(40) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Qualifparti (
id_qualif SMALLINT UNSIGNED NOT NULL,
id_participant INT UNSIGNED NOT NULL,
date_qualif DATE NULL DEFAULT NULL,
PRIMARY KEY (`id_qualif`,`id_participant`),
CONSTRAINT qualifqualifparti
FOREIGN KEY (id_qualif)
REFERENCES Qualif(id),
CONSTRAINT qualifpartiparti
FOREIGN KEY (id_participant)
REFERENCES Participant(id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Niveau (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
libele VARCHAR(60) NOT NULL,
parametre VARCHAR(50),
classement TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Nivparti (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
id_niveau SMALLINT UNSIGNED NOT NULL,
id_participant INT UNSIGNED NOT NULL ,
date_niveau timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
verif_niveau TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
CONSTRAINT nivpartiparti
FOREIGN KEY (id_participant)
REFERENCES Participant(id),
CONSTRAINT nivpartiniveau
FOREIGN KEY (id_niveau)
REFERENCES Niveau(id),
UNIQUE INDEX ind_uni_niveau_parti (id_niveau, id_participant)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Fonction (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
libele_fonction VARCHAR(60) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Moyen (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
libele_moyen VARCHAR(70),
nb_place TINYINT UNSIGNED,
commentaire VARCHAR(250),
PRIMARY KEY (id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Fonctparti (
id_participant INT UNSIGNED NOT NULL,
id_fonction INT UNSIGNED NOT NULL,
PRIMARY KEY (id_participant,id_fonction),
CONSTRAINT fonctpartiparti
FOREIGN KEY (id_participant)
REFERENCES Participant(id),
CONSTRAINT fonctpartifonction
FOREIGN KEY (id_fonction)
REFERENCES Fonction(id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Fonctmoyen (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
id_fonction INT UNSIGNED NOT NULL,
id_moyen SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fonctmoyenfonct
FOREIGN KEY (id_fonction)
REFERENCES Fonction(id),
CONSTRAINT fonctmoyenmoyen
FOREIGN KEY (id_moyen)
REFERENCES Moyen(id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Prestation (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
libele_prestation VARCHAR(255) NOT NULL UNIQUE,
commentaire VARCHAR(255) NULL,
tarif_presta INT UNSIGNED NULL,
sur_presta_parent VARCHAR(70),
PRIMARY KEY (id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Sousprestation (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
libele_sous_prestation VARCHAR(40) NULL,
niveau_min SMALLINT UNSIGNED NOT NULL,
sur_presta_parent VARCHAR(70),
PRIMARY KEY (id),
CONSTRAINT sousprestaniveau
FOREIGN KEY (niveau_min)
REFERENCES Niveau(id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Evenplan (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
heure_rdv TIME NOT NULL,
heure_retour TIME NULL,
date_rdv DATE NOT NULL,
commentaire VARCHAR(255) DEFAULT 'no comm',
site VARCHAR(40) NULL,
status VARCHAR(40) NULL,
nb_place TINYINT UNSIGNED,
PRIMARY KEY (id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Fonctmoyevenplanparti (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
id_evenplan MEDIUMINT UNSIGNED NOT NULL,
id_fonctmoyen INT UNSIGNED NOT NULL,
id_participant INT UNSIGNED NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fonctmoyevenplanpartievenplan
FOREIGN KEY (id_evenplan)
REFERENCES Evenplan(id),
CONSTRAINT fonctmoyevenplanpartiparti
FOREIGN KEY (id_participant)
REFERENCES Participant(id),
CONSTRAINT fonctmoyevenplanpartifonctmoyen
FOREIGN KEY (id_fonctmoyen)
REFERENCES Fonctmoyen(id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Rglmt_type (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
libele_rglmt_type VARCHAR(40) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Reservation (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
id_niveau SMALLINT UNSIGNED,
id_participant INT UNSIGNED NOT NULL,
id_evenplan MEDIUMINT UNSIGNED NOT NULL,
id_rglmt_type TINYINT UNSIGNED NOT NULL,
id_sous_prestation SMALLINT UNSIGNED NOT NULL,
tarif_negoc SMALLINT NULL DEFAULT NULL,
validation TINYINT(1),
rglmt TINYINT(1),
date_rglmt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
date_souhait_d TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
date_souhait_f TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
num_palanq CHAR(4) NULL,
prof_real CHAR(5) NULL,
duree_real CHAR(5) NULL,
palier_real CHAR(5) NULL,
PRIMARY KEY (id),
CONSTRAINT reservationniveau
FOREIGN KEY (id_niveau)
REFERENCES Niveau(id),
CONSTRAINT reservationparti
FOREIGN KEY (id_participant)
REFERENCES Participant(id),
CONSTRAINT reservationrglmttype
FOREIGN KEY (id_rglmt_type)
REFERENCES Rglmt_type(id)
)
ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS Prestaresa (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
id_prestation SMALLINT UNSIGNED NOT NULL,
id_reservation MEDIUMINT UNSIGNED NOT NULL,
PRIMARY KEY (id),
CONSTRAINT prestaresapresta
FOREIGN KEY (id_prestation)
REFERENCES Prestation(id),
CONSTRAINT prestaresareservation
FOREIGN KEY (id_reservation)
REFERENCES Reservation(id)
)
ENGINE=INNODB; |
Partager