Discussion :

[LeFredd]

Bonjour,

Je bloque sur les relations n-m, j'ai besoin de vos aides pour dépatouiller tout ça.

J'ai deux tables article et commande.

1 article peut se trouver dans n commandes
1 commande peut contenir n articles

On est donc dans une relation n-m (ou n-n ?)

J'ai compris que pour ce genre de relation il faut une table intermédiaire mais je peux me tromper.
Donc cette table intermédiaire se nommera article_commande et possèdera une clé primaire composée d'un champ article_id faisant référence à l'id de la table article et un champ commande_id faisant référence à l'id de la table commande.

0ème question : Ma table commande doit comporter quoi comme champs ? id, numéro de commande, articles (représentés comment ? une suite de chaine de caractères comme article1, article15, article3 ?) ou alors dans la table commande il y aura 1 ligne pour chaque article ?) quantité (donc si il y a une ligne par article on pourra remplir la colonne quantité)

1ère question :
Dans un programme 1 personne passe une commande et y insère des articles. Quel est la commande SQL à faire ???
INSERT INTO commande (article1, article3, article15) // Mais comment insérer la quantité des articles commandé ?

2ème question :
Comment la table intermédiaire doit être complété ? A quel moment dans le programme il faut faire un INSERT INTO article_commande (article_id, commande_id) ?

3ème question :
Mais ou mettre la quantité ? Est ce que ce sera un attribut de la table intermédiaire et si oui pourquoi ?

4ème question :
Comment selectionner la totalité d'une commande ? En passant par la table de jointure ?

Donc voilà je suis plus que perdu. J'aimerai si vous voulez bien m'aider bien sûr, comprendre exactement ce que je dois faire dans ce cas.
De la compréhension, à la création des tables avec schémas si possible, aux insertions en SQL. Tout ceci par clarté et je pense que ça pourra aider d'autres personnes comme moi qui débutent. En effet il existe maintes ressources sur le net et dans les livres mais pour les auteurs tout ceci est clair et ça manque méchamment de détails parfois pour l'étudiant.

PS : mon langage favori est le php donc ça peut m'aider à éclaircir encore plus si vous avez des exemples de bonnes pratique dans le code.

Un grand merci d'avance !!!

[fsmrel]

Bonsoir LeFredd,

On est donc dans une relation n-m (ou n-n ?)

Les deux peuvent se dire, ou vous pouvez préférer « plusieurs à plusieurs », « beaucoup à beaucoup », (many to many », et d’autres expressions équivalentes.

J'ai compris que pour ce genre de relation il faut une table intermédiaire mais je peux me tromper.

Avant de parler des tables, au niveau conceptuel (du haut de la dunette), on représente les choses ainsi, de préférence avec un AGL (atelier de génie logiciel) ad-hoc :

1) A la façon de Merise (Je vous engage vivement à lire l'ouvrage de Michel Diviné Parlez-vous Merise ?, gratuit et téléchargeable (Merci Michel !)), vous représentez les choses ainsi (je n’ai pas fait figurer les attributs) :



Diagramme dans lequel COMMANDE et ARTICLE sont des entités-types (ou types d’entités) et COMPOSER une association (ou relation) entre entités-types. Dans la prime jeunesse de la méthode Merise (fin des années soixante-dix), on utilisait plutôt le terme objet plutôt que celui d’entité-type et les théoriciens de la méthode utilisent aussi le terme individu-type.

« 1,n » et « 0,n » symbolisent ce qu’on appelle les cardinalités (minimale et maximale) des associations :

— Une commande est composée d’au moins un article (cardinalité minimale 1) et au plus plusieurs (cardinalité maximale n).

— Un article peut entrer dans la composition d’un article mais pas nécessairement (cardinalité minimale 0 : il y a des articles qui sont des vieux rogatons et qu’on ne commandera jamais), et au plus plusieurs articles (cardinalité maximale n).

En résumé : une commande est composée d’au moins un article et un article peut entrer dans la composition d’une commande, au plus plusieurs.

Notez que les concepteurs ont souvent du mal à trouver le mot juste pour nommer les associations : l’usage est d’utiliser un verbe, en l’occurrence COMPOSER, mais si vous utilisez le substantif COMPOSITION, ce n’est pas moi qui vous en voudrais, et quand on est en mal d’inspiration, on nomme une association comme on peut, par exemple très prosaïquement : COM_ART. Bien sûr, un poète ou quelqu’un d’imaginatif aura plein d’autres mots dans sa besace...


2) A la façon d’UML :



