Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Schéma Discussion :

Conception base de données


Sujet :

Schéma

  1. #1
    Membre du Club
    Conception base de données
    Bonjour,

    Etant autodidacte, je n'applique pas forcément les bonnes pratiques pour la conception des bases de données.

    Je me trouve régulièrement confronté à ce genre de cas présenté avec cette image
    - Un fournisseur a des produits et des catégories
    - Un fournisseur à des produits (lié également à une catégorie du MÊME fournisseur)


    Cas 1 : SANS la liaison 'Rouge', je peux remonter au "Fournisseur" d'un produit par le biais de sa "Categorie" elle même liée à un "Fournisseur"
    Cas 2 : AVEC la liaison 'Rouge', j'accède directement au "Fournisseur" d'un produit.

    Dans le cas 1 c'est moins rapide certainement puisque il faut lire plusieurs fichiers pour avoir une information
    Dans le cas 2 c'est plus rapide puisqu'on accède directement au Fournisseur. MAIS un produit peut, par errreur, bug etc.. se trouver brancher sur une catégorie d'un fournisseur différent du fournisseur du produit et la c'est un gros problème.

    En conception de base de données, le cas 2 est t'il totalement à bannir puisque source d'erreur ?

    Merci pour vos réponses,

  2. #2
    Membre confirmé
    Bonjour,

    Si la catégorie se rapporte au produit, alors je lierai la catégorie aux produits et non pas aux fournisseurs. Le cas 2 me semble plus naturel. Comment peut-il y avoir de mauvais rattachements si le fournisseur ne vend pas le produit?

  3. #3
    Rédacteur

    Avez-vous procédé à des essais, en vrai grandeur, pour vérifier que c'était plus ou moins rapide dans l'un et l'autre cas ? Sinon, votre affirmation est un "à priori" et peut se révéler fausse ! En effet, les SGBDR possèdent un optimiseur qui décide à votre place de comment se fait l'accès... L'optimiseur n'étant pas forcé de suivre l'écriture de votre requête !

    Lorsque les chemin d'accès sont redondants, vous aurez forcément des performances plus mauvaises :
    • le maintien de deux chemins d'accès coute plus cher qu'un seul
    • l'accès se faisant tantôt d'un côté, tantôt de l'autre conduira à des incohérences, très couteuses à rectifier.

    Les performances c'est pas juste une requête.... les performances ce sont des milliers de requêtes faites en permanence et de manière concurrentes par des centaines d'utilisateurs ! Et pas seulement en lecture, mais aussi en écriture !

    Les bases de données relationnelles ont été conçues pour être un extraordinaire compromis entre la parfaite cohérence des données et les performances, lorsqu'elles sont techniquement bien maîtrisées....

    Je vous livre en toute première, un extrait de mon nouveau livre à paraitre sur le SQL :
    (Chapitre 2 : Modélisation des données relationnelles - introduction)

    "
    La modélisation relationnelle est la clef de voûte de l’architecture relationnelle. Vouloir échapper à structurer l’information tel que l’a prévu Codd dans sa théorie est rapidement sanctionné par des performances médiocres à exécrables, des difficultés à écrire les requêtes, une absence de contrôle des données saisies, des résultats potentiellement faux et la frustration des utilisateurs finaux.
    "

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  4. #4
    Modérateur

    - Un fournisseur a des produits et des catégories
    - Un fournisseur à des produits (lié également à une catégorie du MÊME fournisseur)
    Votre souci vient du fait que vos règles de gestion des données sont mal formulées.

    Commencez par n'établir des règles de gestion qu'entre deux "choses" : fournisseur et produit d'une part, fournisseur et catégorie, produit et catégorie.
    Vous vous poserez donc la question : "Les catégories des produits et les catégories des fournisseurs sont-elles de même nature ?"

    Qu'entendez-vous par "catégorie" dans ces deux cas ?
    Catégorie d'un fournisseur : fabricant, revendeur, concessionnaire....
    Catégorie de produit : véhicule, matériel électrique, métallerie, matière première...

    On voit que ces catégories sont totalement différentes ; je ne sais pas si c'est pareil dans votre cas. Une fois que vous aurez répondu correctement à ces questions et lu mon billet sur les règles de gestion, vous arriverez au bon MCD facilement.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Expert éminent sénior
    Bonsoir ddom76,


    Citation Envoyé par ddom76 Voir le message
    En conception de base de données, le cas 2 est t'il totalement à bannir puisque source d'erreur ?
    Revenir à un peu de théorie, ça ne peut pas faire de mal...

    Les bases de données ça se modélise. Il y a l’aspect théorique développé au début des années 70 par E. F. Codd, père de la théorie relationnelle, et il est bon de ne pas ignorer totalement cette théorie si on veut modéliser correctement.


    Votre schéma peut être traduit ainsi, où l’on fait apparaître les clés des variables relationnelles FOURNISSEUR, CATEGORIE, et PRODUIT :

    
                   ——————————————————————————————————————————
                  |                                          | 
                  V                                          |
    FOURNISSEUR{FourId, ....}             CATEGORIE{CatId, FourId, ....}
                  ∧                                   ∧
                  |                                   |
                   ——————————————         ————————————
                                 |       |
               PRODUIT{ProdId, FourId, CatId, ...}
    
    


    Le singleton {FourId} est clé candidate (clé pour abréger) de la variable FOURNISSEUR, c’est-à-dire que pour deux fournisseurs, le valeurs prises par {FourId} sont nécessairement différentes.

    De même, le singleton {CatId} est clé de la variable CATEGORIE, et le singleton {ProdId} est clé de la variable PRODUIT.

    La présence de l’attribut FourId dans l’en-tête de la variable CATEGORIE et le lien orienté vers l’attribut FourId de la variable FOURNISSEUR expriment le fait que chaque valeur prise par l’attribut FourId de l’en-tête de la variable CATEGORIE est nécessairement une valeur prise par l’attribut FourId de l’en-tête de la variable FOURNISSEUR : dans CATEGORIE, {FourId} est alors une clé étrangère référençant la clé {FourId} de FOURNISSEUR. La présence du mot « clé » dans « clé étrangère » n’est pas un très bon choix, car qui dit clé dit unicité, or tel n’est pas le cas ici, car il s’agit d’intégrité (référentielle) des données, mais on fait avec.
    De la même façon, dans PRODUIT, {CatId} est clé étrangère référençant la clé {CatId} de CATEGORIE et {FourId} est clé étrangère référençant la clé {FourId} de FOURNISSEUR.

    A partir d’un article de Codd (1971) Further normalization of the data base relational model, on démontre que la modélisation ci-dessus n’est pas bonne. En effet, considérons l’en-tête {ProdId, FourId, CatId, ...} de la variable PRODUIT. Un produit donné P ne peut faire référence qu’à un unique fournisseur F : on dit qu’il existe la dépendance fonctionnelle {CatId} → {FourId}.

    Dans son article, Codd définit ce qu’on appelle la troisième forme normale (3NF), ce dont je traite essentiellement dans son extension, la forme normale de Boyce Codd (BCNF).

    Ici, la BCNF est violée parce que le déterminant {CatId} de la DF {CatId} → {FourId} ne fait pas partie des clés de la variable PRODUIT (en effet, pour une catégorie on peut avoir plus d’un produit). Cette variable doit être décomposée ainsi, par application du théorème de Heath (1971) :

    PRODUIT1{ProdId, CatId, ...}

    PRODUIT2{CatId, FourId}


    Clairement, PRODUIT2 fait double emploi avec CATEGORIE et doit disparaître. Le schéma de vient :

    
                   ——————————————————————————————————————————
                  |                                          | 
                  V                                          |
    FOURNISSEUR{FourId, ....}             CATEGORIE{CatId, FourId, ....}
                                                      ∧  
                                                      |
                                  ———————————————————— 
                                 |  
               PRODUIT{ProdId, CatId, ...}
    
    

    En SQL :

    CREATE TABLE FOURNISSEUR 
    (
            FourId        INT       NOT NULL 
          , FourSiret     CHAR(14)  NOT NULL 
            ...
        , CONSTRAINT FOURNISSEUR_PK PRIMARY KEY (FourId)
    )
    ;
    CREATE TABLE CATEGORIE  
    (
            CatId         INT       NOT NULL  
          , FourId        INT       NOT NULL  
            ...
        , CONSTRAINT CATEGORIE_PK PRIMARY KEY (CatId)
        , CONSTRAINT CATEGORIE_FOURNISSEUR_FK FOREIGN KEY (FourId)
              REFERENCES FOURNISSEUR (FourId) 
    )
    ;
    CREATE TABLE PRODUIT 
    (
            ProdId        INT          NOT NULL  
          , CatId         INT          NOT NULL  
          , ProdNom       VARCHAR(32)  NOT NULL  
            ...
        , CONSTRAINT PRODUIT_PK PRIMARY KEY (ProdId)
        , CONSTRAINT PRODUIT_CATEGORIE_FK FOREIGN KEY (CatId)
              REFERENCES CATEGORIE (CatId) 
    )
    ;
    

    Pour connaître les produits d’un fournisseur f dont le Siret est '12345678901234', il faut désormais passer par CATEGORIE. Si on connaît cette valeur f, alors en SQL :

    SELECT y.ProdNom
    FROM   CATEGORIE AS x 
      JOIN PRODUIT AS y ON x.CatId = y.CatId
    WHERE x.FourId = f
    
    Si on ne connaît pas f, on utilise le Siret du fournisseur :

    SELECT y.ProdNom
    FROM   CATEGORIE AS x 
      JOIN PRODUIT AS y ON x.CatId = y.CatId
      JOIN FOURNISSEUR AS z ON z.FourId = x.FourId
    WHERE z.FourSiret = '12345678901234'
    
    Bon d’accord, il y a une table de plus dans laquelle SQL doit crapahuter. Pour revenir à deux tables, il suffit d’utiliser l’identification relative (voyez à la page 130 de l’ouvrage remarquable de Dominique Nanci (qui nous a quittés en 2012) Ingénierie des systèmes d'information : Merise deuxième génération (4e édition, 2001), co-écrit avec Bernard Espinasse), c’est-à-dire, en SQL, faire participer FourId à la clé de CATEGORIE, d’où le script SQL :

    CREATE TABLE FOURNISSEUR 
    (
            FourId        INT       NOT NULL 
          , FourSiret     CHAR(14)  NOT NULL 
            ...
        , CONSTRAINT FOURNISSEUR_PK PRIMARY KEY (FourId)
        , CONSTRAINT FOURNISSEUR_AK UNIQUE (FourSiret)
    )
    ;
    CREATE TABLE CATEGORIE  
    (
            CatId         INT       NOT NULL  
          , FourId        INT       NOT NULL  
            ...
        , CONSTRAINT CATEGORIE_PK PRIMARY KEY (CatId, FourId)
        , CONSTRAINT CATEGORIE_FOURNISSEUR_FK FOREIGN KEY (FourId)
              REFERENCES FOURNISSEUR (FourId) 
    )
    ;
    CREATE TABLE PRODUIT 
    (
            ProdId        INT          NOT NULL  
          , CatId         INT          NOT NULL  
          , FourId        INT          NOT NULL  
          , ProdNom       VARCHAR(32)  NOT NULL  
            ...
        , CONSTRAINT PRODUIT_PK PRIMARY KEY (ProdId)
        , CONSTRAINT PRODUIT_CATEGORIE_FK FOREIGN KEY (CatId, FourId)
              REFERENCES CATEGORIE (CatId, FourId) 
    )
    ;
    
    Vous aurez noté que du fait du mécanisme des clés étrangères, l’attribut FourId est propagé jusque dans la table PRODUIT, mais sans viol de BCNF cette fois-ci !

    en SQL on peut ainsi faire l’économie de la participation de la table CATEGORIE :

    SELECT x.ProdNom
    FROM   PRODUIT AS x   
      JOIN FOURNISSEUR AS y ON y.FourId = x.FourId
    WHERE  y.FourSiret = '12345678901234'
    
    Si votre SGBD est doté d’un optimiseur du calibre de celui de DB2, il transformera la requête ci-dessous pour la ramener à la précédente :

    SELECT z.ProdNom
    FROM   FOURNISSEUR AS x
      JOIN CATEGORIE As y ON x.FourId = y.FourId
      JOIN PRODUIT AS z ON y.CatId = z.CatId
                       AND y.FourId = z.FourId
    WHERE x.FourSiret = '12345678901234'
    
    Bref, un SGBD n’est pas un gestionnaire de fichiers...
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  6. #6
    Membre éclairé
    Bonsoir,
    Citation Envoyé par fsmrel Voir le message
    Pour revenir à deux tables, il suffit d’utiliser l’identification relative (voyez à la page 130 de l’ouvrage remarquable de Dominique Nanci (qui nous a quittés en 2012) Ingénierie des systèmes d'information : Merise deuxième génération (4e édition, 2001), co-écrit avec Bernard Espinasse)
    Pour rebondir sur l'usage de cet excellent ouvrage et sur la méthode qu'il décrit, je pense que, avant de s'embarquer dans des considérations techniques, il est essentiel de réaliser le modèle conceptuel de données, un peu comme vous l'avez fait dans votre premier message, mais avec un outil mieux adapté.
    Parmi les logiciels que je connais : Win'Design, PowerAMC, JMerise, Looping (et il en existe bien d'autres) => cf. discussion Quel logiciel télécharger pour réaliser un MCD

    Une fois la phase conception parfaitement effectuée, passer au niveau logique puis physique pose rarement de problème, si ce n'est des histoires d'optimisations propres au SBGD.

    Bon courage !


    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  7. #7
    Membre du Club
    Merci de vos retours
    Bonjour,

    Grand merci à tous pour toutes vos réponses très complètes !

    je crois que je vais profiter du confinement pour approfondir mes connaissances sur le sujet.. J'ai ai apparemment besoin :-)

    Merci encore.

    Cordialement.

  8. #8
    Expert éminent sénior
    Bonjour ddom76,


    C’est la bonne approche. Mettez-vous à Looping, prenez le temps d’étudier le chapitre 7 (« Modélisation conceptuelle des données ») de l’ouvrage de D. Nanci, puis le chapitre 13 (« Modélisation logique des données »). Reprenez les exercices donnés par Paprick sur son site. Et quand vous vous sentirez bien entraîné, voyez l’approche relationnelle (cf. mon article sur la normalisation des données).


    Le loup adore les bases de données, puisque vous en êtes au stade de Noufnouf et Nifif, attention !




    Il faut procéder comme Nafnaf :




    Bon courage !
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  9. #9
    Invité
    Invité(e)
    Addendum
    Je passais par erreur dans le coin…

    Comme nous nous connaissons, ma démarche ne surprendra personne.


    J'entendais par là que nous lisions mutuellement nos messages.
    Il ne peut pas et ne doit jamais y avoir de relation 0,n. S’il y a une relation 1,1 d’un côté, il y a forcément une relation 1,n de l’autre.


    J'ai rectifié le tir. Je comprenais que l'on pouvait créer des catégories fournisseur non rattachées à un fournisseur.
    Je suis désolé, je n’étais pas en mode « conceptuel » mais déjà en mode « développement »… dans la soute.

    Pour le reste de mon message, j'ai tout supprimé. J'ai vraiment atterri ici par erreur.

  10. #10
    Expert éminent sénior
    Citation Envoyé par IFA2377 Voir le message
    Comme nous nous connaissons, ma démarche ne surprendra personne.
    heu , je ne crois pas que nous ayons été présentés


    Citation Envoyé par IFA2377 Voir le message
    Il ne peut pas et ne doit jamais y avoir de relation 0,n. S’il y a une relation 1,1 d’un côté, il y a forcément une relation 1,n de l’autre.
    C'est faux, voici un exemple archi typique de MCD tout à fait valide, notez le 0,n coté PRODUIT qui est fort heureusement correct !

    COMMANDE 1,n --- posséder --- (1,1R) LIGNE_COMMANDE 1,1 --- concerner --- 0,n PRODUIT

    Remplacer la cardinalité 0,n par 1,n m'interdirait d'enregistrer un produit sous prétexte qu'il n'a encore fait l'objet d'aucune commande

  11. #11
    Expert éminent sénior
    Capitaine,

    Ouf ! Tu sauves la théorie des ensembles ! A en croire IFA, seules les surjections y sont légales


    IFA,

    Dans votre script de déclaration des tables, où sont les déclarations des contraintes de clés primaires et de clés étrangères ? Pourquoi violer la troisième forme normale ? Ici vous êtes dans un forum de modélisation, c’est-à-dire qu’on se situe d’abord sur le pont, les index c’est plus tard, dans la soute (on n’est plus au début des années 80, quand l’unicité des clés ne pouvait être assurée que par les index).
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  12. #12
    Expert éminent sénior
    Citation Envoyé par IFA2377 Voir le message
    Il ne peut pas et ne doit jamais y avoir de relation 0,n. S’il y a une relation 1,1 d’un côté, il y a forcément une relation 1,n de l’autre.
    Pour le reste de mon message, j'ai tout supprimé. J'ai vraiment atterri ici par erreur.
    Atterrir par erreur est une chose, comprendre ses erreurs et par là même progresser en est une autre, cette dernière réponse ne semble pas indiquer que vous soyez sur la bonne voie

  13. #13
    Membre éclairé
    Il ne peut pas et ne doit jamais y avoir de relation 0,n. S’il y a une relation 1,1 d’un côté, il y a forcément une relation 1,n de l’autre.
    Bonsoir,
    Et bien, heureusement que François et Capitaine ont vite réagi ! Cela m'évite de le faire plus longuement…
    Le fait que chaque entité d'une classe A soit concernée par une et une seule entité d'une classe associée B (1,1), ne signifie bien évidemment pas que chaque entité de B soit forcément concernée par une entité de A.
    Il n'y a donc aucun problème à avoir des modèles avec des associations 1,1 / 0,n : c'est même assez fréquent, et c'est tant mieux !
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation