Discussion :

[seb_perl]

Bonjour,

J'expose mon sujet : concerne le milieu de la vente de voitures.

Une offre de voiture concerne une voiture ayant :
- une marque (ex: Renault)
- un modèle (ex : Clio)
- une motorisation (ex: 1.5 Dci 85)
- une finition (ex: Dynamique)

Une motorisation concerne un modèle uniquement.
Une finition concerne un modèle uniquement.

Quand je veux sélectionner la liste des marques ayant au moins une offre:
- je lie vn_offre à veh_motorisation
- puis veh_motorisation à veh_modele
- puis veh_modele à veh_marque

Mais je pourrais tout aussi bien "passer" par veh_version ...
Il y a donc 2 trajets "parallèles".

Ce modèle est-il bien pertinent ?



Merci du coup de pouce
Seb

[fsmrel]

Bonjour,

Petit problème : vous pouvez associer une offre Citroën à une motorisation Renault et à une finition Peugeot...

Un moyen d'éviter cela serait d’utiliser l’identification relative pour propager la marque et le modèle de voiture jusqu’à l’offre.

[seb_perl]

Bonjour,

Merci pour cette réponse !
Pourquoi ne pas propager juste ModeleId ?

Par contre, faut propager ça à la main ou le définir en tant que relation ?

Merci !
Seb

[fsmrel]

Bonsoir Seb,

Pourquoi ne pas propager juste ModeleId ?

Considérons l’exemple tabulaire qui suit :


Marque

Code :

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

1
2
3
4
5
MarqueId  MarqueNom
--------   
    1     Renault
    2     Peugeot
    3     Citroen

Modele

Code :

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

1
2
3
4
5
6
7
8
MarqueId  ModeleId  ModeleNom
--------  --------   
    1        1      Clio Nouvelle
    1        2      R5 d'époque
    2        1      302 super
    2        2      403 prestige
    3        1      DS19 de luxe
    3        2      2CV de compète

Motorisation

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MarqueId  ModeleId  MotorisationId  Motorisation  CO2
--------  --------  --------------   
    1        1           1              motor a    1
    1        1           2              motor b    5
    1        2           1              motor c    3
    1        2           2              motor d    1
    2        1           1              motor e    2
    2        1           2              motor f    6
    2        2           1              motor g    4
    2        2           2              motor h    1
    3        1           1              motor i    3
    3        1           2              motor j    5
    3        2           1              motor k    1
    3        2           2              motor l    1

Finition

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MarqueId  ModeleId  FinitionId  Finition
--------  --------  ----------   
    1        1          1       fini a
    1        1          2       fini b
    1        2          1       fini c
    1        2          2       fini d
    2        1          1       fini e
    2        1          2       fini f
    2        2          1       fini g
    2        2          2       fini h
    3        1          1       fini i
    3        1          2       fini j
    3        2          1       fini k
    3        2          2       fini l

Offre

Code :

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

1
2
3
4
5
6
7
MarqueId  ModeleId  OffreId  MotorisationId  FinitionId  Prix  NbPortes
--------  --------  -------  --------------  
    1        1          1          1             1       10000    3
    1        1          2          1             1       11000    5
    1        1          3          2             1       15000    3
    2        1          1          1             2        6000    5 
   ...      ...        ...        ...           ...       ...    ...

Concernant la table Modele, vous noterez que pour la marque Renault (MarqueId = 1), l’attribut ModeleId prend les valeurs successives 1, 2, etc., même chose pour les autres marques. Ceci vaut pour les tables Motorisation, Finition et Offre.

Situons-nous maintenant sur un plan conceptuel.

Quand je parle d’identification relative, par exemple identifier Modele relativement à Marque, je considère qu’au niveau conceptuel, Modele est une entité-type « faible » par rapport à Marque qui est une entité-type « forte » : la suppression d’une marque entraîne la suppression de ses modèles, car ceux-ci n’ont de sens ontologique qu’au travers d’une marque. Quand vous pensez « Clio », vous pensez « Renault ». Inversement, si vous supprimez un modèle, vous ne supprimez pas la marque. On peut encore dire que l’entité-type Modele est en réalité une propriété multivaluée de l’entité-type Marque (que je renomme ci-dessous MarqueGr) : du point de vue de la théorie relationnelle, la variable relationnelle Modele peut faire l’objet d’un attribut de type RELATION (table en SQL) :