En notant l’absence de graphisme genre ovale pour l’association, ainsi que l’inversion des cardinalités.

J'ai compris que pour ce genre de relation il faut une table intermédiaire mais je peux me tromper.

Oui et non. Si on se situe dans une logique du 2e ordre, une entité-type peut avoir des entités-types pour attributs (une entité-type phagocyte l’autre), mais pas dans une logique du 1er ordre (table intermédiaire nécessaire). En inventant la théorie relationnelle, Ted Codd a traité de cela, il est d’abord parti du principe qu’on pouvait opérer avec des relations (au sens non pas merisien, mais mathématique) ayant d’autres relations pour attributs, pour finalement s’en tenir au 1er ordre. Pour en savoir plus, voyez ses articles fondateurs Derivability, Redundancy and Consistency of Relations Stored in Large Data Banks, et surtout A Relational Model of Data for Large Shared Data Banks. Les pères de SQL (ses collègues chez IBM) ont suivi. Je ne peux pas m’étendre sur le sujet, mais c’est du fait de l’adhésion à cette logique du 1er ordre que depuis Codd on met en oeuvre une table intermédiaire, et les AGL ne font pas autrement quand on passe du niveau conceptuel au niveau dit « logique », plus précisément« relationnel » : au même titre que les entités-types COMMANDE et ARTICLE, l’association COMPOSER fait bien l’objet d’une variable relationnelle (relvar pour abréger), et dont la table SQL est un avatar.

Tout cela peut paraître un peu abscons, mais c'est un peu comme si vous vouliez expliquer le darwinisme en quelques lignes...


3) Pour descendre d’un cran en dessous du conceptuel et nous rapprocher de SQL, utilisons une des notations de MySQL Workbench (voyez l’article que je lui consacré). Il y a bien une table intermédiaire, et dont le nom a été généré automatiquement par concaténation des autres noms (rien ne m’empêche de le changer, pourquoi pas en LIGNE_DE_FACTURE, après tout d’un point de vue sémantique c’est plutôt pertinent) :

— Une commande fait l’objet d’au moins une et au plus plusieurs commande_article et une commande_article se rapporte à au moins et au plus une commande ;

— Un article fait l’objet de 0 à au plus plusieurs commande_article et une commande_article se rapporte à au moins et au plus un article.

Ma table commande doit comporter quoi comme champs ?

L’en-tête d’une table est composé de noms de colonnes (dans le monde des bases de données relationnelles, au niveau théorique, on utilise le terme attribut, et au niveau SQL le terme colonne, le champ faisant partie du vocabulaire des systèmes et langages pré-relationnels, à la façon de DL/1).

Tout d’abord, ces colonnes correspondent aux propriétés naturelles des entités-types COMMANDE et ARTICLE : numéro de commande, date de commande, numéro d’article, libellé de l’article, ce sont les images des données dont l’utilisateur a besoin et dont il a la maîtrise. En plus, d’autres colonnes correspondront aux propriétés artificielles, dépourvues de signification, cachées à l’utilisateur, ou tout du moins sur lesquelles il n’a aucune prise et dont l’objet est d’identifier de façon invariante les entités-types. En ce sens, je reprends ce que j’ai écrit ici et dans nombre de messages (cherchez le mot « Tabourier ») :

A la suite d’Yves Tabourier, J’applique ici une règle fondamentale qu’il a développée dans son ouvrage (De l’autre côté de MERISE, page 80), et c’est une règle d’or qui reste malheureusement trop souvent méconnue, malgré ses plus de 25 ans d’âge :

