Discussion :

[Sh4dow49]

Bonjour,

j'aurais voulu savoir s'il était possible de rendre des champs multiples sous MySQL.

Je donne un exemple pour me faire comprendre :

Je stocke mes employés dans une table construite de la façon suivante dans ma base de données :
id
nom
prenom
mail

Exemple classique.
Maintenant, je voudrais savoir comment permettre à mon champ mail d'avoir plusieurs valeurs pour la même personne. Exemple :
Je souhaite enregistrer jean dupont.
Nom : dupont
prenom : jean
mail : jd@shadow.com et jean.dupont@shadow.com

Comment n'enregistrer qu'une seule fois jean dupont mais en insérant les 2 adresses mails en même temps ?

Si possible, je voudrais une solution qui n'engage pas de créer une autre table spéciale pour les mails, car la base de données que j'utilise comporte une trentaine de tables avec beaucoup de champs dans ce cas la ...

Egalement, l'option "mettre 2 champs mail1 / mail2" dans la table est à éviter, car un autre employé peut avoir plus de 2 adresses mails (le nombre est variable selon le cas ... )

Merci de votre aide.

ps : je ne sais pas si je suis dans la bonne partie du forum mysql, j'ai hésité avec la partie requête, dites moi si je dois reposter ailleurs

[Alain Defrance]

Bonjour,
Tu as deux solutions qui s'offrent a toi, une mauvaise et une bonne.

La mauvaise : champ multi-valué, c'est à dire que tu va utiliser un séparateur qui te permettra de découper la chaine stocké, et récupérer ainsi toute tes adresse stocké dans le même champ. Ceci est très vivement déconseillé car la première forme normal n'est même pas respecté. Je me permet quand même de t'indiquer cette technique puisque même des entreprises l'utilisent (ce qui n'est en rien une référence ...).

La bonne : Utiliser une table mail, chose que tu voudrais éviter je sais mais c'est pourtant la meilleur solution. Le fait qu'il y ait 30 tables n'est pas gênant et raison de plus, une de plus ou de moins, si c'est pour que ta base soit le plus pratique possible alors il ne faut pas hésiter. Si c'est pour éviter des jointures, je te conseil d'utiliser des vues

[hed62]

car la première forme normal n'est même pas respecté.

Selon fsmrel, dans la plus pure théorie, si. Parcequ'on peut considérer "toto@titi.fr;truc@muche.fr" comme une valeur (ou alors je n'ai pas compris son explication du moment). M'enfin c'est très très déconseillé quand même

Si possible, je voudrais une solution qui n'engage pas de créer une autre table spéciale pour les mails, car la base de données que j'utilise comporte une trentaine de tables avec beaucoup de champs dans ce cas la ...

Comme le dit kazou, peut importe d'avoir un grand nombre de tables, ce qui compte c'est la cohérence des données, la maintenabilité de la base.
Je suis sur un projet qui a généré environ 200 tables, et cela ne me choque pas.

[Sh4dow49]

Le problème n'est pas le nombre de tables mais le nombre de champs à rendre multiples.

Si je créais une table pour chaque champ à rendre multiple, je pense que je passerai rapidement de 30 à 100 tables, voir plus. C'est pour ça que je voudrais éviter cette solution.

De plus, comme le nombre de valeurs pour ces champs est indéterminé, il me faudrait des tables supplémentaires d'une dizaine de champs chacune, coté optimisation ce n'est peut-être pas le top ...

Cependant il est vrai que l'autre solution de stocker toutes les valeurs séparées par un simple point virgule ou autre n'est pas super non plus, surtout au niveau du code cette fois.

[hed62]

Si je créais une table pour chaque champ à rendre multiple, je pense que je passerai rapidement de 30 à 100 tables, voir plus. C'est pour ça que je voudrais éviter cette solution.

Je doute qu'il y ait autant d'entités différentes.. mais c'est possible.

De plus, comme le nombre de valeurs pour ces champs est indéterminé, il me faudrait des tables supplémentaires d'une dizaine de champs chacune, coté optimisation ce n'est peut-être pas le top ...

La je ne comprend vraiment pas... il te suffit d'une table supplémentaire, avec deux champs (l'id de la personne dans une colonne, le mail dans l'autre) et c'est tout...