MarqueGr {MarqueId INTEGER, MarqueNom CHAR, Modele RELATION {ModeleId INTEGER, ModeleNom CHAR}}

Ceci dit, il est d’usage se sortir les attributs de type RELATION et d’en faire des variables relationnelles comme les autres, à l’aide d’un opérateur relationnel appelé UNGROUP (dont le symétrique s’appelle GROUP). On peut ainsi produire des variables relationnelles dont la structure est plus familière et correspondent à la représentation tabulaire obtenue avec Workbench :

Marque {MarqueId INTEGER, MarqueNom CHAR}

Modele {MarqueId INTEGER, ModeleId INTEGER, ModeleNom CHAR}

Traduction au niveau SQL :

Code SQL :

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 Marque
(
  MarqueId    INT           NOT NULL ,
  MarqueNom   VARCHAR(45)   NOT NULL ,
  PRIMARY KEY (MarqueId) 
) ;
CREATE  TABLE Modele 
(
  MarqueId    INT           NOT NULL ,
  ModeleId    INT           NOT NULL ,
  ModeleNom   VARCHAR(45)   NOT NULL ,
  PRIMARY KEY (MarqueId, ModeleId) ,
  CONSTRAINT Modele_Marque_fk
    FOREIGN KEY (MarqueId) REFERENCES Marque (MarqueId)
) ;

Vous noterez que l’attribut MarqueId entre dans la composition de la clé primaire de Modele, mais aussi dans la composition de la clé étrangère permettant de référencer la table Marque.

Vous pouvez tout à fait propager ModeleId seul, c'est-à-dire ne pas utiliser l’identification relative à Marque :




Ce qui donne en SQL :

Code SQL :

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 Marque
(
  MarqueId    INT           NOT NULL ,
  MarqueNom   VARCHAR(45)   NOT NULL ,
  PRIMARY KEY (MarqueId) 
) ;
CREATE  TABLE Modele 
(
  MarqueId    INT           NOT NULL ,
  ModeleId    INT           NOT NULL ,
  ModeleNom   VARCHAR(45)   NOT NULL ,
  PRIMARY KEY (ModeleId) ,
  CONSTRAINT Modele_Marque_fk
    FOREIGN KEY (MarqueId) REFERENCES Marque (MarqueId)
) ;

Seule la composition de la clé primaire de la table Modele a changé.

Personnellement, je préfère conserver l’identification relative entre Marque et Modele, car :

1) Au niveau conceptuel, d’un point de vue sémantique, ontologique, on perçoit parfaitement les choses ;

2) Au niveau logique (disons Workbench) puis SQL, on peut ainsi améliorer certaines requêtes, du genre :

Quel est le prix moyen d’une offre par marque ?

Requête que l’on code ainsi (une seule jointure) :

Code SQL :

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

1
2
3
4
SELECT a.MarqueNom, AVG(b.prix) AS PrixMoyen
FROM   Marque AS a
         JOIN Offre AS b ON a.MarqueId = b.MarqueId 
GROUP BY b.MarqueId, a.MarqueNom ;

Alors qu’en n’identifiant que relativement au modèle, on doit coder (une jointure supplémentaire) :

Code SQL :

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

1
2
3
4
5
SELECT a.MarqueNom, AVG(c.prix) AS PrixMoyen
FROM   Marque AS a
         JOIN Modele AS b ON a.MarqueId = b.MarqueId 
         JOIN Offre AS  c ON b.ModeleId = c.ModeleId 
GROUP BY b.MarqueId, a.MarqueNom ;

Vous me direz qu’une jointure de plus, ça n’est pas dramatique : cependant, quand vous avez des tables comportant cent millions de lignes, l’économie n’est pas mince. Et puis, le nombre de tables intermédiaires entre une table « racine » telle que Marque et une table « feuille » telle que Offre peut être plus conséquent. Je vous renvoie à ce sujet au message suivant, qui traite de commandes et de livraisons par camion :

http://www.developpez.net/forums/d62...s/#post4843686

3) Au niveau physique, on joue essentiellement sur les performances. A ce sujet, je vous renvoie au message dont je viens de faire mention.

