Discussion :

[larix]

Bonjour à tous,

Je me sens un peu bête d'exposer mon problème.

Je suis en train de créer une base pour référencer des oiseaux que j'ai sur un tableau Excel. J'ai créeé 3 tables + une table de liaison.

Table 1: bird avec différentes entrées uniques, dont le nom en latin.
Table 2: lang pour mettre les traductions de nom d'oiseau en français, anglais et allemand.
Table 3: class pour y mettre les infos sur l'ordre et la famille
Table 4 bird_class qui fait le lien entre l'identifiant de l'oiseau son ordre et sa famille

C'est sur la table class qui me chicane.

Elle est composée de:
IDClass, id_bird, branche, nom_branche

1, 1, 'ordre', 'Anseriformes'
2, 1, 'famille', 'Anatidés'

La requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT nom_latin, bird_name, nom_branche, description
FROM bird
INNER JOIN langues ON IDBird=langues.id_bird
INNER JOIN bird_class ON IDBird=bird_class.id_bird
INNER JOIN class ON class.id_bird = bird_class.id_class
WHERE lg = 'en'

Elle fonctionne très bien, mais elle me retourne 2 lignes!!!

Alors, j'ai pensée soit faire une table comme ceci: IDClass, ordre, famille
Et ainsi avec LEFT OUTER JOIN je retrouver mes deux termes.

Ou, je crée deux tables, ordre et famille que elle seront liée avec l'ID de la table bird.

Voilà, la conception n'est pas encore au top.

D'avance merci de vos précieux conseils.

Stéphane

[fsmrel]

Bonsoir Stéphane,


Le résultat de votre requête comporte deux lignes, mais quel est son objet ? On peut le subodorer, mais quoi qu’il en soit, on n’a pas de boule de cristal, veuillez fournir le script de création des tables, un jeu d’essai et le résultat attendu.

Je n’y connais rien en ornithologie, mais en jetant un coup d’œil ici, on voit que si un oiseau fait partie d’une famille, celle-ci fait partie d’’un ordre (structure hiérachique), donc vous devriez avoir une table FAMILLE référencée par la table BIRD et une table ORDRE référencée par la table FAMILLE.

Mettez un minimum de rigueur, vous présentez une table LANG, que vous renommez LANGUES dans votre SQL.

[larix]

Bonjour François,

C'est vrai que c'est un petit peu le bordel... j'ai mis de l'ordre, merci de ta pertinence.

Voici le jeu de donnée...

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
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
 
 
-- --------------------------------------------------------
 
--
-- Structure de la table `bird`
--
 