[Sh4dow49]

Oui en effet, j'ai mal réfléchis sur ce coup la ^^
Je voyais une table ou on enregistrait toutes les adresses dans le même enregistrement mais le plus simple est un enregistrement par ligne.

Je pense que je n'ai pas le choix, je vais devoir opter pour cette solution.

Je laisse le topic ouvert encore un peu pour si quelqu'un a une autre idée, dans le cas contraire merci pour vos avis.

[JPhi33]

Sh4dow49,

Je laisse le topic ouvert encore un peu pour si quelqu'un a une autre idée

N'espère pas trop que dans le forum Modélisation quelqu'un puisse te conseiller quelque chose qui contreviendrait aux principes fondamentaux permettant de construire des bases de données rationnelles, optimisées et cohérentes.

[fsmrel]

Citation:
car la première forme normal n'est même pas respecté.
Selon fsmrel, dans la plus pure théorie, si. Parcequ'on peut considérer "toto@titi.fr;truc@muche.fr" comme une valeur (ou alors je n'ai pas compris son explication du moment). M'enfin c'est très très déconseillé quand même

Exact hed62. Supposons que l’on crée une table Personne, dont une des colonnes conserve la trace des salaires de l’année, mois par mois :

Code :

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

1
2
3
4
5
6
Create table Personne (
    PersonneId     Integer    Not Null
  , PesonneNom     Varchar(48)   Not Null
  , PesonneDept    Varchar(48)   Not Null
  , Salaire        Varchar(255) Not Null
) ;

Rien n’empêche d’exécuter l’instruction suivante :

INSERT INTO Personne Values (1, "Cézig", "3000; 2997; ...; 3200", ...)

Signifiant qu’en janvier, Cézig a eu droit à 3000 euros en janvier, 2997 euros en février, etc.

Stricto sensu, la 1re forme normale est respectée, car le type Varchar est scalaire, et comme dirait l’autre, il encapsule la chaîne de caractères "3000; 2997; ...; 3200". Le problème est que si l’on veut calculer la moyenne des salaires du mois de mars pour l’ensemble des salariés ou savoir quels salariés d’un même département ont le même salaire annuel, on est mal, alors qu'avec SQL on serait infiniment plus à l'aise dès lors que les salaires feraient l'objet d'une table dédiée.

Évidemment, c’est très très déconseillé de structurer comme je viens de le faire, même si tel fournisseur de SGBD donne à l’occasion le mauvais exemple. Ainsi, la table du catalogue de DB2 for z/OS comporte une table appelée SYSCOPY qui est utilisée pour les opérations de recovery et comporte une colonne DSVOLSER de type VARCHAR(1784), utilisée pour stocker la liste des numéros à 6 chiffres (séparés par des virgules) des disques sur lesquels reposent les données d’une table.

En passant, réfléchissez aux techniques à employer dans le cas des mises à jour de la table Personne (ajouts, modifications, suppressions). Un des enjeux est celui de la simplicité des opérations dans un contexte ensembliste.

Jadis, nous utilisions des SGBD non relationnels et nous n'avions pas d'état d'âme à mettre en oeuvre des attributs multivalués car nous manipulions les enregistrements (terme utilisé avec ces SGBD) un par un. Le défi des SGBDR fut de permettre de manipuler des ensembles, et pour nous, informaticiens de l'époque, ce fut une véritable révélation qui nous conduisit à repenser complètement la manière de structurer les données pour profiter pleinement de la puissance apportée par le Modèle relationnel de données : Adieu ! les attributs multivalués. Ne cherchons pas à revenir à une époque révolue.

Si je créais une table pour chaque champ à rendre multiple, je pense que je passerai rapidement de 30 à 100 tables, voir plus

Un SGBD normalement constitué supporte cela. Ainsi, j’ai eu à mettre en place des bases de données de 1500 tables et plus, sans que ce nombre ne gêne en quoi que ce soit (toujours avec DB2 for z/OS). Et bien des bases de données opérationnelles comportent une foultitude de tables.

Je pense que je n'ai pas le choix, je vais devoir opter pour cette solution

C’est ce qu’il y a de mieux à faire. Dans l’exemple des salaires, j’ai mis en évidence la difficulté à manipuler les données, mais il faut penser aussi à leur intégrité (référentielle notamment) : La structure des données est une chose, il s’agit d'une sorte de point d'entrée qui conditionne tout le reste et, par le canal de ce reste, le succès d'un projet...

[Alain Defrance]

la 1re forme normale est respectée, car le type Varchar est scalaire, et comme dirait l’autre, il encapsule la chaîne de caractères "3000; 2997; ...; 3200"

Dans ce cas il absolument impossible de violer cette forme normal (a moins de ne pas mettre d'identifiant). Je suis entièrement d'accord que pour un et un seul identifiant correspond un seule valeur pour ce champ, = une chaine de caractère. Ça ne deviens donc un point de vue et ça dépend si on considère le sens de la chaine, ou le fait que ça soit une seule chaine.
Mais je comprend ton point de vue et tu as probablement raison

[fsmrel]

ça dépend si on considère le sens de la chaine, ou le fait que ça soit une seule chaine

Je suppose que vous voulez dire qu’à la lettre la 1re forme normale est scrupuleusement respectée, mais qu’elle est copieusement violée dans l’esprit, auquel cas il est certain qu’on ne peut que recommander de s’attacher à l’esprit.

Dans ce cas il absolument impossible de violer cette forme normal (a moins de ne pas mettre d'identifiant).

Puisqu’on parle de 1re forme normale, on se situe dans le cadre de la théorie relationnelle et dans cette théorie, on ne parle pas d’identifiant mais de clé candidate. Cela dit, je ne comprends rien à l’amalgame que vous faites de la normalisation et de la clé.

Par définition, chaque relation (table en SQL) comporte une clé, sinon ça n’est pas une relation mais un sac (bag), c'est-à-dire quelque chose qui est hors sujet.

La 1re forme normale ne concerne que les relations (et les variables dont elles sont les valeurs). Prenons la définition de Gardarin, on ne peut plus exacte et concise :

Une relation est en première forme normale si tout attribut contient une valeur atomique

atomique étant synonyme de scalaire. Voyez-vous figurer des termes tels que clé ou identifiant dans la définition ?

Qu'avez-vous exactement en tête ?

Maintenant, reprenons votre affirmation concernant les types scalaires :

Dans ce cas il absolument impossible de violer cette forme normale

Dans le cas d’un type scalaire comme Varchar, c’est certain, mais si vous utilisez des types non scalaires, vous violez la 1NF. Manifestement, la norme SQL n’en a cure et propose le type ARRAY ( c'est-à-dire TABLEAU), qui expose le niveau subatomique (on "désencapsule"). Un SGBD comme PostgreSQL en permet du reste la mise en oeuvre : Cf. PostgreSQL, Arrays.

Est-ce grave ? L'auteur de l'article auquel je vous renvoie donne son sentiment :

... searching for specific array elements may be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale up better to large numbers of elements.

C'est-à-dire :

... chercher des éléments particuliers dans un tableau peut être le signe d’une mauvaise conception de la base de données. Étudiez la mise en place d’une table séparée dont chaque ligne soit affectée à chaque élément du tableau. La recherche sera plus simple et cela sera plus adapté si ces éléments sont nombreux.

Et j'ajouterais : pensez à l'intégrité à l'occasion des mises à jour...

Je ne voudrais pas paraître strictement dogmatique, et le type ARRAY n'est certainement pas une mauvaise chose, mais il faut étudier l'ensemble des conséquences de chacun de ses choix, en sortant du cadre purement structurel.

[JPhi33]

Et bien des bases de données opérationnelles comportent une foultitude de tables.

Dans l'entreprise pour laquelle je travaille, chaque domaine fonctionnel est responsable de 10 à 30 tables de la base de données. Il existe une centaine de domaines environ, ce qui implique l'existence de 1000 à 3000 tables sans que l'intégrité ni la cohérence des données n'aient à en souffrir (heureusement pour notre potefeuille, il s'agit d'une banque !) Le SGBD est DB2 for z/OS.

[Sh4dow49]

Donc si je reprends rapidement :

l'enregistrement des différentes valeurs d'un cas dans le même enregistrement avec comme séparation un point virgule (par exemple) peut se faire mais reste très peu pratique à manipuler, notamment lors d'update des données.
J'avais de toute façon écarté cette solution dès le départ car nous sommes plusieurs à développer le projet (en php) et certains ne sont que débutants en la matière (bien que je ne soit pas expert non plus).

La deuxième solution qui s'offre donc est la suppression du champ dans sa table d'origine pour créer à la place une table dédiée pour ce type de valeur. L'utilisation en est beaucoup plus simple. Malgré le fait que beaucoup de tables risquent de s'ajouter, cette solution reste la meilleur des deux.


J'aurai maintenant une question sur la composition de la table.

Reprenons l'exemple des salaires exprimé par fsmrel
La table d'origine était :

Code :

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

1
2
3
4
5
6
7
Create table Personne (
    PersonneId     Integer    Not Null
  , PesonneNom     Varchar(48)   Not Null
  , PesonneDept    Varchar(48)   Not Null
  , Salaire        Varchar(255) Not Null
) ;

Nous supprimons donc le champ Salaire dans cette table pour créer une table Salaire.

Je pense à deux façons de créer la table

Première solution :
clef primaire : PersonneId
champ : Salaire
Dans ce cas ci, la clef primaire pointerait vers la clef primaire de la table Personne.

Deuxième solution
clef primaire : SalaireId
champ : Salaire
clef étrangère : PersonneId
Dans ce cas la, c'est la clef étrangère qui pointe vers la clef primaire de la table Personne.

Je ne sais pas si il y a une façon prédéfinie de faire et je préfère poser la question car le projet est pour une entreprise commerciale, je voudrais donc créer la base de données en respectant au mieux les règles de modélisation

Qu'en pensez vous donc ?

Merci d'avance, et merci pour vos réponses jusqu'à présent également !

[fsmrel]

Bonjour,

Première solution :
clef primaire : PersonneId
champ : Salaire
Dans ce cas ci, la clef primaire pointerait vers la clef primaire de la table Personne.

Avec cette solution, en vertu de la contrainte d’unicité des clés, une personne ne peut avoir qu’un salaire, or d’après les exemples utilisés, elle peut en avoir plusieurs.
Il faut donc prévoir un attribut supplémentaire, disons Seq (pour séquenceur), permettant de distinguer les différents salaires :

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
 
Create table Personne (
    PersonneId     Integer       Not Null
  , PesonneNom     Varchar(48)   Not Null
  , PesonneDept    Varchar(48)   Not Null
  , Constraint PersonnePK Primary Key (PersonneId)
) ;

Create table Salaire (
    PersonneId     Integer       Not Null
  , Seq            Integer       Not Null
  , Salaire        Integer       Not Null
  , Constraint SalairePK Primary Key (PersonneId, Seq)
  , Constraint SalaireFK Foreign Key (PersonneId) References Personne
) ;

Au passage, vous aurez noté que l’attribut Salaire est du type INTEGER et non plus VARCHAR, ce qui est quand même plus pertinent.

Exemple en extension :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Personne (PersonneId   PersonneNom   PersonneDept  ...)
             1           Tézig1          d1
             2           Cézig1          d1

Salaire (PersonneId   Seq   Salaire ...)
             1         1      3000
             1         2      2997 
             1         3      3200
             2         1      2500
             2         2      2600 
             2         3      2500
            ...       ...     ...

Deuxième solution
clef primaire : SalaireId
champ : Salaire
clef étrangère : PersonneId
Dans ce cas la, c'est la clef étrangère qui pointe vers la clef primaire de la table Personne.

Cette solution est possible, mais moins satisfaisante que celle que j’ai présentée :

1) Au plan sémantique, on perd de vue que Salaire est une propriété multivaluée de Personne. Conceptuellement Salaire perd son véritable statut d’entité-type faible pour passer à celui d’entité-type forte, ce que Salaire n’est pas.

2) Au niveau physique, vous aurez intérêt à prévoir deux index au lieu d’un pour la table Salaire, mais ceci sera un facteur de ralentissement des opérations de mise à jour.

3) Quand vous en viendrez à mesurer les performances des requêtes, vous observerez que pour bénéficier de l’effet "cluster" résultant du regroupement dans une même page physique de tous les salaires d’une personne, ce regroupement devra être effectué au moyen de l’index branché sur l’attribut PersonneId, c'est-à-dire que cet index devient indispensable.

...

[Sh4dow49]

si j'ai bien compris donc, la nouvelle table aurait pour primary key PersonneId ET Seq, et PersonneId serait de plus lié à la primary key PersonneId de la table Personne.

C'est une solution intéressante.
Par contre, je sais que ce n'est pas trop le bon endroit dans le forum mais je voudrais votre avis sur la façon de coder l'insertion d'un nouvel enregistrement via le code d'une page html.

Sans rentrer dans les détails d'un langage précis, je ne vois pas trop comment faire cela.

J'ai bien une idée flou, je vous l'expose vous me donnerez votre avis :
Pour PersonneId pas de problème on met le même que celui de la personne concernée.
Pour la séquence, je pensais à une variable partant de zéro que l'on incrémente à chaque valeur, en prenant compte que la liste des valeurs est stockée dans un tableau par exemple (et donc insérer les enregistrements via une boucle for)

Si vous avez une meilleur solution ou si je dois poster cela ailleurs dites le moi (je demande quand même ici car cela permettra de garder le fil de la discussion)

[fsmrel]

si j'ai bien compris donc, la nouvelle table aurait pour primary key PersonneId ET Seq, et PersonneId serait de plus lié à la primary key PersonneId de la table Personne

Exact.
A ce sujet, j’ai ajouté la clause "References Personne" que j’avais omise concernant la clé étrangère SalaireFK.