faut propager ça à la main ou le définir en tant que relation ?

Supposons que nous conservions MarqueId comme identifiant générique, à propager jusqu’à Offre.

La clé primaire de Modèle est donc définie par le couple {MarqueId, ModeleId}, ce qui signifie que, pour établir une relation entre Marque et Modele, il suffit d’utiliser l’icône proposée par Workbench :

Place a new 1:n Identifying Relationship

Le principe est le même concernant Motorisation relativement à Modele, pour obtenir une clé primaire qui soit le triplet {MarqueId, ModeleId, MotorisationId} ; idem pour Finition.

Offre est identifiée relativement à Modèle, mais pas à Motorisation et Finition. J’aurais pu identifier relativement soit à Motorisation, soit à Finition, mais pas au deux en même temps, car la clé primaire aurait été une surclé réductible (présence d’attributs dangereusement superflus) : ne faisons donc pas de jaloux Quoi qu’il en soit, j’ai utilisé l’icône

Place a new 1:n Non-Identifying Relationship

entre Motorisation et Offre d’une part, puis Finition et Offre d’autre part.

Au résultat, Offre a l’allure suivante qui n’est pas satisfaisante :



Il y a donc quelques actions à réaliser pour retrouver le MLD que je vous avais proposé dans mon précédent message :

1) Intégrer les attributs MarqueId et ModeleId à la clé primaire




=>




2) La relation entre Offre et Motorisation convient :




Par contre, la relation entre Offre et Motorisation n’est pas satisfaisante, car les attributs MarqueId1 et ModeleId1 sont inutiles :



On modifie donc en conséquence la composition de la clé étrangère Offre_Finition_Fk :



3) Les attributs MarqueId1 et ModeleId1 ne jouent plus de rôle particulier :



On peut donc les supprimer et l’on retrouve le MLD attendu :

[seb_perl]

Bonjour fsmrel,

Alors là ...
Je viens de prendre une LECON mais dans le bon sens du terme !

Je vais étudier ça au calme.

Je te remercie d'avoir pris du temps pour m'exposer si en détails les choses.
C'est une entraide rare de nos jours !!!

A bientôt
Seb

[fsmrel]

Bonsoir Seb,


J’ai détaillé les choses parce que l’on est en présence d’un cas de figure qui n’est pas toujours modélisable de façon formelle au niveau conceptuel (c’est pourquoi j’ai agi un cran en-dessous, au niveau « logique »). Au niveau conceptuel, représenter les entités-types Marque, Modele, Motorisation, Finition et leurs relations ne pose aucune difficulté, par contre on bute quand il s’agit de représenter l’entité-type Offre, du fait de la contrainte :

La motorisation et la finition faisant partie de l’offre concernent un même modèle (d’une même marque).

Dans le cas de Merise/2, la contrainte peut-être représentée, sinon il faut se résoudre à intervenir manuellement au niveau logique, comme je l’ai fait.

En outre, je suppose que, le fait d’avoir détaillé les opérations effectuées à ce niveau, peut intéresser certains concepteurs confrontés à ce genre de contrainte. A cette occasion, je les invite à tenter une rétroconception pertinente...

[fsmrel]

Bonjour Sébastien,

Reprenons le modèle que vous proposez :




Vous me dites que MySQL signale une erreur lors de la création de la table veh_finition. Je pense deviner ce dont il s’agit : dans le script de création des tables, la composition de la clé étrangère ne correspond pas à la composition de la clé primaire de la table référencée (table modele). Dans ce qui suit, je réagis par rapport à la version 5.1.18 de Workbench.

Avant de traiter de cela, je tiens à rappeler que je vous avais suggéré d’utiliser l’identification relative plutôt que l’identification absolue : vous avez opté pour la première solution et c’est bien.

Toutefois (peut-être sous l’influence de Workbench), vous avez suivi l’exemple du roi Dagobert en ordonnant à l’envers la liste des attributs composant les clés primaires. Considérez par exemple la clé primaire de veh_modele :





L’ordre que vous avez retenu est celui-ci : modele_id suivi de marque_id. Permettez-moi de jouer les saint Eloi en remettant les choses à l’endroit : marque_id suivi de modele_id :





En effet, c’est quand même le modèle qui est relatif à la marque et pas l’inverse, revoyez mes précédents messages à ce sujet.

