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
| -- ----------------------------------------------------------------------------------
-- T_GROUPE
-- ----------------------------------------------------------------------------------
CREATE TABLE `t_groupe` (
`id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`nom` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`),
INDEX (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_groupe` (`id`, `nom`) VALUES
(1, "Groupe 1"),
(2, "Groupe 2");
-- ----------------------------------------------------------------------------------
-- T_ETABLISSEMENT
-- ----------------------------------------------------------------------------------
CREATE TABLE `t_etablissement` (
`id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`nom` VARCHAR(100) NOT NULL,
`id_groupe` SMALLINT(5) UNSIGNED,
PRIMARY KEY (`id`),
INDEX (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_etablissement` (`id`, `nom`, `id_groupe`) VALUES
(1, "Etablissement 1", 1),
(2, "Etablissement 2", 1),
(3, "Etablissement 3", NULL),
(4, "Etablissement 4", 2);
-- ----------------------------------------------------------------------------------
-- T_PERSONNE
-- ----------------------------------------------------------------------------------
CREATE TABLE `t_personne` (
`id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`civilite` ENUM("","Mr","Mme","Mlle") NOT NULL,
`nom` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
KEY (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_personne` (`id`, `civilite`, `nom`)
VALUES
(1, "Mr", "AA"),
(2, "Mr", "BB"),
(3, "Mme", "CC"),
(4, "Mme", "DD"),
(5, "Mr", "EE");
-- ----------------------------------------------------------------------------------
-- T_APPARTENANCE
-- ----------------------------------------------------------------------------------
CREATE TABLE `t_appartenance` (
`id_personne` SMALLINT(5) UNSIGNED NOT NULL,
`id_etablissement` SMALLINT(5) UNSIGNED NOT NULL,
PRIMARY KEY (`id_personne`, `id_etablissement`),
FOREIGN KEY (`id_personne`) REFERENCES `t_personne` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (`id_etablissement`) REFERENCES `t_etablissement` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_appartenance` (`id_personne`, `id_etablissement`)
VALUES
(3, 1), -- CC appartient à l'établissement 1 du groupe 1
(3, 2), -- CC appartient à l'établissement 2 du groupe 1
(4, 1), -- DD appartient à l'établissement 1 du groupe 1
(5, 3); -- EE appartient à l'établissement 3 |
Partager