Bonjour,
J'aurais besoin d'un conseil pour la création d'une base de donnée. Voici le problème :

J'ai trois tables (batiment, local, box) qui se référence l'une l'autre pour déterminer les emplacements d'animaux. Un bâtiment peut contenir plusieurs locaux et un local peut contenir plusieurs box.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
CREATE TABLE batiment (
  id smallint,
  nom varchar(255),
  PRIMARY KEY (id),
) ENGINE=InnoDB;
 
CREATE TABLE locale (
  id smallint,
  batiment smallint,
  nom varchar(255),
  PRIMARY KEY (id),
  FOREIGN KEY (batiment) REFERENCES batiment (id) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB;
 
CREATE TABLE box (
  id smallint,
  locale smallint,
  nom varchar(255),
  PRIMARY KEY (id),
  FOREIGN KEY (locale) REFERENCES locale (id) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB;
 
INSERT INTO batiment VALUES
   (1, "Vésale"),
   (2, "Pasteur");
INSERT INTO local VALUES
   (1, 1, "Locale A"),
   (2, 1, "Locale B"),
   (3, 2, "Locale C");
INSERT INTO box VALUES
   (1, 1, "Box A"),
   (2, 1, "Box B"),
   (3, 3, "Box C");
J'ai une table déplacement qui utilisent ces emplacements. Lorsque je déplace un animal, j'ajoute un nouvel emplacement pour cet animal dans la table déplacement.

Tout serait simple si je ne déplaçait des animaux que de box à box. Malheureusement, je dois parfois encoder des déplacements d'un box vers un local, d'un local vers un bâtiment,... Concrètement, je dois parfois transférer un animal de "Vésale / Local A / Box B" vers "Pasteur / Local C" par exemple.

Je ne peux donc pas juste mettre un champ "box" dans ma table déplacement puisque ça peut parfois être juste un local ou un bâtiment.

Si je met trois champs box, local et batiment dans la table déplacement et que je met des foreign key composite sur chacun d'entre eux, ça ne marchera puisque la foreign key sur box par exemple m'interdira de ne mettre juste un bâtiment et un local sans mettre de box par exemple :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE deplacement (
  id smallint,
  animal smallint,
  date_dépl date,
  batiment smallint,
  locale smallint,
  box smallint,
  PRIMARY KEY (id),
  FOREIGN KEY (animal) REFERENCES animal (id) ON DELETE NO ACTION ON UPDATE CASCADE
  FOREIGN KEY (batiment) REFERENCES batiment (id) ON DELETE NO ACTION ON UPDATE CASCADE
  FOREIGN KEY (batiment, locale) REFERENCES locale (batiment, id) ON DELETE NO ACTION ON UPDATE CASCADE
  FOREIGN KEY (locale, box) REFERENCES box (locale, id) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB;
 
INSERT INTO deplacement VALUES
   (1, 1, "20130923", 2, 3, NULL);
Et ne pas mettre de clé composite n'aurait pas de sens puisque la filiation batiment -> local -> box ne serait plus respectée et on pourrait encoder dans déplacement un local qui se trouve dans un autre batiment que celui encodé.

Si quelqu'un a une idée, je serais fou de joie de l'entendre