Discussion :

[jecomprendsrien]

Bonjour,

je me pose régulièrement la même question lorsque je créée mes tables. Est ce plus avantageux de créer un champs énuméré dans une table ou bien de créer une autre table et d'y mettre les choix possibles ?

Par exemple pour une couleur de cheveux, vaut il mieux créer un champs ENUM avec valeurs blonds, noirs, roux...ou une table reliée par jointure avec des enregistrements blonds, noirs, roux ?

Quels peuvent être les avantages des 2 solutions svp ?

Au niveau applicatif (java) dans le cas d'un champs de type ENUM il est plus propre d'avoir je pense une Enumeration afin de gérer les possibilités mais niveau maintenabilité c'est moins aisé que d'avoir une table annexe.

En cas de table annexe, cela fait une jointure en plus...

Je précise que je pense avoir entre 5 et 10 valeurs possibles.

Merci pour vos conseils

[fsmrel]

Bonsoir,


Si vous utilisez SQL, sachez qu’il est fait pour travailler verticalement plutôt qu’horizontalement (pensez par exemple aux opérateurs relationnels, JOIN, UNION, RESTRICT, PROJECT, etc., ainsi qu’aux opérateurs d’agrégation, SUM, AVG, MAX, etc.) Par ailleurs, je ne sache pas que le type ENUM fasse partie de ceux qui sont proposés en standard par les SGBD/R, vous aurez donc à le construire (si tant est que votre SGBD vous le permette).

Supposons maintenant que vous ayez une table des personnes : chaque personne a une seule couleur de cheveux (aux chauves près...) Votre table comportera donc une colonne hébergeant des valeurs atomiques (par exemple : "blonds"), mais avec une contrainte de table pour vous assurer que la couleur de cheveux de chaque personne est valide :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE PERSONNE
(
        PersonneId            INT              NOT NULL
      , PersonneNom           VARCHAR(64)      NOT NULL    
      , PersonnePrenom        VARCHAR(64)      NOT NULL 
      , CouleurCheveux        VARCHAR(64)      NOT NULL 
    , CONSTRAINT PERSONNE_PK PRIMARY KEY (PersonneId)
    , CONSTRAINT CouleurCheveux_CHCK01 CHECK (CouleurCheveux IN ('blonds', 'blancs', 'roux', 'noirs', 'blonds vénitien', 'Châtain clair avec des reflets mordorés'))
) ;

Il n’est pas dit que tous les SGBD/R permettent la mise en œuvre de contraintes CHECK, mais quoi qu’il en soit, la solution la plus couramment pratiquée consiste quand même à définir une table des couleurs de cheveux :

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE COULEUR_CHEVEUX
(
        CouleurId             INT              NOT NULL
      , CouleurNom            VARCHAR(64)      NOT NULL    
    , CONSTRAINT COULEUR_CHEVEUX_PK PRIMARY KEY (CouleurId)
) ;

Et établir une contrainte référentielle entre les deux tables :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE PERSONNE
(
        PersonneId            INT              NOT NULL
      , PersonneNom           VARCHAR(64)      NOT NULL    
      , PersonnePrenom        VARCHAR(64)      NOT NULL 
      , CouleurCheveuxId      INT              NOT NULL           
    , CONSTRAINT PERSONNE_PK PRIMARY KEY (PersonneId)
    , CONSTRAINT PERSONNE_COULEUR_CHEVEUX_FK FOREIGN KEY (CouleurCheveuxId)
                 REFERENCES COULEUR_CHEVEUX (CouleurId)
) ;

Un début de jeu d’essai :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
 
INSERT INTO COULEUR_CHEVEUX (CouleurId, CouleurNom) VALUES (1, 'blonds') ;
INSERT INTO COULEUR_CHEVEUX (CouleurId, CouleurNom) VALUES (2, 'blancs') ;
INSERT INTO COULEUR_CHEVEUX (CouleurId, CouleurNom) VALUES (3, 'roux') ;
INSERT INTO COULEUR_CHEVEUX (CouleurId, CouleurNom) VALUES (4, 'noirs') ;
 
INSERT INTO PERSONNE (PersonneId, PersonneNom, PersonnePrenom, CouleurCheveuxId) VALUES (1, 'Martin', 'Albert', 2) ;
INSERT INTO PERSONNE (PersonneId, PersonneNom, PersonnePrenom, CouleurCheveuxId) VALUES (2, 'Dupond', 'Bernard', 3) ;
INSERT INTO PERSONNE (PersonneId, PersonneNom, PersonnePrenom, CouleurCheveuxId) VALUES (3, 'Dupond', 'Carole', 1) ;
INSERT INTO PERSONNE (PersonneId, PersonneNom, PersonnePrenom, CouleurCheveuxId) VALUES (4, 'Durand', 'Denis', 2) ; 
INSERT INTO PERSONNE (PersonneId, PersonneNom, PersonnePrenom, CouleurCheveuxId) VALUES (5, 'LeBlanc', 'Emile', 1) ;

Quelles sont les personnes ayant des cheveux blonds :

Code SQL :

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

1
2
3
SELECT x.PersonnePrenom, x.PersonneNom
FROM   PERSONNE AS x JOIN COULEUR_CHEVEUX AS y ON x.CouleurCheveuxId = y.CouleurId
WHERE  y.CouleurNom = 'blonds' ;

Réponse :

Code :

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

1
2
3
4
PersonnePrenom  PersonneNom
--------------  -----------
Carole          Dupond
Emile           LeBlanc

A noter que si la table PERSONNE est dotée d’un index sur la colonne CouleurCheveuxId, vous aurez une possibilité de filtrage que n’auriez probablement pas avec un type ENUM, lequel pénaliserait donc la performance.


La jointure semble vous inquiéter : rassurez-vous, les optimiseurs des SGBD/R la mignotent et sont attentifs à sa performance. En outre, l’encombrement de la table COULEUR_CHEVEUX est epsilonesque et elle résidera donc en mémoire : les jointures ne seront pas la cause d’une baisse de performance. En tout cas, après une longue carrière de DBA, ce ne sont pas les jointures avec ce genre de table qui m’ont causé des soucis.

[jecomprendsrien]

Bonjour et merci pour cette réponse détaillée.

En fait je connais bien le principe et les notions élémentaires des SGBDR (PK, FK, contraintes...).

Dans mon cas de figure, je vais utiliser PostgreSQL et je peux donc créer un type pour gérer mes énumérations.

J'ai donc le choix entre 2 possibilités, soit créer un nouveau type énuméré, soit créer une nouvelle table comme vous l'avez bien expliqué dans votre message.

Le tout étant maintenant de choisir la bonne méthode...

Or, je ne sais finalement pas trop les raisons qui légitiment telle ou telle méthode, c'est ce que j'aimerais comprendre.

Merci encore pour l'explication

[Richard_35]

Bonjour Jecomprendsrien et Fsmrel,

Personnellement, je considère les champs ENUM comme des "faux amis" qui, à terme, sont la source de beaucoup d'ennuis.

Avec une table par champ ENUM, la maintenance et l'évolutivité sont assurées.

[jecomprendsrien]

Bonjour Richard, merci pour ton avis.

je vais donc m'orienter vers une table supplémentaire comme tu le suggères. Il sera toujours temps de modifier à l'usage...

Merci pour vos avis en tout cas