CREATE TABLE IF NOT EXISTS `bird` (
  `IDBird` int(11) NOT NULL AUTO_INCREMENT,
  `ID_ordre` int(11) NOT NULL,
  `ID_famille` int(11) NOT NULL,
  `nom_latin` varchar(128) NOT NULL,
  `description` text NOT NULL,
  `taille_min` tinyint(4) NOT NULL,
  `taille_max` tinyint(4) NOT NULL,
  `envergure_min` smallint(6) NOT NULL,
  `envergure_max` smallint(6) NOT NULL,
  `poids_min` tinyint(3) NOT NULL DEFAULT '0',
  `poids_max` tinyint(4) NOT NULL,
  `nourriture` text NOT NULL,
  `img_defaut` varchar(255) NOT NULL,
  PRIMARY KEY (`IDBird`),
  UNIQUE KEY `IDOiseau` (`IDBird`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
--
-- Contenu de la table `bird`
--
 
INSERT INTO `bird` (`IDBird`, `ID_ordre`, `ID_famille`, `nom_latin`, `description`, `taille_min`, `taille_max`, `envergure_min`, `envergure_max`, `poids_min`, `poids_max`, `nourriture`, `img_defaut`) VALUES
(1, 1, 2, 'Cygnus bewickii', 'Le Cygne de Bewick (Cygnus columbianus bewickii) est une sous-espèce sibérienne du Cygne siffleur (Cygnus columbianus).', 107, 127, 186, 211, 3, 0, '', 'http://upload.wikimedia.org/wikipedia/commons/thumb/f/f8/Bewicks.swan.2.arp.750pix.jpg/290px-Bewicks.swan.2.arp.750pix.jpg');
 
-- --------------------------------------------------------
 
--
-- Structure de la table `bird_class`
--
 
CREATE TABLE IF NOT EXISTS `bird_class` (
  `Link_TB_TC` int(11) NOT NULL AUTO_INCREMENT,
  `id_bird` int(11) NOT NULL,
  `id_class` int(11) NOT NULL,
  PRIMARY KEY (`Link_TB_TC`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
--
-- Contenu de la table `bird_class`
--
 
INSERT INTO `bird_class` (`Link_TB_TC`, `id_bird`, `id_class`) VALUES
(1, 1, 1),
(2, 1, 2);
 
-- --------------------------------------------------------
 
--
-- Structure de la table `class`
--
 
CREATE TABLE IF NOT EXISTS `class` (
  `IDClass` int(11) NOT NULL AUTO_INCREMENT,
  `id_bird` int(11) NOT NULL,
  `branche` enum('Regne','Embranchement','Classe','Ordre','Famille') NOT NULL,
  `nom_branche` varchar(32) NOT NULL,
  PRIMARY KEY (`IDClass`),
  UNIQUE KEY `IDClassification` (`IDClass`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
--
-- Contenu de la table `class`
--
 
INSERT INTO `class` (`IDClass`, `id_bird`, `branche`, `nom_branche`) VALUES
(1, 1, 'Ordre', 'Anseriformes'),
(2, 1, 'Famille', 'Anatidés');
 
-- --------------------------------------------------------
 
--
-- Structure de la table `lang`
--
 
CREATE TABLE IF NOT EXISTS `lang` (
  `IDLang` int(11) NOT NULL AUTO_INCREMENT,
  `id_bird` mediumint(8) unsigned NOT NULL,
  `lg` enum('fr','de','en') NOT NULL DEFAULT 'fr',
  `bird_name` varchar(128) NOT NULL,
  PRIMARY KEY (`IDLang`),
  UNIQUE KEY `IDLangues` (`IDLang`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
--
-- Contenu de la table `lang`
--
 
INSERT INTO `lang` (`IDLang`, `id_bird`, `lg`, `bird_name`) VALUES
(0, 1, 'fr', 'Cygne de Bewick'),
(2, 1, 'en', 'Bewick''s Swann');

Je remets la requête qui fonctionne mais retourne 2 lignes:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT nom_latin, bird_name, nom_branche, description
FROM bird
INNER JOIN langues ON IDBird=langues.id_bird
INNER JOIN bird_class ON IDBird=bird_class.id_bird
INNER JOIN class ON class.id_bird = bird_class.id_class
WHERE lg = 'en'

J'espère être un peu plus clair.

Est-ce pas plus simple de créer une table ORDER et une seconde FAMILLE. Me semble-t-il ce sera plus souple et simple?

J'en suis vraiment au début, et je n'ai plus touché de requête de 5 ans après un gros Burn Out...

Encore merci.

Stéphane

[fsmrel]

Bonsoir Stéphane,

La requête :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT nom_latin, bird_name, nom_branche, description
FROM bird
INNER JOIN langues ON IDBird=langues.id_bird
INNER JOIN bird_class ON IDBird=bird_class.id_bird
INNER JOIN class ON class.id_bird = bird_class.id_class
WHERE lg = 'en' ;

Elle fonctionne très bien, mais elle me retourne 2 lignes!!!

C’est normal !

Décomposons. N’utilisons d’abord que la jointure des tables BIRD et LANGUES (que j’ai renommée LANG pour être en phase avec l’instruction CREATE TABLE) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT nom_latin, bird_name
FROM BIRD INNER JOIN LANG ON IDBird= LANG.id_bird
WHERE lg = 'en' ;

Au résultat on a une seul ligne, tout va bien :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Nom_latin                  bird_name           
Cygnus bewickii            Bewick's Swann

Si on enrichit cette jointure en faisant intervenir la table BIRD_CLASS :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT nom_latin, bird_name, bird_class.id_class 
FROM BIRD INNER JOIN LANG ON IDBird= LANG.id_bird
          INNER JOIN BIRD_CLASS ON IDBird = BIRD_CLASS.id_bird
WHERE lg = 'en' ;

Alors ça se gâte, on produit deux lignes, une par classe, et c’est normal puisque dans la table BIRD_CLASS, à la valeur 1 prise par la colonne id_bird, vous associez deux valeurs de la colonne id_class :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4

Nom_latin                  bird_name          id_class 
Cygnus bewickii            Bewick's Swann     1
Cygnus bewickii            Bewick's Swann     2

La modélisation est donc à revoir. En attendant, vous pouvez vous en sortir au moyen d’une tambouille peu ragoûtante, au moyen de la fonction (propre à MySQL) GROUP_CONCAT :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT nom_latin, bird_name, GROUP_CONCAT(branche, ' = ', nom_branche, ' ; ') AS ordre_famille , description
FROM BIRD INNER JOIN LANG ON IDBird= LANG.id_bird
          INNER JOIN BIRD_CLASS ON IDBird = BIRD_CLASS.id_bird
          INNER JOIN CLASS ON CLASS.id_bird = BIRD_CLASS.id_class
WHERE lg = 'en' ;

Au résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3

Nom_latin          bird_name        ordre_famille                                   description 
Cygnus bewickii    Bewick's Swann    Ordre = Anseriformes ; ,Famille = Anatidés ;   Le Cygne de Berwick (Cygnus columbianus bewickii) est une sous-espèce sibérienne du Cygne siffleur (Cygnus columbianus). 

Est-ce pas plus simple de créer une table ORDER et une seconde FAMILLE. Me semble-t-il ce sera plus souple et simple?

C’est surtout plus sain. Souvenez-vous de ce que j’avais écrit :

vous devriez avoir une table FAMILLE référencée par la table BIRD et une table ORDRE référencée par la table FAMILLE.

Et là, plus besoin de tambouille...


On reprend tout ça un peu plus tard, j’ai d’autres remarques à faire...

[dehorter olivier]

là, c'est un petit peu mon domaine

Ce type de gestion fait partie de ma base de données. Je n'ai aucun problème pour passer la structure de de ma base qui gère cette partie (en privé ? à moins que cela puisse intéresser d'autres personnes, même si cela n'a rien de révolutionnaire)

Table 1: bird avec différentes entrées uniques, dont le nom en latin.
Table 2: lang pour mettre les traductions de nom d'oiseau en français, anglais et allemand.
Table 3: class pour y mettre les infos sur l'ordre et la famille
Table 4 bird_class qui fait le lien entre l'identifiant de l'oiseau son ordre et sa famille

Des problèmes dans la structuration parce que les noms scientifiques changent, de même que les noms vernaculaires (d'autant qu'il en existe souvent plusieurs par langue).


et comme le fsmrel, c'est typiquement une structure hiérarchique.

Je me suis beaucoup inspiré des articles de SQLPro pour cela . Même si je n'ai pas opté pour la structuration intervallaire (pour conserver les historiques dans les changements taxonomiques - pas les simples changements de noms, mais bien les changements de familles, ou autres...), même si je pense cela doit être possible parce que la relation est unique à un moment donné.

J'ai opté pour une structuration qui ressemble plus à celle que tu as adopté plutôt que celle proposée par fsmrel qui scinde en plusieurs tables toujours à cause de la gestion des changements dans la taxonomie

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE E_TAXON (
    ID_TAXON        INTEGER NOT NULL,
    NIVEAU          SMALLINT DEFAULT 0 NOT NULL, -- 1 : Classe, 2 : Ordre, 3 : Sous-Ordre, 4 : Famille, 5 : Sous-Famille, 6 : Espece, 7 : Sous-Espece
    ACTIF           BOOLEEN NOT NULL 
    ID_USER         INTEGER NOT NULL,
    DATE_MODIF      TIMESTAMP DEFAULT current_timestamp NOT NULL
);

et une table qui gère donc leur relation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE E_RELATION (
    ID_E_RELATION  INTEGER NOT NULL,
    ID_TAXON       INTEGER NOT NULL,
    ID_TAXON_PERE  INTEGER,
    ACTIF          BOOLEEN NOT NULL /* BOOLEEN = SMALLINT DEFAULT 0 NOT NULL CHECK(VALUE between 0 and 1) */,
    DATE_MODIF     TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

Ensuite j'utilise la récursivité pour reconstruire la hiérarchisation.

[fsmrel]

Bonsoir Stéphane et Olivier,

Des problèmes dans la structuration parce que les noms scientifiques changent, de même que les noms vernaculaires

Si ce sont que les noms qui changent, ça n’est pas très compliqué à prendre en considération du point de vue de l’historisation, on modélise le « depuis » et le « durant » tel que c’est abordé dans la théorie relationnelle, je vous renvoie à l’ouvrage de référence de C. J. Date, H. Darwen et N. A. Lorentzos : Temporal Data and the Relational Model (voyez aussi ici où j’aborde le sujet).

Mais vous écrivez par ailleurs :

Même si je n'ai pas opté pour la structuration intervallaire (pour conserver les historiques dans les changements taxonomiques - pas les simples changements de noms, mais bien les changements de familles, ou autres...).

Ce que j’interprète par : le dahu du Jura, attaché à telle sous-espèce, peut à une date donnée changer de sous-espèce, et plus généralement, quel que soit son niveau, un élément (taxon ?) peut changer de parent. Par exemple, à une date donnée, l’espèce E1, attachée à la sous-famille SF1 de la famille F1 du sous-ordre SO1, peut changer de rattachement et se retrouver rattachée à la sous-famille SF2 de la famille F1, voire à la sous-famille SF3 de la famille F2 du sous-ordre SO1, voire du sous-ordre SO2. Ce genre de scénario — où l'on doit évidemment historiser les changements — est-il possible ? Dans le contexte de récursivité, Stéphane pourrait être découragé s’il devait s’y plonger...

Stéphane, votre avis ?


Sinon, c’est pour moi la journée des oiseaux ! J’ai récupéré près de mon PC un ramier d’environ 3 semaines, tombé de la cheminée, et j’ai heureusement trouvé un organisme auquel j’ai pu le confier (la société protectrice des oiseaux de ville), j’irai voir dans un mois ce qu’il devient (il paraît qu’il sera magnifique).

[larix]

Un grand merci à tous les deux!!!

Je n'aurais pas pensé que me petite question prenne cette envergure.

Vous m'avez fait réfléchir à la solution la plus simple possible.

J'aurais pu faire 2 requêtes et traiter les résultats en PHP, mais cette solution n'est pas optimale pour les mises à jour.

Donc, j'ai créé une table CLASS_ORDER et une seconde CLASS_FAMILY. La gestion s'en trouve plus simple.

Voici les nouvelles tables:

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
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
 
 
CREATE TABLE IF NOT EXISTS `BIRD` (
  `IDBird` int(11) NOT NULL AUTO_INCREMENT,
  `ordre_ext` varchar(50) NOT NULL DEFAULT '0',
  `ID_ordre` int(11) NOT NULL,
  `ID_famille` int(11) NOT NULL,
  `nom_latin` varchar(128) NOT NULL,
  `description` text NOT NULL,
  `taille_min` tinyint(4) NOT NULL,
  `taille_max` tinyint(4) NOT NULL,
  `envergure_min` smallint(6) NOT NULL,
  `envergure_max` smallint(6) NOT NULL,
  `poids_min` tinyint(3) NOT NULL DEFAULT '0',
  `poids_max` tinyint(4) NOT NULL,
  `nourriture` text NOT NULL,
  `img_defaut` varchar(255) NOT NULL,
  UNIQUE KEY `IDBird` (`IDBird`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
--
-- Contenu de la table `BIRD`
--
 
INSERT INTO `BIRD` (`IDBird`, `ordre_ext`, `ID_ordre`, `ID_famille`, `nom_latin`, `description`, `taille_min`, `taille_max`, `envergure_min`, `envergure_max`, `poids_min`, `poids_max`, `nourriture`, `img_defaut`) VALUES
(1, '0', 1, 2, 'Cygnus bewickii', 'Le Cygne de Bewick (Cygnus columbianus bewickii) est une sous-espèce sibérienne du Cygne siffleur (Cygnus columbianus). ', 107, 127, 186, 211, 3, 0, '', 'http://upload.wikimedia.org/wikipedia/commons/thumb/f/f8/Bewicks.swan.2.arp.750pix.jpg/290px-Bewicks.swan.2.arp.750pix.jpg');
 
-- --------------------------------------------------------
 
--
-- Structure de la table `CLASS_FAMILY`
--
 
CREATE TABLE IF NOT EXISTS `CLASS_FAMILY` (
  `IDFamily` int(11) NOT NULL AUTO_INCREMENT,
  `family_fr` varchar(32) NOT NULL DEFAULT '0',
  `family_lt` varchar(32) NOT NULL DEFAULT '0',
  `especes_en` varchar(32) NOT NULL DEFAULT '0',
  `especes_fr` varchar(32) NOT NULL DEFAULT '0',
  UNIQUE KEY `IDFamille` (`IDFamily`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=70 ;
 
--
-- Contenu de la table `CLASS_FAMILY`
--
 
INSERT INTO `CLASS_FAMILY` (`IDFamily`, `family_fr`, `family_lt`, `especes_en`, `especes_fr`) VALUES
(1, 'Anatidés', 'Anatidae', 'Ducks, Geese and Swans', 'Oies – cygnes – canards'),
(2, 'Phasianides', 'Phasianidae', 'Pheasants and allies', 'Faisans – Perdrix'),
(3, 'Tetraonidés', 'Tetraonidae', 'Grouses', 'Tétras – Gélinottes');
 
-- --------------------------------------------------------
 
--
-- Structure de la table `CLASS_ORDER`
--
 
CREATE TABLE IF NOT EXISTS `CLASS_ORDER` (
  `IDorder` int(11) NOT NULL AUTO_INCREMENT,
  `order_fr` varchar(32) DEFAULT NULL,
  `order_lt` varchar(32) DEFAULT NULL,
  UNIQUE KEY `IDordre` (`IDorder`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=26 ;
 
--
-- Contenu de la table `CLASS_ORDER`
--
 
INSERT INTO `CLASS_ORDER` (`IDorder`, `order_fr`, `order_lt`) VALUES
(1, 'Anseriformes', ''),
(2, 'Galliformes', ''),
(3, 'Charadriiformes', ''),
(4, 'Phoenicopteriformes', '');
 
-- --------------------------------------------------------
 
--
-- Structure de la table `LANG`
--
 
CREATE TABLE IF NOT EXISTS `LANG` (
  `IDLang` int(11) NOT NULL AUTO_INCREMENT,
  `id_bird` mediumint(8) unsigned NOT NULL,
  `lg` enum('fr','de','en') NOT NULL DEFAULT 'fr',
  `bird_name` varchar(128) NOT NULL,
  PRIMARY KEY (`IDLang`),
  UNIQUE KEY `IDLangues` (`IDLang`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
--
-- Contenu de la table `LANG`
--
 
INSERT INTO `LANG` (`IDLang`, `id_bird`, `lg`, `bird_name`) VALUES
(0, 1, 'fr', 'Cygne de Bewick'),
(2, 1, 'en', 'Bewick''s Swann');

Et la requête qui va avec...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT nom_latin, bird_name, family_lt, order_fr, description
FROM BIRD INNER JOIN LANG ON IDBird= LANG.id_bird
          INNER JOIN CLASS_FAMILY ON BIRD.ID_famille = CLASS_FAMILY.IDFamily
          INNER JOIN CLASS_ORDER ON BIRD.ID_ordre = CLASS_ORDER.IDorder
WHERE 
    lg = 'en';

Je vais pouvoir continuer sereinement.

Stéphane

[larix]

là, c'est un petit peu mon domaine

Ce type de gestion fait partie de ma base de données. Je n'ai aucun problème pour passer la structure de de ma base qui gère cette partie (en privé ? à moins que cela puisse intéresser d'autres personnes, même si cela n'a rien de révolutionnaire)

Merci beaucoup de ton analyse très poussée et très pertinente. Tu as probablement une solution exemplaire , mais je n'ai pas trop envie de me lancer dans un sujet que je ne maîtrise pas assez. En définitive, je veux rester dans quelque chose de simple que je peux maîtriser et surtout évolutif... on ne sais jamais.

Ma curiosité me pour tout de même à savoir comment tu procéderais pour modéliser ce cas.

Stéphane