A noter que Workbench ne compose pas les clés primaires dans l’ordre qui nous convient des attributs car, contrairement à ce que veut la logique, il installe toujours le moins significatif en tête... Pour obtenir l’ordre qui convient, il faut agir via l’onglet Indexes.

Ordre erroné des attributs dans l’index primaire :





Remise en ordre en agissant sur # :







Revenons maintenant à ce que j’ai dit au début au sujet de l’erreur signalée par MySQL : la composition de la clé étrangère ne correspond pas à la composition de la clé primaire de la table référencée. En effet, à l’ordre des attributs près, la clé primaire de veh_modele est composée des attributs marque_id et modele_id, or si on visualise l’onglet Foreign keys de veh_finition, seul l’attribut modele_id est coché, c'est-à-dire que la clé étrangère référençant veh_modele est incomplète (marque_id manque à l’appel) :





Cochons donc marque_id comme il convient :






Ce qui vaut pour veh_finition vaut évidemment pour veh_motorisation et vn_offre : les attributs composant une clé étrangère doivent correspondre à ceux qui composent la clé primaire référencée, attribut par attribut, dans le même ordre.

Une fois de plus, Workbench ne respecte pas l’ordre logique des attributs des clés étrangères. Mais cette fois-ci, même en intervenant sur l’index associé à la clé étrangère, l’ordre des clés n’est pas changé pour cette dernière. En effet, si l’ordre des attributs de la clé de l’index utilisé pour la clé étrangère est conforme (quand il ne prend pas fantaisie à Workbench de remettre les attributs dans le désordre, pour des raisons que lui seul connaît) :

CREATE INDEX `modele_id` ON `Ma_base`.`veh_finition` (`marque_id` ASC, `modele_id` ASC) ;

il n’en demeure pas moins que pour la clé étrangère, ça craint, car la clé primaire référencée étant ordonnée ainsi

(`marque_id`, `modele_id`)

il se trouve que la clé étrangère et la référence sont malencontreusement ordonnées différemment :

FOREIGN KEY (`modele_id` , `marque_id` ) REFERENCES `Ma_base`.`veh_modele` (`modele_id` , `marque_id` )

C’est grave, car comme les attributs marque_id et modele_id sont tous les deux du même type (Integer), on risquerait la catastrophe si le SGBD ne vérifiait pas l’appariement des attributs entre les attributs cités dans la clause REFERENCES et ceux de la clé primaire référencée.

=> A moins que les versions postérieures à version 5.1.18 n’aient été corrigées, il faudra que je j’attire l’attention de ceux qui développent l’outil...


Je rappelle en passant le modèle que j'avais proposé :




N.B. Selon votre modèle, les attributs suivants peuvent être marqués NULL au niveau des tables, mais je vous engage à n’en rien faire :

modele (table veh_modele)
co2 (table veh_motorisation)
finition (table veh_finition)
prix (table vn_offre)
nb_portes (table vn_offre)

[CinePhil]

Si j'en crois SQLPro, dans son article consacré aux index, lors d'un index multicolonnes :

on obtiendra une efficacité plus grande en posant en premier les colonnes ayant la plus forte dispersion.

Il ne semble peut-être pas logique, du point de vue humain, que MySQL Workbench ordonne les colonnes des clés en mettant le modèle en premier mais peut-être les concepteurs ont-ils anticipé ce souci de performance des index car une marque ayant potentiellement plusieurs modèles, il y aura un plus grand nombre de modèles différents que de marques différentes.

[fsmrel]

peut-être les concepteurs ont-ils anticipé ce souci de performance des index

Ça serait une bien grande imprudence de leur part, outre un diktat insupportable. De toute façon, les problèmes de performance sont abordés au niveau du modèle physique : certaines tables méritent une dispersion (par exemple la table des clients), d'autres au contraire un regroupement (propriétés multivaluées des autres, telles que la table des commandes, celle des lignes de commande, etc.)

Au niveau où l’on se situe, c’est au concepteur de décider de l’ordre des colonnes dans les clés. Du reste, Workbench permet cela, mais il y a manifestement un bug dans la génération du code SQL de création des tables (composition des clés étrangères), c’est tout.

[seb_perl]

Bonjour,

J'ai tout bien réorganisé mon model de sorte que sous Mysql Workbench il semble correct.
Malheureusement l'export Sql ne donne rien de valable ...

Il y a le problème de l'ordre des index mais en plus pour pourvoir avoir un champ auto increment par exemple pour la table motorisation, motorisation_id, il faut que ce champ soit en premier sinon Mysql génère une erreur.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP TABLE IF EXISTS `base`.`veh_finition` ;

CREATE  TABLE IF NOT EXISTS `base`.`veh_finition` (
  `finition_id` INT NOT NULL AUTO_INCREMENT ,
  `marque_id` INT NOT NULL ,
  `modele_id` INT NOT NULL ,
  `finition` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`finition_id`, `marque_id`, `modele_id` ) ,
  CONSTRAINT `fin_marque_modele_fk`
    FOREIGN KEY (`marque_id` , `modele_id` )
    REFERENCES `base`.`veh_modele` (`marque_id` , `modele_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Ce code génère bien une table dans MySql mais les relations ne sont pas crées !!!!

Pour ça faut éxécuter :

Code :

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

1
2
3
ALTER TABLE `veh_finition` ADD FOREIGN KEY ( `marque_id` ) REFERENCES `ap_test`.`veh_modele` (
`marque_id`
);

et de même pour modele_id

Workbench a du mal avec ce model ...

A bientôt
Seb

[seb_perl]

Bonjour,

J'ai bien réussi à avoir un modèle pertinent sous MySql mais comme j'utilise un Orm (Doctrine pour symfony), celui-ci n'arrive pas à gérer la complexité des relations ...
Va falloir que je trouve une autre solution ...

A bientôt
Seb

[fsmrel]

Bonsoir Sébastien,

J'ai tout bien réorganisé mon model de sorte que sous Mysql Workbench il semble correct.
Malheureusement l'export Sql ne donne rien de valable ...
Il y a le problème de l'ordre des index mais en plus pour pourvoir avoir un champ auto increment par exemple pour la table motorisation, motorisation_id, il faut que ce champ soit en premier sinon Mysql génère une erreur.

L’auto-incrément peut valoir pour la table Marque, mais après, basta ! En effet, l’auto-incrément est à utiliser pour l’identification absolue, or les tables Modele, Motorisation, Finition et Offre sont la traduction d’entités-types identifiées de manière relative, revoyez l’exemple tabulaire que j’avais fourni pour illustrer tout cela. Ainsi, au niveau SQL on utilise l’opérateur MAX pour affecter les valeurs des attributs relatifs.

Par exemple, pour créer un modèle :

Code SQL :

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

1
2
3
4
5
6
7
8
INSERT INTO veh_modele
    SELECT x.marque_id
        , (SELECT COALESCE (MAX(modele_id) + 1, 1)
           FROM   Veh_Marque as x JOIN veh_Modele AS y ON x.marque_Id = y.marque_Id  
           WHERE  x.marque = 'Renault') AS s
        , 'Clio nouvelle' 
    FROM   veh_Marque as x LEFT JOIN veh_Modele AS y ON x.marque_Id = y.marque_Id
    WHERE  x.Marque = 'Renault' ;

Pour créer une motorisation :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO veh_motorisation
    SELECT y.marque_id, y.modele_id
        , (SELECT COALESCE(MAX(motorisation_id) + 1, 1) 
           FROM   Veh_Marque AS x JOIN veh_Modele AS y       ON x.marque_Id = y.marque_Id  
                                  JOIN veh_Motorisation AS z ON x.marque_Id = y.marque_Id  
                                                            AND y.modele_id = z.modele_id
          WHERE  marque = 'Renault' AND modele = 'Clio nouvelle') AS s
        , 'motor a', 1
    FROM   veh_Marque as x JOIN veh_Modele AS y            ON  x.marque_Id = y.marque_Id
                           LEFT JOIN veh_motorisation AS z ON  y.marque_Id = z.marque_Id
                                                           AND y.modele_id = z.modele_id
   WHERE  x.Marque = 'Renault' AND y.modele =  'Clio nouvelle' ;

Etc.

Ce code génère bien une table dans MySql mais les relations ne sont pas crées !!!!

Pour ma part, je ne coche pas Skip Creation of FOREIGN KEYS lors de la génération du script SQL :



Code généré (j’ai changé les noms des tables et des colonnes) :

Code SQL :

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
 
-- -----------------------------------------------------
-- Table `A`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `A` (
  `A1` INT NOT NULL ,
  `A2` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`A1`) )
ENGINE = InnoDB;
 
-- -----------------------------------------------------
-- Table `B`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `B` (
  `A1` INT NOT NULL ,
  `B1` INT NOT NULL ,
  `B2` VARCHAR(45) NULL ,
  PRIMARY KEY (`A1`, `B1`) ,
  CONSTRAINT `fk_B_A`
    FOREIGN KEY (`A1` )
    REFERENCES `A` (`A1` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
 
CREATE INDEX `fk_B_A` ON `B` (`A1` ASC) ;
 
-- -----------------------------------------------------
-- Table `C`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `C` (
  `A1` INT NOT NULL ,
  `C1` INT NOT NULL ,
  `C2` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`A1`, `C1`) ,
  CONSTRAINT `C_A_FK1`
    FOREIGN KEY (`A1` )
    REFERENCES `A` (`A1` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;
 
CREATE INDEX `C_A_FK1` ON `C` (`A1` ASC) ;
 
-- -----------------------------------------------------
-- Table `D`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `D` (
  `A1` INT NOT NULL ,
  `D1` INT NOT NULL ,
  `B1` INT NOT NULL ,
  `C1` INT NOT NULL ,
  `D1` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`A1`, `D1`) ,
  CONSTRAINT `D_FK_B`
    FOREIGN KEY (`A1` , `B1` )
    REFERENCES `B` (`A1` , `B1` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `D_FK_C`
    FOREIGN KEY (`A1` , `C1` )
    REFERENCES `C` (`A1` , `C1` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;
 
CREATE INDEX `D_FK_B` ON `D` (`A1` ASC, `B1` ASC) ;
 
CREATE INDEX `D_FK_C` ON `D` (`A1` ASC, `C1` ASC) ;

il n’en demeure pas moins que pour la clé étrangère, ça craint, car la clé primaire référencée étant ordonnée ainsi

(`marque_id`, `modele_id`)

il se trouve que la clé étrangère et la référence sont malencontreusement ordonnées différemment :

FOREIGN KEY (`modele_id` , `marque_id` ) REFERENCES `Ma_base`.`veh_modele` (`modele_id` , `marque_id` )

J’ai trouvé un moyen de générer le code correct (cf. ci-dessus), je vous expliquerai cela dans mon prochain message. Mais bien entendu, j’utilise l’identification relative.

[fsmrel]

Dans mon message précédent, j'ai dit que j'avais trouvé un moyen de produire du code sans erreur.

Dans ce qui suit, j'ai remplacé Marque, Modèle, Motorisation, Finition et Offre par respectivement A, B, C et D.

1) Je crée A, B, C, D, sans établir de lien entre D et ses parents. Clé primaire : {A1, D1} :




2) Je définis les clés étrangères sans m'occuper des index. A noter que le lien entre B et D a été représenté de façon automatique par Workbench suite à mon action :



Même chose concernant le lien entre C et D :




3) Les index ont aussi été automatiquement définis par Workbench et sont OK.







Au final, je produis un script SQL dans les conditions que j'ai précisées dans mon précédent message.

[fsmrel]

Bonjour,

J'ai bien réussi à avoir un modèle pertinent sous MySql mais comme j'utilise un Orm (Doctrine pour symfony), celui-ci n'arrive pas à gérer la complexité des relations ...

Quatre tables et quatre relations, qu'y a-t-il donc de complexe ?

[jeanll]

Hello,
J'ai utilisé votre problématique comme tuto personnel.
Mais avec la vesrion Workbench 5.2.27 j'ai :

Que faire ??

[fsmrel]

Je n’en suis qu’à la version 5.1.18, mais on va supposer que ça ne joue pas.


La table D hérite de l’attribut A1 de la table A par deux chemins différents : l’un des deux attributs B_A_A1 ou C_A_A1 est inutile, disons C_A_A1 (en effet, pour une offre donnée, on ne veut faire référence qu’à une marque). La clé étrangère référençant la table C ne doit donc pas être la paire {C_A_A1, C_C1} mais la paire {B_A_A1, C_C1}.

Vous cliquez donc sur l’onglet Foreign keys pour la table D puis sur D_C_fk1 (ou le nom équivalent produit par Workbench), apparaissent alors les noms des colonnes participant à la clé étrangère :



Vous décochez C_A_A1 et cochez B_A_A1, d’où la nouvelle bobine de D :



La clé primaire de D est composée de la paire {B_A_A1, D1}, donc vous cochez la case PK pour la colonne B_A_A1 :



Et vous remettez les attributs dans le bon ordre :



Si à la génération du script SQL les colonnes des clés primaires ne sont pas dans le bon ordre, dans l’onglet Columns décochez les cases PK et cochez à nouveau, mais dans l’ordre : A1 doit toujours être la 1re colonne de chaque clé (tables B, C, D). Même principe pour les clés étrangères. C’est rustique, mais avec la version dont je dispose, je ne vois pas comment faire autrement...

A noter que l’attribut C_A_A1 est devenu inutile et disparaît :

[jeanll]

bon, alors revenons à l'exemple du départ (avec la table "marques" appellée Z).
Workbench me génère ceci :


si j'ai compris :
fk_D_C1 je décoche C_A_A1 et C_A_Z_Z1 et je coche B_A_A1 et B_A_Z_Z1
ces 2 derniers ont maintenant une PK.
j'ordonne les colonnes

c'est ça ??


ce qui pond :

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
-- -----------------------------------------------------
-- Table `mydb`.`Z`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Z` (
  `Z1` INT NOT NULL ,
  `Z2` VARCHAR(45) NULL ,
  PRIMARY KEY (`Z1`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`A`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`A` (
  `Z_Z1` INT NOT NULL ,
  `A1` INT NOT NULL ,
  `A2` VARCHAR(45) NULL ,
  PRIMARY KEY (`Z_Z1`, `A1`) ,
  INDEX `fk_A_Z1` (`Z_Z1` ASC) ,
  CONSTRAINT `fk_A_Z1`
    FOREIGN KEY (`Z_Z1` )
    REFERENCES `mydb`.`Z` (`Z1` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`B`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`B` (
  `A_Z_Z1` INT NOT NULL ,
  `A_A1` INT NOT NULL ,
  `B1` INT NOT NULL ,
  PRIMARY KEY (`A_Z_Z1`, `A_A1`, `B1`) ,
  INDEX `fk_B_A1` (`A_Z_Z1` ASC, `A_A1` ASC) ,
  CONSTRAINT `fk_B_A1`
    FOREIGN KEY (`A_Z_Z1` , `A_A1` )
    REFERENCES `mydb`.`A` (`Z_Z1` , `A1` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`C`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`C` (
  `A_Z_Z1` INT NOT NULL ,
  `A_A1` INT NOT NULL ,
  `C1` INT NOT NULL ,
  PRIMARY KEY (`A_Z_Z1`, `A_A1`, `C1`) ,
  INDEX `fk_C_A1` (`A_Z_Z1` ASC, `A_A1` ASC) ,
  CONSTRAINT `fk_C_A1`
    FOREIGN KEY (`A_Z_Z1` , `A_A1` )
    REFERENCES `mydb`.`A` (`Z_Z1` , `A1` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`D`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`D` (
  `B_A_Z_Z1` INT NOT NULL ,
  `B_A_A1` INT NOT NULL ,
  `D1` INT NOT NULL ,
  `B_B1` INT NOT NULL ,
  `C_C1` INT NOT NULL ,
  PRIMARY KEY (`B_A_Z_Z1`, `B_A_A1`, `D1`) ,
  INDEX `fk_D_B1` (`B_A_Z_Z1` ASC, `B_A_A1` ASC, `B_B1` ASC) ,
  INDEX `fk_D_C1` (`B_A_A1` ASC, `C_C1` ASC, `B_A_Z_Z1` ASC) ,
  CONSTRAINT `fk_D_B1`
    FOREIGN KEY (`B_A_Z_Z1` , `B_A_A1` , `B_B1` )
    REFERENCES `mydb`.`B` (`A_Z_Z1` , `A_A1` , `B1` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_D_C1`
    FOREIGN KEY (`B_A_A1` , `C_C1` , `B_A_Z_Z1` )
    REFERENCES `mydb`.`C` (`A_Z_Z1` , `A_Z_Z1` , `A_Z_Z1` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[fsmrel]

Ça a l’air pas mal, à ceci près que la clé étrangère fk_D_C1 est à revoir complètement (comparez avec fk_D_B1 qui est correcte).

Actuellement :

Code SQL :

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

1
2
3
  CONSTRAINT fk_D_C1
    FOREIGN KEY (B_A_A1, C_C1, B_A_Z_Z1)
    REFERENCES mydb.C (A_Z_Z1, A_Z_Z1 , A_Z_Z1)

Après remise en ordre :

Code SQL :

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

1
2
3
  CONSTRAINT fk_D_C1
    FOREIGN KEY (B_A_Z_Z1, B_A_A1, C_C1)
    REFERENCES mydb.C (A_Z_Z1, A_A1, C1)

En effet, remettre les colonnes dans l’ordre dans le dessin n’entraîne pas la remise en ordre des clés primaires et étrangères sous le capot.

Index fk_D_C1 : à remettre en ordre lui aussi (inspirez-vous de l’index fk_D_B1).

[fsmrel]

Étant donné que Z est à la racine de tout, vous pouvez aussi identifier relativement à Z seulement :

Code SQL :

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
 
-- ----------------------------------- 
-- Table mydb.Z 
-- ----------------------------------- 
CREATE  TABLE IF NOT EXISTS mydb.Z ( 
  Z1 INT NOT NULL , 
  Z2 VARCHAR(45) NOT NULL, 
  PRIMARY KEY (Z1)); 
-- ----------------------------------- 
-- Table mydb.A 
-- ----------------------------------- 
CREATE  TABLE IF NOT EXISTS mydb.A ( 
  Z1 INT NOT NULL, 
  A1 INT NOT NULL, 
  A2 VARCHAR(45) NOT NULL, 
  PRIMARY KEY (Z1, A1), 
  INDEX A_Z_fk1 (Z1 ASC), 
  CONSTRAINT A_Z_fk1 
    FOREIGN KEY (Z1) 
    REFERENCES mydb.Z (Z1) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION); 
-- ----------------------------------- 
-- Table mydb.B 
-- ----------------------------------- 
CREATE  TABLE IF NOT EXISTS mydb.B ( 
  Z1 INT NOT NULL, 
  A1 INT NOT NULL, 
  B1 INT NOT NULL, 
  B2 VARCHAR(45) NOT NULL, 
  PRIMARY KEY (Z1, B1), 
  INDEX B_A_fk1 (Z1 ASC, A1 ASC), 
  CONSTRAINT B_A_fk1 
    FOREIGN KEY (Z1, A1) 
    REFERENCES mydb.A (Z1, A1) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION); 
-- ----------------------------------- 
-- Table mydb.C 
-- ----------------------------------- 
CREATE  TABLE IF NOT EXISTS mydb.C ( 
  Z1 INT NOT NULL, 
  A1 INT NOT NULL, 
  C1 INT NOT NULL, 
  C2 VARCHAR(45) NOT NULL, 
  PRIMARY KEY (Z1, C1), 
  INDEX C_A_fk1 (Z1 ASC, A1 ASC), 
  CONSTRAINT C_A_fk1 
    FOREIGN KEY (Z1, A1) 
    REFERENCES mydb.A (Z1, A1) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION); 
-- ----------------------------------- 
-- Table mydb.D 
-- ----------------------------------- 
CREATE  TABLE IF NOT EXISTS mydb.D ( 
  Z1 INT NOT NULL, 
  D1 INT NOT NULL, 
  B1 INT NOT NULL, 
  C1 INT NOT NULL, 
  D2 VARCHAR(45) NOT NULL, 
  PRIMARY KEY (Z1, D1), 
  INDEX D_B_fk1 (Z1 ASC, B1 ASC), 
  INDEX D_C_fk1 (Z1 ASC, C1 ASC), 
  CONSTRAINT D_B_fk1 
    FOREIGN KEY (Z1, B1) 
    REFERENCES mydb.B (Z1, B1) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION, 
  CONSTRAINT D_C_fk1 
    FOREIGN KEY (Z1, C1) 
    REFERENCES mydb.C (Z1, C1) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION);