Discussion :

[hbx360]

J'ai modifié mon MCD de cette façon (certaine info ne m'était pas demandé ou traité d'une autre façon) :


Mais comment je fais pour mettre l' id Constructeur dans l'annonce comme tu m'as montrés dans ton exemple car le script SQL généré par Looping ne me le met pas c'est pour ça que j'avais pensé à relié la table constructeur à l'annonce aussi.
Et dois-je obligatoirement mettre aussi l'id Caractéristique_value et l'id Caractéristique dans annonce pour afficher les valeurs de caractéristique_value et les caractéristique qui doivent obligatoirement être mis dans l'annonce.

Pour les tables Constructeur, Modèle, caractéristique_value, et caractéristique je me suis basé sur un échantillon d'une bdd de voitures.

[Paprick]

Bonsoir,

Mais comment je fais pour mettre l' id Constructeur dans l'annonce comme tu m'as montrés dans ton exemple car le script SQL généré par Looping ne me le met pas c'est pour ça que j'avais pensé à relié la table constructeur à l'annonce aussi.
Et dois-je obligatoirement mettre aussi l'id Caractéristique_value et l'id Caractéristique dans annonce pour afficher les valeurs de caractéristique_value et les caractéristique qui doivent obligatoirement être mis dans l'annonce.

Il n'est pas forcément nécessaire d'avoir Id_Constructeur dans Annonce, puisque vous pourriez y accéder via Modèle. Cependant, comme le suggère fsmrel, cela permettrait d'optimiser les accès.
Si vous souhaitez le faire, il suffit d'indiquer "Identifiant Relatif" sur la patte 1,1 de l'association Avoir. Par contre, attention : cela produira forcément une surclé.
Même chose pour Id Caractéristique_value et Id Caractéristique.

[fsmrel]

comment je fais pour mettre l' id Constructeur dans l'annonce

Dans le post #19, l’attribut marqueId a été propagé jusqu’à ANNONCE parce que j’ai utilisé l’identification relative (1,1(R)) :

[MARQUE]---1,N---(Avoir)---1,1(R)---[MODELE]

Quoi qu’il en soit, au vu de la requête qui suit, l’identification relative n’intervient pas pour obtenir la marque référencée par ANNONCE transitivement via MODELE, en effet c’est le nom de la marque qu’il faut obtenir et non pas l’identifiant, lequel doit normalement rester caché.

Pour voir les annonces, et pour reprendre l’exemple SQL du post #19 :

SELECT marqueNom, modeleNom, description 
FROM   ANNONCE as x 
  JOIN MODELE as y ON x.marqueId = y. marqueId AND x.modeleId = y.modeleId  
  JOIN MARQUE as z ON y.marqueId = z.marqueId
;

=>

marqueNom     modeleNom     description
Marque 1      M1            occasion à saisir 

[fsmrel]

c’est le nom de la marque qu’il faut obtenir et non pas l’identifiant, lequel doit normalement rester caché.

Il est recommandé que les valeurs prises par un identifiant soient calculées par le SGBD et connues seulement de lui (les forums des divers SGBD sont remplis des plaintes de ceux qui ont fait référence aux valeurs calculées par leur SGBD et s’en sont mordu les doigts parce que celui-ci les a remplacées, « modifiées » sans prévenir...) Comme dit la reine : never explain, never complain. 
Pour signifier au SGBD qu’on lui sous-traite le calcul des valeurs des identifiants, on utilise habituellement l’auto-incrémentation (IDENTITY avec SQL Server, Sybase et DB2, AUTO_INCREMENT avec MySQL, SERIAL avec PostgreSQL, etc.) Avec Looping, dans la fenêtre des propriétés, vous choisissez votre SGBD (fenêtre de gauche, MLD SQL / SGBD cible), et pour les identifiants des entités-types vous choisissez le type Numérique / Compteur. Dans l’exemple qui suit j’utilise SQL Server, vous nous direz quel est votre SGBD.

Création des tables :

CREATE TABLE MARQUE
(
   marqueId INT IDENTITY,
   marqueNom VARCHAR(48) NOT NULL,
   CONSTRAINT MARQUE_PK PRIMARY KEY(marqueId)
);

CREATE TABLE MODELE
(
   marqueId INT,
   modeleId INT IDENTITY,
   modeleNom VARCHAR(48) NOT NULL,
   CONSTRAINT MODELE_PK PRIMARY KEY(marqueId, modeleId),
   CONSTRAINT MODELE_MARQUE_FK FOREIGN KEY(marqueId) 
       REFERENCES MARQUE(marqueId),
   CONSTRAINT MODELE_UK UNIQUE(marqueId, modeleNom)
);

CREATE TABLE ANNONCE
(
   annonceId INT IDENTITY,
   marqueId INT,
   modeleId INT NOT NULL,
   description VARCHAR(48) NOT NULL,
   CONSTRAINT ANNONCE_PK PRIMARY KEY(annonceId),
   CONSTRAINT ANNONCE_MODELE_FK FOREIGN KEY(marqueId, modeleId) 
       REFERENCES MODELE(marqueId, modeleId)
);

Création de marques. A noter que les inserts ne font jamais référence aux colonnes pour lesquelles on utilise l’auto-incrémentation (IDENTITY pour SQL Server), c’est la cuisine du SGBD :

INSERT INTO MARQUE (marqueNom)
VALUES
    ('Marque 1')
  , ('Marque 2')
;
SELECT marqueNom FROM MARQUE ; 

=>

marqueNom

Marque 1
Marque 2

Création des modèles. On est obligé de fournir la valeur de la colonne marqueId. Puisqu’on s’interdit pour cette colonne de coder les valeurs « en dur », pour obtenir la valeur de la colonne marqueId on se sert de la colonne marqueNom de la table MARQUE. Par exemple pour la marque 'Marque 1' :

SELECT marqueId FROM MARQUE WHERE marqueNom = 'Marque 1'

D’où l’insert (à comparer avec celui du post #19 où les valeurs prises par marqueId sont codées en dur) :

INSERT INTO MODELE (marqueId, modeleNom)
VALUES
    ((SELECT marqueId FROM MARQUE WHERE marqueNom = 'Marque 1'), 'M1')
  , ((SELECT marqueId FROM MARQUE WHERE marqueNom = 'Marque 1'), 'M2')
  , ((SELECT marqueId FROM MARQUE WHERE marqueNom = 'Marque 1'), 'M3')
  , ((SELECT marqueId FROM MARQUE WHERE marqueNom = 'Marque 2'), 'M1')

Au résultat :

SELECT marqueNom, modeleNom FROM MODELE as x
  JOIN MARQUE as y on x.marqueId = y.marqueId ;

=>

marqueNom      modeleNom

Marque 1      M1
Marque 1      M2
Marque 1      M3
Marque 2      M1

Création des annonces (par exemple pour le modèle 'M1' de la marque 'Marque 2'), le principe est le même.

INSERT INTO ANNONCE (marqueId, modeleId, description)
VALUES
    (
     (SELECT marqueId 
      FROM   MARQUE 
      WHERE  marqueNom = 'Marque 2')
   , (SELECT modeleId 
      FROM   MODELE as x JOIN MARQUE as y ON x.marqueId = y.marqueId
      WHERE  marqueNom = 'Marque 2' AND modeleNom = 'M1')
   , 'occasion à saisir'   
   )
;

Au résultat (on reprend la requête du post #19) :

SELECT marqueNom, modeleNom, description 
FROM   ANNONCE as x 
  JOIN MODELE as y ON x.marqueId = y. marqueId AND x.modeleId = y.modeleId  
  JOIN MARQUE as z ON y.marqueId = z.marqueId
;

=>

marqueNom       modeleNom     description

Marque 2        M1            occasion à saisir

Dans tout ça, on n’a jamais vu les valeurs des colonnes auto-incrémentées, elles sont restées sous le capot et le SGBD s’en débrouille.

[hbx360]

Je suis sur phpMyAdmin avec MariaDB.
Je vois parce que j'ai rentré à la main les id j'ai fais comme ça :

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
INSERT INTO constructeur (nom)
VALUES 
  ('BMW'),
  ('Mercedes'),
  ('Renault'),
  ('Peugeot'),
  ('Honda'),
  ('Toyota'),
  ('FIAT'),
  ('Ford');
 
INSERT INTO modele (
  id_Constructeur,
  libelle 
)
VALUES
-- BMW
  ('1', 'Série 1'),
  ('1', 'Série 6'),
 
-- MERCEDES
  ('2', 'Classe C4'),
  ('2', 'Classe E5'),
 
-- RENAULT
  ('3', 'Scenic 4'),
  ('3', 'Megan 4 Rs'),
 
-- PEUGEOT
  ('4', '208'),
  ('4', '308'),
 
-- HONDA
  ('5', 'Civic 10'),
  ('5', 'Civic 8'),
  ('5', 'Cr-v 5'),
 
-- TOYOTA
  ('6', 'Gt86'),
  ('6', 'Land Cruiser'),
 
-- FIAT
  ('7', 'Fullback'),
  ('7', 'Punto 3'),
 
-- FORD
  ('8', 'Mustang 6'),
  ('8', 'Puma 2');
 
INSERT INTO caracteristique (nom)
VALUES
  ('Nombre de portes'),
  ('Nombre de places'),
  ('Puissance'),
  ('Puissance Fiscale'),
  ('Co2 (g/Km)'),
  ('Boîte de vitesse'),
  ('Type'),
  ('Carburant');
 
INSERT INTO caracteristique_value (
  id_Modele, 
  id_Caracteristique,
  valeur)
VALUES
 
  -- BMW
  -- prix 44 650€
  ('1', '1', '5'),
  ('1', '2', '5'),
  ('1', '3', '190'),
  ('1', '4', '10'),
  ('1', '5', '140'),
  ('1', '6', 'Mécanique'),
  ('1', '7', 'Coupé'),
  ('1', '8', 'Diesel'),
 
-- prix 147 600€
  ('2', '1', '2'),
  ('2', '2', '4'),
  ('2', '3', '47'),
  ('2', '4', '560'),
  ('2', '5', '239'),
  ('2', '6', 'Automatique'),
  ('2', '7', 'Cabriolet'),
  ('2', '8', 'Essence'),
 
-- .....
-- .....
-- .....
 
 
  -- FORD
  -- prix 58 000€
  ('16', '1', '2'),
  ('16', '2', '4'),
  ('16', '3', '440'),
  ('16', '4', '35'),
  ('16', '5', '274'),
  ('16', '6', 'Automatique'),
  ('16', '7', 'Cabriolet'),
  ('16', '8', 'Essence'),
 
  -- prix 24 900€
  ('17', '1', '5'),
  ('17', '2', '5'),
  ('17', '3', '125'),
  ('17', '4', '6'),
  ('17', '5', '97'),
  ('17', '6', 'Mécanique'),
  ('17', '7', 'SUV'),
  ('17', '8', 'Hybride (essence/électricité)');
 
  INSERT INTO utilisateur (estAdmin, email, motDePasse)
  VALUES
    ('1', 'Lodevie@gmail.com', '123aBc456');

C'est pour ça que je me posais la question comment faire pour pour ajouter de nouveau modèle parce que sinon sa m'aurait décalé les id.

@fsmrel merci beaucoup du temps et de l'investissement que tu prends à me répondre sa m'aide beaucoup !
@Paprick merci pour ton aide.

[hbx360]

Je suis sur phpMyAdmin avec MariaDB.
Je vois parce que j'ai rentré à la main les id j'ai fais comme ça :

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
INSERT INTO constructeur (nom)
VALUES 
  ('BMW'),
  ('Mercedes'),
  ('Renault'),
  ('Peugeot'),
  ('Honda'),
  ('Toyota'),
  ('FIAT'),
  ('Ford');
 
INSERT INTO modele (
  id_Constructeur,
  libelle 
)
VALUES
-- BMW
  ('1', 'Série 1'),
  ('1', 'Série 6'),
 
-- MERCEDES
  ('2', 'Classe C4'),
  ('2', 'Classe E5'),
 
-- RENAULT
  ('3', 'Scenic 4'),
  ('3', 'Megan 4 Rs'),
 
-- PEUGEOT
  ('4', '208'),
  ('4', '308'),
 
-- HONDA
  ('5', 'Civic 10'),
  ('5', 'Civic 8'),
  ('5', 'Cr-v 5'),
 
-- TOYOTA
  ('6', 'Gt86'),
  ('6', 'Land Cruiser'),
 
-- FIAT
  ('7', 'Fullback'),
  ('7', 'Punto 3'),
 
-- FORD
  ('8', 'Mustang 6'),
  ('8', 'Puma 2');
 
INSERT INTO caracteristique (nom)
VALUES
  ('Nombre de portes'),
  ('Nombre de places'),
  ('Puissance'),
  ('Puissance Fiscale'),
  ('Co2 (g/Km)'),
  ('Boîte de vitesse'),
  ('Type'),
  ('Carburant');
 
INSERT INTO caracteristique_value (
  id_Modele, 
  id_Caracteristique,
  valeur)
VALUES
 
  -- BMW
  -- prix 44 650€
  ('1', '1', '5'),
  ('1', '2', '5'),
  ('1', '3', '190'),
  ('1', '4', '10'),
  ('1', '5', '140'),
  ('1', '6', 'Mécanique'),
  ('1', '7', 'Coupé'),
  ('1', '8', 'Diesel'),
 
-- prix 147 600€
  ('2', '1', '2'),
  ('2', '2', '4'),
  ('2', '3', '47'),
  ('2', '4', '560'),
  ('2', '5', '239'),
  ('2', '6', 'Automatique'),
  ('2', '7', 'Cabriolet'),
  ('2', '8', 'Essence'),
 
-- .....
-- .....
-- .....
 
 
  -- FORD
  -- prix 58 000€
  ('16', '1', '2'),
  ('16', '2', '4'),
  ('16', '3', '440'),
  ('16', '4', '35'),
  ('16', '5', '274'),
  ('16', '6', 'Automatique'),
  ('16', '7', 'Cabriolet'),
  ('16', '8', 'Essence'),
 
  -- prix 24 900€
  ('17', '1', '5'),
  ('17', '2', '5'),
  ('17', '3', '125'),
  ('17', '4', '6'),
  ('17', '5', '97'),
  ('17', '6', 'Mécanique'),
  ('17', '7', 'SUV'),
  ('17', '8', 'Hybride (essence/électricité)');
 
  INSERT INTO utilisateur (estAdmin, email, motDePasse)
  VALUES
    ('1', 'Lodevie@gmail.com', '123aBc456');

C'est pour ça que je me posais la question comment faire pour pour ajouter de nouveau modèle parce que sinon sa m'aurait décalé les id.

cela permettrait d'optimiser les accès.

Pourrais-tu m'en dire plus, comment optimiser les accès.

attention : cela produira forcément une surclé.

Pourquoi il faut faire attention ?

@fsmrel merci beaucoup du temps et de l'investissement que tu prends à me répondre sa m'aide beaucoup !
@Paprick merci pour ton aide.

[fsmrel]

Bonsoir,

attention : cela produira forcément une surclé.

Pourquoi il faut faire attention ?

Dans la théorie relationnelle, on appelle surclé K d’une variable relationnelle R (table en SQL) un sous-ensemble d’attributs de [l’en-tête de] R, tel que deux tuples t1 et t2 de R (lignes en SQL) ne puissent jamais avoir la même valeur pour K. On dit alors qu’on respecte la règle d’unicité des clés.

La surclé K est clé candidate si elle vérifie en outre une 2e règle, à savoir la règle d’irréductibilité des clés : il n’existe pas de sous-ensemble strict de K garantissant lui aussi la règle d’unicité.

La variable relationnelle R peut avoir plusieurs surclés parmi lesquelles plusieurs clés candidates. Dans le cas particulier de SQL, une de ces clés est à déclarer comme clé primaire (notion non essentielle, donc virée il y a une trentaine d’années de la théorie relationnelle au nom du rasoir d’Ockham). Avec SQL Server, pas de problème, voyez l’instruction “CREATE TABLE MODELE” dans le post #24.
Je ne connais pas MariaDB, mais dans le cas de MySQL il est avéré que cette instruction doit être réécrite ainsi :

CREATE TABLE MODELE
(
        marqueId     INT
      , modeleId     INT    AUTO_INCREMENT
      , modeleNom    VARCHAR(48) NOT NULL
    , CONSTRAINT MODELE_PK PRIMARY KEY(modeleId)
    , CONSTRAINT MODELE_AK UNIQUE (marqueId, modeleId)
    , CONSTRAINT MODELE_MARQUE_FK FOREIGN KEY(marqueId) 
          REFERENCES MARQUE(marqueId)
    , CONSTRAINT MODELE_UK UNIQUE(marqueId, modeleNom)
); 

En effet, les auteurs de MySQL ont une connaissance insuffisante et erronée de la théorie relationnelle et MySQL exige à tort que, à cause de la présence du paramètre AUTO_INCREMENT, l’attribut modeleId donne lieu à la clé primaire singleton {modeleId} de la table MODELE. Fonctionnellement, c’est évidemment la paire {marqueId, modeleId} qui est clé candidate, aussi met-on en oeuvre la contrainte MODELE_AK (avec la clause UNIQUE garantissant l’unicité) pour être en adéquation avec le fonctionnel. On se fiche complètement que MySQL garantisse l’unicité pour « sa clé primaire », ça ne nous gêne pas puisque, comme je l’ai écrit dans le post #24, les seules valeurs qu’on utilise sont celles des données naturelles (valeurs des colonnes marqueNom, modeleNom dans les exemples que j’ai fournis), que ce soit pour les instructions SELECT, INSERT, UPDATE, et DELETE.
 

Avec Looping, le code généré pour MySQL est le suivant :

CREATE TABLE MODELE(
   marqueId INT,
   modeleId INT AUTO_INCREMENT,
   modeleNom VARCHAR(48) NOT NULL,
   CONSTRAINT MODELE_PK PRIMARY KEY(marqueId, modeleId),
   CONSTRAINT MODELE_MARQUE_FK FOREIGN KEY(marqueId) REFERENCES MARQUE(marqueId)
);

Pour déclarer la clé candidate {marqueId, modeleId}, au lieu de le faire manuellement dans le code, avec Looping on peut ajouter une règle (cf. le ruban de Looping) :

[fsmrel]

cela permettrait d'optimiser les accès.

Pourrais-tu m'en dire plus, comment optimiser les accès.

Comme disait le Général : Vaste programme !

Concernant plus précisément l’identification relative, j’en parle ici, avec la diminution sensible des jointures : 1.7. Dénormalisation vs amélioration (optimisation), au paragraphe « Note concernant l'identification relative ».

Cela dit, depuis toujours, les index (en général des arbres balancés) sont faits pour accéder directement à l’information. La recherche d’une ligne dans une table d’un milliard de lignes peut coûter entre disons une milliseconde et l’infini selon que le SGBD dispose ou non des index qui vont bien.
Les SGBD SQL proposent l’instruction EXPLAIN pour savoir si une requête mettra à profit les index existants. Cf. la doc MySQL.

Par exemple, rechercher les descriptions pour la marque 'Marque 2' :

EXPLAIN 
SELECT description 
FROM   ANNONCE as x
  JOIN MARQUE as y ON x.marqueId = y. marqueId
WHERE  marqueNom = 'Marque 2' 
;  

Ou

EXPLAIN
SELECT description 
FROM   ANNONCE  
WHERE  marqueid IN (SELECT marqueid FROM marque  WHERE marqueNom = 'Marque 2')  
;

Etc.


Pour savoir quels sont les index banchés sur la table ANNONCE :

SHOW INDEX FROM ANNONCE ;

[hbx360]

Merci pour tes informations,
Sur ton post 19 sur la création de la table

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE ANNONCE
(
   annonceId INT,
   marqueId INT,
   modeleId INT NOT NULL,
   description VARCHAR(48) NOT NULL,
   CONSTRAINT ANNONCE_PK PRIMARY KEY(annonceId),
   CONSTRAINT ANNONCE_MODELE_FK FOREIGN KEY(marqueId, modeleId) 
       REFERENCES MODELE(marqueId, modeleId)
);

tu as mis les clés étrangères marqueId et modeleId donc pour mon MCD est-ce que je dois faire comme ceci :

pour avoir la clé étrangère du constructeur dans l'annonce ?

[Paprick]

Bonjour,

tu as mis les clés étrangères marqueId et modeleId donc pour mon MCD est-ce que je dois faire comme ceci :
pour avoir la clé étrangère du constructeur dans l'annonce ?

Non, pour obtenir le code proposé par fsmrel, li faut faire comme lui, à savoir mettre un identifiant relatif sur la patte 1,1 de Avoir.

[hbx360]

Merci pour ta réponse Paprick.

[hbx360]

J'ai une erreur quand j'ai créée la clé relative quand je fais ma requête sql voici l'erreur :



Voilà ce que j'ai mis dans mon entité modèle :

[fsmrel]

J'ai une erreur quand j'ai créée la clé relative quand je fais ma requête SQL

Je rappelle ce que j’ai écrit dans le post #27, à savoir qu’avec Looping le code généré pour MySQL est le suivant :

CREATE TABLE MODELE(
   marqueId INT,
   modeleId INT AUTO_INCREMENT,
   modeleNom VARCHAR(48) NOT NULL,
   CONSTRAINT MODELE_PK PRIMARY KEY(marqueId, modeleId),
   CONSTRAINT MODELE_MARQUE_FK FOREIGN KEY(marqueId) 
       REFERENCES MARQUE(marqueId)
);

Et j’ai précisé qu’avec DB2, SQL Server, etc. ce code est correct, tandis que dans le cas particulier de MySQL il doit être modifié puisque, par méconnaissance la théorie relationnelle, ses auteurs se sont plantés, en conséquence de quoi MySQL déclenche l’erreur que vous avez signalée. Toujours dans le post #27 j’ai indiqué le code de remplacement qui va bien :

CREATE TABLE MODELE
(
        marqueId     INT
      , modeleId     INT    AUTO_INCREMENT
      , modeleNom    VARCHAR(48) NOT NULL
    , CONSTRAINT MODELE_PK PRIMARY KEY(modeleId)
    , CONSTRAINT MODELE_AK UNIQUE (marqueId, modeleId)
    , CONSTRAINT MODELE_MARQUE_FK FOREIGN KEY(marqueId) 
          REFERENCES MARQUE(marqueId)
    , CONSTRAINT MODELE_UK UNIQUE(marqueId, modeleNom)
);  

Pour produire ce code avec Looping, on pourrait créer une règle pour passer du 1er code au 2e, mais ce crétin de MySQL vérifie instantanément que la clé primaire est singleton, et la modification du code doit donc être faite manuellement...

[escartefigue]

L'une des multiples bizarreries de MySQL...

[hbx360]

Merci pour tes messages et ton aide et excuse moi j'ai manqué de vigilance sur tes postes, j'aurai du comprendre que c'était à moi de changer manuellement les données.

[hbx360]

Une question frsmel mais si tu as une base de données super grande tu fais donc tout manuellement, mais sa peut prendre un temps énorme si tu as par exemple 30 ou 40 voir plus de requêtes à modifier.

Donc je suppose que personne n'utilise MySql en entreprise.

[fsmrel]

si tu as une base de données super grande tu fais donc tout manuellement, mais sa peut prendre un temps énorme si tu as par exemple 30 ou 40 voir plus de requêtes à modifier.

Donc je suppose que personne n'utilise MySql en entreprise.

Tout ce que je propose ici n’est que du niveau prototype, voire jouet, juste pour illustrer le problèmes de modélisation en relation avec les SGBD. Si effectivement j’ai une base de données de 1000 tables ou plus (et j’ai été confronté à ça plus d’une fois !) pour lesquelles il faut modifier les CREATE TABLE (les clauses PRIMARY KEY dans le cas qui nous occupe), je n’agirai évidemment pas manuellement, comme tous les DBA je m’écrirai un petit programme pour le faire, quel que soit le SGBD (et ça m’est arrivé en permanence d’écrire des programmes de « transformation des instructions »). L’utilisation de MySQL en entreprise n’est bien entendu pas à remettre en cause (en notant que du temps où je faisais le DBA, MySQL n’existait pas encore, en gros on avait le choix entre DB2 et Oracle, et ils n'étaient pas gratuits, c'est le moins qu’on puisse dire...)

[escartefigue]

bonsoir hbx360,

Bien que la question soit nominative, je me permets d'y répondre.

MySQL est plutôt courant en entreprise en particulier dans les PME.
Il a comme argument la gratuité, ce qui est loin d'être négligeable et également une interface graphique conviviale (workbench).

En sa défaveur, quelques bizarreries comme celle mentionnée par François (Fsmrel) et aussi quelques bugs et retards par rapport aux principaux SGBD leaders du marché.
Avec la V8, Mysql a rattrapé une partie de son retard en ajoutant notamment les CTE qui autorisent les fonctions récursives ainsi que les fonctions OLAP bien pratiques dans certains cas (avec 15 ans de retard sur la norme, mieux vaut tard que jamais ).

Ce bug sur les PK multi-colonnes m'en rappelle un autre : MySQL ne fait aucune distinction entre la PK et l'index cluster ! Autre étrangeté que rien ne justifie.

[hbx360]

fsmrel merci pour ses informations et merci aussi escartefigue pour ta réponse.

[fsmrel]

Et si l’on parlait des identifiants alternatifs...

Prenons le cas des annonces. Avec SQL, dès lors qu’on utilise l’auto-incrémentation et qu’à juste titre on n’utilise pas les valeurs fournies par le SGBD, il est nécessaire de disposer d’un attribut « naturel » pour accéder à une annonce en particulier, en l’occurrence il s’agit de l’attribut description :

SELECT constructeurNom, modeleNom, garageNom
FROM   ANNONCE as x 
  JOIN MODELE as y ON x.constructeurId = y.constructeurId 
                  AND x.modeleId = y.modeleId  
  JOIN CONSTRUCTEUR as z ON y.constructeurId = z.constructeurId 
  JOIN GARAGE as t ON x.garageId = t.garageId
WHERE  description = 'occasion à saisir'
; 

=>

constructeurNom   modeleNom   garageNom

Marque 2         M1          Gge chez Raoul

Mais cet attribut description n’est évidemment pas ce qui convient pour accéder à une annonce spécifique, ne serait-ce que parce que plusieurs annonces pourraient avoir la même description, et donc l’usage veut qu’on définisse un attribut qui corresponde à un code (dont les valeurs seront établies par la maîtrise d’oeuvre en conjugaison avec la maîtrise d’ouvrage), permettant d’accéder à une annonce en particulier.

Exemple, on définit un attribut annonceCode donnant lieu à la clé candidate, alternative {annonceCode} :

CREATE TABLE ANNONCE
(
        annonceId           INT AUTO_INCREMENT
      , constructeurId      INT
      , modeleId            INT NOT NULL
      , garageId            INT NOT NULL
      , annonceCode         VARCHAR(8) NOT NULL  
      , description         VARCHAR(48) NOT NULL
    , CONSTRAINT ANNONCE_PK PRIMARY KEY (annonceId)
    , CONSTRAINT ANNONCE_AK UNIQUE (annonceCode) 
    , CONSTRAINT ANNONCE_MODELE_FK FOREIGN KEY (constructeurId, modeleId) 
          REFERENCES MODELE(constructeurId, modeleId)
    , CONSTRAINT ANNONCE_GARAGE_FK FOREIGN KEY (garageId) 
        REFERENCES GARAGE (garageId)
);

Pour créer une annonce, en l’affectant du code 'a01' :

INSERT INTO ANNONCE (constructeurId, modeleId, garageId, annonceCode, description)
VALUES
    (
     (SELECT constructeurId 
      FROM   CONSTRUCTEUR 
      WHERE  constructeurNom = 'Marque 2')
   , (SELECT modeleId 
      FROM   MODELE as x 
        JOIN CONSTRUCTEUR as y ON x.constructeurId = y.constructeurId
      WHERE  constructeurNom = 'Marque 2' AND modeleNom = 'M1')
   , (SELECT garageId 
      FROM   GARAGE
      WHERE  garageCode = 'gar002')
   , 'a01' 
   , 'occasion à saisir'   
   )
; 

A noter que dans cet exemple les garages sont aussi dotés d’un code et qu’il faudra en faire autant pour les constructeurs et les modèles, bref généraliser ce système de codes dans le cadre du MCD.

Accès aux données de l’annonce de code 'a01' :

SELECT constructeurNom, modeleNom, garageNom, description 
FROM   ANNONCE as x 
  JOIN MODELE as y ON x.constructeurId = y.constructeurId 
                  AND x.modeleId = y.modeleId  
  JOIN CONSTRUCTEUR as z ON y.constructeurId = z.constructeurId
  JOIN GARAGE as t ON x.garageId = t.garageId
WHERE  annonceCode = 'a01'
;

=>

constructeurNom   modeleNom   garageNom        description

Marque 2      M1          Gge chez Raoul   occasion à saisir

Exemple de définition et d’utilisation des codes de façon générale.

Les garages :

CREATE TABLE GARAGE
(
        garageId    INT AUTO_INCREMENT
      , garageCode  VARCHAR(8) NOT NULL   
      , garageNom   VARCHAR(48) NOT NULL 
    , CONSTRAINT GARAGE_PK PRIMARY KEY (garageId)
    , CONSTRAINT GARAGE_AK UNIQUE (garageCode)  
) ;

INSERT INTO GARAGE (garageCode, garageNom)
VALUES
    ('gar001', 'Ets Naudin')
  , ('gar002', 'Gge chez Raoul')
;
SELECT garageCode, garageNom FROM GARAGE ;

=>

garageCode    garageNom

gar001        Ets Naudin
gar002        Gge chez Raoul

Les constructeurs (marques) :

CREATE TABLE CONSTRUCTEUR
(
        constructeurId      INT AUTO_INCREMENT
      , constructeurCode    VARCHAR(8) NOT NULL   
      , constructeurNom     VARCHAR(48) NOT NULL
    , CONSTRAINT CONSTRUCTEUR_PK PRIMARY KEY (constructeurId)
    , CONSTRAINT CONSTRUCTEUR_AK UNIQUE (constructeurCode) 
    , CONSTRAINT CONSTRUCTEUR_AK2 UNIQUE (constructeurNom)
); 

INSERT INTO CONSTRUCTEUR (constructeurCode, constructeurNom)
VALUES
    ('mk001', 'Marque 1')
  , ('mk002', 'Marque 2')
;
SELECT constructeurCode, constructeurNom FROM CONSTRUCTEUR ;

=>

constructeurCode   constructeurNom

mk001              Marque 1
mk002              Marque 2

Les modèles

CREATE TABLE MODELE
(
        constructeurId     INT
      , modeleId           INT    AUTO_INCREMENT
      , modeleCode         VARCHAR(48) NOT NULL   
      , modeleNom          VARCHAR(48) NOT NULL
    , CONSTRAINT MODELE_PK PRIMARY KEY(modeleId)
    , CONSTRAINT MODELE_AK UNIQUE(modeleCode) 
    , CONSTRAINT MODELE_AK2 UNIQUE(constructeurId, modeleId)
    , CONSTRAINT MODELE_AK3 UNIQUE(constructeurId, modeleNom)
    , CONSTRAINT MODELE_CONSTRUCTEUR_FK FOREIGN KEY(constructeurId) 
          REFERENCES CONSTRUCTEUR(constructeurId)
);

INSERT INTO MODELE (constructeurId, modeleCode, modeleNom)
VALUES
    ((SELECT constructeurId FROM CONSTRUCTEUR WHERE constructeurNom = 'Marque 1'), 'mod001', 'M1')
  , ((SELECT constructeurId FROM CONSTRUCTEUR WHERE constructeurNom = 'Marque 1'), 'mod002', 'M2')
  , ((SELECT constructeurId FROM CONSTRUCTEUR WHERE constructeurNom = 'Marque 1'), 'mod003', 'M3')
  , ((SELECT constructeurId FROM CONSTRUCTEUR WHERE constructeurNom = 'Marque 2'), 'mod004', 'M1')
;

SELECT constructeurNom, modeleCode, modeleNom FROM MODELE as x
  JOIN CONSTRUCTEUR as y on x.constructeurId = y.constructeurId ;

=>

constructeurNom   modeleCode   modeleNom

Marque 1          mod001       M1
Marque 1          mod002       M2
Marque 1          mod003       M3
Marque 2          mod004       M1 

Les annonces

CREATE TABLE ANNONCE
(
        annonceId           INT AUTO_INCREMENT
      , constructeurId      INT
      , modeleId            INT NOT NULL
      , garageId            INT NOT NULL
      , annonceCode         VARCHAR(8) NOT NULL
      , description         VARCHAR(48) NOT NULL
    , CONSTRAINT ANNONCE_PK PRIMARY KEY (annonceId)
    , CONSTRAINT ANNONCE_AK UNIQUE (annonceCode)  
    , CONSTRAINT ANNONCE_MODELE_FK FOREIGN KEY (constructeurId, modeleId) 
          REFERENCES MODELE(constructeurId, modeleId)
    , CONSTRAINT ANNONCE_GARAGE_FK FOREIGN KEY (garageId) 
        REFERENCES GARAGE (garageId)
);

INSERT INTO ANNONCE (constructeurId, modeleId, garageId, annonceCode, description)
VALUES
    (
     (SELECT constructeurId 
      FROM   CONSTRUCTEUR 
      WHERE  constructeurNom = 'Marque 2')
   , (SELECT modeleId 
      FROM   MODELE as x 
        JOIN CONSTRUCTEUR as y ON x.constructeurId = y.constructeurId
      WHERE  constructeurNom = 'Marque 2' AND modeleNom = 'M1')
   , (SELECT garageId 
      FROM   GARAGE
      WHERE  garageCode = 'gar002')
   , 'a01'
   , 'occasion à saisir'   
   )
;

SELECT constructeurNom, modeleNom, garageNom, description 
FROM   ANNONCE as x 
  JOIN MODELE as y ON x.constructeurId = y.constructeurId 
                  AND x.modeleId = y.modeleId  
  JOIN CONSTRUCTEUR as z ON y.constructeurId = z.constructeurId
  JOIN GARAGE as t ON x.garageId = t.garageId
WHERE  annonceCode = 'a01'
;

=>

constructeurNom   modeleNom   garageNom        description

Marque 2          M1          Gge chez Raoul   occasion à saisir

J’espère que le Capitaine Escartefigue et Paprick seront d’accord avec toute cette partie traitant des identifiants alternatifs...