« ... la fonction d’une propriété est de décrire les objets (et les rencontres), alors que l’identifiant ne décrit rien. Son rôle fondamental est d’être sûr de distinguer deux jumeaux parfaits, malgré des descriptions identiques.
L’expérience montre d’ailleurs que l’usage des “identifiants significatifs” (ou “codes significatifs”) a pu provoquer des dégâts tellement coûteux que la sagesse est d’éviter avec le plus grand soin de construire des identifiants décrivant les objets ou, pis encore, leurs liens avec d’autres objets... »

Pour avoir effectué bien des audits de SI (systèmes d’information), j’ai pu constater ces dégâts, entraînant souvent la refonte des SI victimes de la non application de cette règle de bon sens. Ainsi un identifiant n’est porteur d’aucune signification et donc est invariant.

Exemples. Ci-dessous, CommandeId et ArticleId sont des attributs artificiels (dont les valeurs sont en général calculées par le SGBD, par exemple par auto-incrémentation) :

COMMANDE

Code :

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

1
2
3
4
5
CommandeId    CommandeNumero    CommandeDate
         1    2014-F6-0007      2014-06-04
         2    2014-A3-0145      2014-09-30
       ...    ...               ...

ARTICLE

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ArticleId    ArticleNumero    ArticleLibelle
        1    BP021471         bigoudi poussin
        2    BC540147         tablier de plongée
        3    PG123459         peigne à girafe 
      ...    ...              ...
        7    XY784558         biglotron
      ...    ...              ...
       23    SC314116         schmilblick
      ...    ...              ...
       54    BC540170         tuba
      ...    ...              ...
       87    BRV47819         bretelles vertes
      ...    ...              ...

Quant à la quantité d’articles commandés, celle-ci n’a de sens que dans l’association d’une commande et d’un article :

COMMANDE_ARTICLE

Code :

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

1
2
3
4
5
6
7
8
9
10
CommandeId    ArticleId    Quantite
         1            2          25
         1            7           4
         1           23         100
         2            3           7
         2           23          12
         2           54         125
         2           87          57
       ...          ...         ...

Ce que l’on modélise ainsi :

MCD (Merise)



Diagramme de classes (UML)




MLD (MySQL Workbench)





Quand on a fini de modéliser, on demande à l’AGL de générer un script SQL de création des tables :


Table COMMANDE

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE COMMANDE 
(
  CommandeId         INT             NOT NULL,
  CommandeNumero     CHAR(12)        NOT NULL,
  CommandeDate       DATE            NOT NULL,
  CONSTRAINT COMMANDE_PK PRIMARY KEY (CommandeId),
  CONSTRAINT COMMANDE_AK UNIQUE (CommandeNumero)
) ;

Table ARTICLE

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE ARTICLE 
(
  ArticleId          INT             NOT NULL,
  ArticleNumero      CHAR(8)         NOT NULL,
  ArticleLibelle     VARCHAR(64)     NOT NULL,
  CONSTRAINT ARTICLE_PK PRIMARY KEY (ArticleId),
  CONSTRAINT ARTICLE_AK UNIQUE (ArticleNumero)
) ;

Table COMMANDE_ARTICLE

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE COMMANDE_ARTICLE 
(
  CommandeId         INT             NOT NULL,
  ArticleId          INT             NOT NULL,
  Quantite           INT             NOT NULL,
  CONSTRAINT COMMANDE_ARTICLE_PK PRIMARY KEY (CommandeId, ArticleId),
  CONSTRAINT COMMANDE_ARTICLE_COMMANDE_FK1 FOREIGN KEY (CommandeId)
    REFERENCES COMMANDE (CommandeId),
  CONSTRAINT COMMANDE_ARTICLE_ARTICLE_FK2 FOREIGN KEY (ArticleId)
    REFERENCES ARTICLE (ArticleId)
) ;

Dans un programme 1 personne passe une commande et y insère des articles. Quel est la commande SQL à faire ?

Il faut déjà que les tables qu’on vient de décrire soient remplies. On utilise pour cela l’instruction INSERT. Pour en visualiser le contenu, c’est l’instruction SELECT.

Par exemple (pour obtenir le résultat présenté plus haut) :

Table COMMANDE

Code SQL :

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

1
2
3
4
INSERT INTO COMMANDE (CommandeId, CommandeNumero, CommandeDate) VALUES (1, '2014-F6-0007', '2014-06-04') ;
INSERT INTO COMMANDE (CommandeId, CommandeNumero, CommandeDate) VALUES (2, '2014-A3-0145', '2014-09-30') ;
 
SELECT * FROM COMMANDE ;

Table ARTICLE

Code SQL :

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

1
2
3
4
5
6
7
8
9
INSERT INTO ARTICLE (ArticleId, ArticleNumero, ArticleLibelle) VALUES (1, 'BP021471', 'bigoudi poussin') ;
INSERT INTO ARTICLE (ArticleId, ArticleNumero, ArticleLibelle) VALUES (2, 'BC540147', 'tablier de plongée') ;
INSERT INTO ARTICLE (ArticleId, ArticleNumero, ArticleLibelle) VALUES (3, 'PG123459', 'peigne à girafe') ;
INSERT INTO ARTICLE (ArticleId, ArticleNumero, ArticleLibelle) VALUES (7, 'XY784558', 'biglotron') ;
INSERT INTO ARTICLE (ArticleId, ArticleNumero, ArticleLibelle) VALUES (23, 'SC314116', 'schmilblick') ;
INSERT INTO ARTICLE (ArticleId, ArticleNumero, ArticleLibelle) VALUES (54, 'BC540170', 'tuba') ;
INSERT INTO ARTICLE (ArticleId, ArticleNumero, ArticleLibelle) VALUES (87, 'BRV47819', 'bretelles vertes') ; 
 
SELECT * FROM ARTICLE ;

Table COMMANDE_ARTICLE

Code SQL :

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

1
2
3
4
5
6
7
8
9
INSERT INTO COMMANDE_ARTICLE (CommandeId, ArticleId, Quantite) VALUES (1, 2, 25) ;
INSERT INTO COMMANDE_ARTICLE (CommandeId, ArticleId, Quantite) VALUES (1, 7, 4) ;
INSERT INTO COMMANDE_ARTICLE (CommandeId, ArticleId, Quantite) VALUES (1, 23, 100) ;
INSERT INTO COMMANDE_ARTICLE (CommandeId, ArticleId, Quantite) VALUES (2, 3, 7) ;
INSERT INTO COMMANDE_ARTICLE (CommandeId, ArticleId, Quantite) VALUES (2, 23, 12) ;
INSERT INTO COMMANDE_ARTICLE (CommandeId, ArticleId, Quantite) VALUES (2, 54,125) ;
INSERT INTO COMMANDE_ARTICLE (CommandeId, ArticleId, Quantite) VALUES (2, 87, 57) ;
 
SELECT * FROM COMMANDE_ARTICLE ;

Pour visualiser l’ensemble des commandes :

Code SQL :

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

1
2
3
4
SELECT CommandeNumero, CommandeDate, ArticleNumero, ArticleLibelle, Quantite
FROM   COMMANDE AS x INNER JOIN COMMANDE_ARTICLE AS y ON x.CommandeId = y.CommandeId
                     INNER JOIN ARTICLE AS z ON y.ArticleId = z.ArticleId
ORDER BY CommandeNumero ;

Au résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CommandeNumero    CommandeDate    ArticleNumero    ArticleLibelle    Quantite
--------------    ------------    -------------    --------------    --------

2014-A3-0145      2014-09-30      PG123459         peigne à girafe          7
2014-A3-0145      2014-09-30      SC314116         schmilblick             12
2014-A3-0145      2014-09-30      BC540170         tuba                   125
2014-A3-0145      2014-09-30      BRV47819         bretelles vertes        57
2014-F6-0007      2014-06-04      BC540147         tablier de plongée      25
2014-F6-0007      2014-06-04      XY784558         biglotron                4
2014-F6-0007      2014-06-04      SC314116         schmilblick            100

Vous noterez qu’on ne présente à l’utilisateur que les données qui l’intéressent, les attributs artificiels (CommandeId et ArticleId) restent sous le capot, et sont utilisés fondamentalement pour les jointures (JOIN est l'opérateur par excellence de l'algèbre relationnelle).

Et équipez-vous d’un AGL pour modéliser et générer les scripts de création des tables, MySQL Workbench (gratuit, pourvu que ça dure) par exemple...