Par ailleurs, Salaire étant propriété multivaluée de Personne, il est évident que la suppression d'une personne donnée entraîne la suppression automatique de ses salaires. L'instruction Create Table Salaire devient donc la suivante :

Code :

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

1
2
3
4
5
6
7
8
Create table Salaire (
    PersonneId     Integer       Not Null
  , Seq            Integer       Not Null
  , Salaire        Integer       Not Null
  , Constraint SalairePK Primary Key (PersonneId, Seq)
  , Constraint SalaireFK Foreign Key (PersonneId)  References Personne
         On Delete Cascade
) ;

je voudrais votre avis sur la façon de coder l'insertion d'un nouvel enregistrement via le code d'une page html

Désolé, je ne connais html...

Pour la séquence, je pensais à une variable partant de zéro que l'on incrémente à chaque valeur

Je ne connais pas non plus MySQL (décidemment...) mais je suppose qu’il dispose d’un mécanisme d’auto-incrémentation vous évitant de programmer vous-même l’incrémentation. Le mieux est de vous renseigner auprès des cracks de ce SGBD.

[Alain Defrance]

Je suppose que vous voulez dire qu’à la lettre la 1re forme normale est scrupuleusement respectée, mais qu’elle est copieusement violée dans l’esprit, auquel cas il est certain qu’on ne peut que recommander de s’attacher à l’esprit.

Exactement.

Dans le cas d’un type scalaire comme Varchar, c’est certain, mais si vous utilisez des types non scalaires, vous violez la 1NF. Manifestement, la norme SQL n’en a cure et propose le type ARRAY ( c'est-à-dire TABLEAU), qui expose le niveau subatomique (on "désencapsule"). Un SGBD comme PostgreSQL en permet du reste la mise en oeuvre : Cf. PostgreSQL, Arrays.

Je comprend bien mieux votre point de vue et compte tenu de cela on est forcé d'admettre qu'une chaine de caractère représente une seule valeur même si elle en encapsule d'autres.

Qu'avez-vous exactement en tête ?

J'avais une mauvaise définition en tête et pour moi une relation pouvais ne pas posséder d'identifiant, a ce moment la ça restais une relation ne respectant pas merise, alors que c'est vrais, on ne peut considérer une relation en tant que telle si elle ne possède pas d'identifiant, et le problème donc ne se pose pas a cet endroit.

Merci pour ces éclaircissements.

[Sh4dow49]

Ce n'est pas grave pour la fin.

En tout cas merci pour votre aide, j'y vois beaucoup plus clair dans ce que j'ai à faire au niveau de la base de données.