Validation Mcd, moyen plus simple ?

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 ?

http://www.developpez.net/forums/att...les.png/?stc=1

Merci du coup de pouce
Seb

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.
http://www.fsmwarden.com/developpez/...native_MLD.jpg

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

Bonsoir Seb,

Citation:

---

Envoyé par seb_perl

Pourquoi ne pas propager juste ModeleId ?

---

Considérons l’exemple tabulaire qui suit :


Marque

Code:

---

1 2 3 4 5

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

---

Modele

Code:

---

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:

---

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:

---

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:

---

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:

---

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 :

http://www.fsmwarden.com/developpez/...deleId_MLD.jpg


Ce qui donne en SQL :

Code:

---

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:

---

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:

---

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.

Citation:

---

Envoyé par seb_perl

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 :

http://www.fsmwarden.com/developpez/...s_Offre_01.jpg

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

http://www.fsmwarden.com/developpez/...ffre_01_PK.jpg


=>

http://www.fsmwarden.com/developpez/...s_Offre_02.jpg


2) La relation entre Offre et Motorisation convient :

http://www.fsmwarden.com/developpez/...s_Offre_03.jpg


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

http://www.fsmwarden.com/developpez/...s_Offre_04.jpg

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

http://www.fsmwarden.com/developpez/...s_Offre_05.jpg

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

http://www.fsmwarden.com/developpez/...s_Offre_06.jpg

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

http://www.fsmwarden.com/developpez/...native_MLD.jpg

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

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...

Bonjour Sébastien,

Reprenons le modèle que vous proposez :


http://www.fsmwarden.com/developpez/..._2010(seb).jpg

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 :


http://www.fsmwarden.com/developpez/..._Dagobert).jpg


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 :


http://www.fsmwarden.com/developpez/...mrel_Eloi).jpg


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 :


http://www.fsmwarden.com/developpez/...imaire_KO).jpg


Remise en ordre en agissant sur # :


http://www.fsmwarden.com/developpez/...imaire_OK).jpg




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) :


http://www.fsmwarden.com/developpez/...n_Key(seb).jpg


Cochons donc marque_id comme il convient :


http://www.fsmwarden.com/developpez/...ey(fsmrel).jpg



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é :

http://www.fsmwarden.com/developpez/...native_MLD.jpg


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)

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

Citation:

---

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.

Citation:

---

Envoyé par CinePhil

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.

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:

---

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:

---

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

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

Bonsoir Sébastien,

Citation:

---

Envoyé par seb_perl

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:

---

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:

---

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.

Citation:

---

Envoyé par seb_perl

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 :

http://www.fsmwarden.com/developpez/...e_Etape_06.jpg

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

Code:

---

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) ;

---

Citation:

---

Envoyé par fsmrel

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.

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} :

http://www.fsmwarden.com/developpez/...e_Etape_01.jpg


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 :

http://www.fsmwarden.com/developpez/...e_Etape_02.jpg

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

http://www.fsmwarden.com/developpez/...e_Etape_03.jpg


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

http://www.fsmwarden.com/developpez/...e_Etape_04.jpg


http://www.fsmwarden.com/developpez/...e_Etape_05.jpg


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

Citation:

---

Envoyé par 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 ...

---

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

Hello,
J'ai utilisé votre problématique comme tuto personnel.
Mais avec la vesrion Workbench 5.2.27 j'ai :
http://www.vocape.com/Picture1.png
Que faire ??

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 :

http://www.fsmwarden.com/developpez/..._jeanll_01.jpg

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

http://www.fsmwarden.com/developpez/..._jeanll_02.jpg

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 :

http://www.fsmwarden.com/developpez/..._jeanll_03.jpg

Et vous remettez les attributs dans le bon ordre :

http://www.fsmwarden.com/developpez/..._jeanll_04.jpg

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 :

http://www.fsmwarden.com/developpez/..._jeanll_05.jpg

bon, alors revenons à l'exemple du départ (avec la table "marques" appellée Z).
Workbench me génère ceci :
http://vocape.com/Picture%203.png

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 ??
http://vocape.com/Picture%204.png

ce qui pond :

Code:

---

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;

---

Ç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:

---

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:

---

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).

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

http://www.fsmwarden.com/developpez/..._jeanll_06.jpg

Code:

---

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);

---