Discussion :

[wizzzzzz]

Bonjour à tous,

Je vais de ce pas faire une petite présentation mais je pose ma question tout de même

Tout novice en base de données, SQL etc, je suis en train de monter un projet pour ma boite afin de sortir des stats sur notre production par rapport au marché. j'ai 4 tables comme ci-dessous :



plus tard viendra s'ajouter une autre table ( reliée à la table 'customers' ) avec les stats marché.

Pour rentrer les premières données que j'ai en main, j'ai été contraint de supprimer tous les index et clés FK, sinon ça ne fonctionnait pas ( saisie manuelle, transfert avec un fichier csv ou copier/coller via ACCESS ). Mais tous les mois, je vais compléter la base avec des milliers de lignes supplémentaires et je ne peux évidemment pas à chaque fois supprimer la moitié de la BDD.

J'ai l'impression que le système refusait mes données car il attendait que chaque ligne contienne des données pour les champs des clés étrangères. par exemple, si j'édite la table Customers, j'ai 4 colonnes. mais mon fichier excel avec les données n'en compte que 3, et du coup le csv aussi.

Quelqu'un sait comment faire pour ignorer la cellule vide et qu'elle se mette à jour toute seule avec les liaisons ?

Merci par avance
Steph

[wizzzzzz]

Salut,

je me réponds à moi-même; la solution trouvée pour le moment est la commande SET_FOREIN_KEY_CHECKS=0

A priori, c'est un peu barbare mais en attendant quelque chose de plus fin, on va considérer que ça convient.

Steph

[Artemus24]

Salut wizzzzzz.

Vous n'avez pas compris comment fonctionne les clefs étrangères (foreign key).
Vous avez deux tables, l'une est la table mère et l'autre la table fille.
Dans la table fille, vous avez une clef étrangère qui pointe sur la table mère.
Vous devez commencer par remplir la table mère avant la table fille. Pourquoi ?
Car la valeur qui est dans la clef étrangère doit exister dans la table mère avant de faire l'insertion dans la table fille.

Comme vous avez quatre tables, il y a un ordre de remplissage à faire sinon vous serez bloqué par vos clef étrangère.

Quelqu'un sait comment faire pour ignorer la cellule vide et qu'elle se mette à jour toute seule avec les liaisons ?

Si en Excel on nomme cela des cellules, en MySql on nomme cela des colonnes.

Une colonne ne se met pas à jour toute seule.
Vous devez faire le lien en faisant une recherche dans la table mère afin de récupérer la valeur de votre clef étrangère.

Autoriser la colonne à NULL n'est pas une bonne idée, surtout si son remplissage est obligatoire.
Cela vous permet de contourner votre difficulté de remplissage, mais vous risquez d'avoir un problème d'intégrité, ce qui est encore plus grave !

Si vous avez quatre fichiers Excel, vous devez avoir une correspondance pour mettre en relation vos lignes.

Vous devez faire votre remplissage en deux temps.
Dans un premier temps, vous créez des tables de travail, juste pour charger vos fichiers Excel dans des tables MySql.
Dans un second temps, vous remplissez vos tables définitives en faisant des requêtes qui récupères tout ce dont vous avez besoin.
Il y aura nécessairement une recherche à faire afin de trouver la valeur de la clef étrangère.

@+

[wizzzzzz]

Bonjour,

Merci beaucoup ! effectivement, je me rends compte de mon erreur. la première partie de la réponse me parle, je dois corriger mes liaisons car la table mère se retrouve fille.

la 2nde partie par contre, j'ai un peu de mal.

Vous devez faire votre remplissage en deux temps.
Dans un premier temps, vous créez des tables de travail, juste pour charger vos fichiers Excel dans des tables MySql

les tables de travail doivent faire partie de la BDD mais pas de liaisons/cardinalités avec les autres tables, c'est ça ?

Dans un second temps, vous remplissez vos tables définitives en faisant des requêtes qui récupères tout ce dont vous avez besoin.
Il y aura nécessairement une recherche à faire afin de trouver la valeur de la clef étrangère.

là par contre ça dépasse de loin mes compétences. je peux commencer à chercher sur internet comment faire mais je ne sais même pas quoi chercher


Merci
Steph

[Artemus24]

Salut wizzzzzz.

les tables de travail doivent faire partie de la BDD mais pas de liaisons/cardinalités avec les autres tables, c'est ça ?

Vous mettez vos tables de travail n'importe où !
Il n'y a aucune nécessité de les mettre dans votre base de données.

Elles ont juste vocation à être chargé à l'état brut afin de pouvoir faire des manipulation en sql pour charger vos tables définitives.
Vous avez raison, vous ne devez faire aucune liaison entre elles. Pourquoi ?
Car vous allez faire des extractions en vue de créer liens dans vos tables définitives.

Il y aura nécessairement une recherche à faire afin de trouver la valeur de la clef étrangère.

Vous avez raison. Prenons un exemple pour mieux illustrer mes propos.
Dans vos fichiers Excel, vous avez un libellé qui est fréquemment utilisé.
Le problème est cette fréquence, vu que vous avez disons une vingtaine de libellé différent alors que votre fichier Excel fait 5000 lignes.

Vous créez alors une table mère qui va contenir d'une manière unique le libellé.
Un identifiant auto incrémenté et votre libellé. Au total vous avez 20 lignes.

Ensuite dans votre table fille, vous allez créer une clef étrangère, en reprenant la clef de la table mère à chaque fois que vous aurez le même libellé.
Cela va vous faire gagner en espace car un libellé, donc une chaîne de caractères, occupe plus de place qu'un simple pointeur en integer.

là par contre ça dépasse de loin mes compétences. je peux commencer à chercher sur internet comment faire mais je ne sais même pas quoi chercher

Je ne vais pas faire votre travail en totalité, mais vous pouvez me donner un exemple qui vous pose problème.
Normalement, vous devez avoir une documentation entre la description de vos fichiers Excel et celles de vos tables MySql.

En plus, vous aurez certainement des conversions à faire.
Par exemple, le sexe peut être codé d'un coté en "H", "F" et de l'autre en 1, 2.

@+

[wizzzzzz]

Salut wizzzzzz.


Vous mettez vos tables de travail n'importe où !
Il n'y a aucune nécessité de les mettre dans votre base de données.

Elles ont juste vocation à être chargé à l'état brut afin de pouvoir faire des manipulation en sql pour charger vos tables définitives.
Vous avez raison, vous ne devez faire aucune liaison entre elles. Pourquoi ?
Car vous allez faire des extractions en vue de créer liens dans vos tables définitives.


Vous avez raison. Prenons un exemple pour mieux illustrer mes propos.
Dans vos fichiers Excel, vous avez un libellé qui est fréquemment utilisé.
Le problème est cette fréquence, vu que vous avez disons une vingtaine de libellé différent alors que votre fichier Excel fait 5000 lignes.

Vous créez alors une table mère qui va contenir d'une manière unique le libellé.
Un identifiant auto incrémenté et votre libellé. Au total vous avez 20 lignes.

Ensuite dans votre table fille, vous allez créer une clef étrangère, en reprenant la clef de la table mère à chaque fois que vous aurez le même libellé.
Cela va vous faire gagner en espace car un libellé, donc une chaîne de caractères, occupe plus de place qu'un simple pointeur en integer.


@+

ça j'ai tout compris, pas de souci

Salut wizzzzzz.

Je ne vais pas faire votre travail en totalité, mais vous pouvez me donner un exemple qui vous pose problème.
Normalement, vous devez avoir une documentation entre la description de vos fichiers Excel et celles de vos tables MySql.

En plus, vous aurez certainement des conversions à faire.
Par exemple, le sexe peut être codé d'un coté en "H", "F" et de l'autre en 1, 2.

@+

Là c'est juste que je ne sais pas et pour le moment, je ne trouve pas sur internet le type de requête pour aller chercher des données dans une table de travail et renvoyer ces données dans les tables définitives. c'est une association de INSERT ou UPDATE avec un SELECT sur la table de travail ?

Ci-dessous les modifications que je vais apporter à mon schéma. en rouge, changement d'une clé étrangère ( la table 'Customers' est la table mère ), en vert les éléments que je vais devoir mettre à jour tous les mois. En noir les clés inutiles que je dois supprimer. Les tables Customers et Agentgroup ne seront remplies qu'une fois. quelques petits ajustements mais que je pourrai faire en ligne de code facilement.



Merci
Steph

[Artemus24]

Salut wizzzzzz.

je ne trouve pas sur internet le type de requête pour aller chercher des données dans une table de travail et renvoyer ces données dans les tables définitives.

Voici un exemple du genre de travail que vous devez faire pour créer vos tables définitive.

1) on charge dans une table de travail un fichier Excel.

2) on doit répartir les données du fichier Excel (donc de la table de travail) dans trois tables définitives.

3) la table père contient le libellé nombre en français. Il y a déjà un existant qu'il ne faut pas écraser !

4) la table mère contient le nom de la langue en français. Il y a déjà aussi un existant qu'il ne faut pas écraser !

5) la table fille contient la traduction du nombre dans différentes langues.
Pour cela, on récupère les pointeurs de la table père (pour le nombre) et de la table mère (pour la langue).
Il faut aussi supprimer tous les doublons au cas où !

6) on crée une table poubelle pour récupérer tout ce qui n'a pas été chargé dans la table fille.

7) on supprime la table de travaille puisque le chargement et la répartition des données sont terminés.

8) on fait la requête sur nos trois tables afin de savoir si le résultat produit est conforme à ce que nous attendons.

Voici l'exécution :

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
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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS trav
--------------
 
--------------
CREATE TABLE trav (
  `id`    integer unsigned not null auto_increment primary key,
  `col1`  varchar(255)         null,
  `col2`  varchar(255)         null,
  `col3`  varchar(255)         null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA LOCAL INFILE 'fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ','
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (`col1`,`col2`,`col3`)
--------------
 
--------------
COMMIT
--------------
 
--------------
select * from trav
--------------
 
+----+--------+----------+--------------+
| id | col1   | col2     | col3         |
+----+--------+----------+--------------+
|  1 | quatre | Anglais  | four         |
|  2 | quatre | Allemand | vier         |
|  3 | quatre | Italien  | quattro      |
|  4 | trois  | anglais  | three        |
|  5 | cinq   | Allemand | fünf         |
|  6 | cinq   | Italien  | cinque       |
|  7 | cinq   | Anglais  | five         |
|  8 | NULL   | NULL     | vingt-quatre |
|  9 | six    | Anglais  | six          |
| 10 | six    | Allemand | sechs        |
| 11 | six    | Italien  | sei          |
| 12 | un     | Espagnol | uno          |
| 13 | deux   | Espagnol | dos          |
| 14 | trois  | Espagnol | tres         |
| 15 | quatre | Espagnol | cuatro       |
| 16 | cinq   | Espagnol | cinquo       |
| 17 | six    | Espagnol | seis         |
+----+--------+----------+--------------+
--------------
DROP TABLE IF EXISTS pere
--------------
 
--------------
CREATE TABLE pere (
  `id`      integer unsigned not null auto_increment primary key,
  `nombre`  varchar(255)     not null,
  index `idx` (`nombre`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `pere` (`nombre`) value ('un'),('deux'),('trois')
--------------
 
--------------
select * from pere
--------------
 
+----+--------+
| id | nombre |
+----+--------+
|  2 | deux   |
|  3 | trois  |
|  1 | un     |
+----+--------+
--------------
insert into `pere` (`nombre`)
  select  distinct t.col1
    from  `trav` as t
   where  not exists (select 1 from `pere` as p where p.nombre = t.col1)
     and  t.col1 is not null
--------------
 
--------------
select * from pere
--------------
 
+----+--------+
| id | nombre |
+----+--------+
|  5 | cinq   |
|  2 | deux   |
|  4 | quatre |
|  6 | six    |
|  3 | trois  |
|  1 | un     |
+----+--------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS mere
--------------
 
--------------
CREATE TABLE mere (
  `id`      integer unsigned not null auto_increment primary key,
  `langue`  varchar(255)     not null,
  index `idx` (`langue`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `mere` (`langue`) value ('Allemand'), ('Italien'), ('Anglais')
--------------
 
--------------
select * from mere
--------------
 
+----+----------+
| id | langue   |
+----+----------+
|  1 | Allemand |
|  3 | Anglais  |
|  2 | Italien  |
+----+----------+
--------------
insert into `mere`  (`langue`)
   select distinct  t.col2
              from  `trav` as t
  where not exists  (select 1 from `mere` as m  where m.langue = t.col2)
               and  t.col2 is not null
--------------
 
--------------
select * from mere
--------------
 
+----+----------+
| id | langue   |
+----+----------+
|  1 | Allemand |
|  3 | Anglais  |
|  4 | Espagnol |
|  2 | Italien  |
+----+----------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS fille
--------------
 
--------------
CREATE TABLE fille (
  `id`          integer unsigned not null auto_increment primary key,
  `pere_id`     integer unsigned not null,
  `mere_id`     integer unsigned not null,
  `traduction`  varchar(255)     not null,
  unique index `idx` (`pere_id`,`mere_id`),
  CONSTRAINT `FK_PERE` FOREIGN KEY (`pere_id`) REFERENCES `pere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_MERE` FOREIGN KEY (`mere_id`) REFERENCES `mere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `fille` (`pere_id`,`mere_id`,`traduction`) value
  (1, 1, 'eins'), (2, 1, 'zwei'), (3, 1, 'drei'),
  (1, 2, 'uno'),  (2, 2, 'due'),  (3, 2, 'tre'),
  (1, 3, 'one'),  (2, 3, 'two'),  (3, 3, 'tree')
--------------
 
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       1 |       2 | uno        |
|  5 |       2 |       2 | due        |
|  6 |       3 |       2 | tre        |
|  7 |       1 |       3 | one        |
|  8 |       2 |       3 | two        |
|  9 |       3 |       3 | tree       |
+----+---------+---------+------------+
--------------
insert into `fille` (`pere_id`,`mere_id`,`traduction`)
    select  p.id,
            m.id,
            t.col3
      from  `trav` as t
 
inner join  `pere` as p
        on  p.nombre = t.col1
 
inner join  `mere` as m
        on  m.langue = t.col2
 
     where  t.col3 is not null
 
        on  duplicate key update traduction = values(traduction)
--------------
 
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       1 |       2 | uno        |
|  5 |       2 |       2 | due        |
|  6 |       3 |       2 | tre        |
|  7 |       1 |       3 | one        |
|  8 |       2 |       3 | two        |
|  9 |       3 |       3 | three      |
| 10 |       4 |       3 | four       |
| 11 |       4 |       1 | vier       |
| 12 |       4 |       2 | quattro    |
| 13 |       5 |       1 | fünf       |
| 14 |       5 |       2 | cinque     |
| 15 |       5 |       3 | five       |
| 16 |       6 |       3 | six        |
| 17 |       6 |       1 | sechs      |
| 18 |       6 |       2 | sei        |
| 19 |       1 |       4 | uno        |
| 20 |       2 |       4 | dos        |
| 21 |       3 |       4 | tres       |
| 22 |       4 |       4 | cuatro     |
| 23 |       5 |       4 | cinquo     |
| 24 |       6 |       4 | seis       |
+----+---------+---------+------------+
--------------
alter table `fille` drop   column id
--------------
 
--------------
alter table `fille` add    column id integer first
--------------
 
--------------
set @id:=0
--------------
 
--------------
update `fille` set id=(@id:=@id+1) order by mere_id, pere_id
--------------
 
--------------
alter table `fille` change column id id integer unsigned auto_increment primary key first
--------------
 
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       4 |       1 | vier       |
|  5 |       5 |       1 | fünf       |
|  6 |       6 |       1 | sechs      |
|  7 |       1 |       2 | uno        |
|  8 |       2 |       2 | due        |
|  9 |       3 |       2 | tre        |
| 10 |       4 |       2 | quattro    |
| 11 |       5 |       2 | cinque     |
| 12 |       6 |       2 | sei        |
| 13 |       1 |       3 | one        |
| 14 |       2 |       3 | two        |
| 15 |       3 |       3 | three      |
| 16 |       4 |       3 | four       |
| 17 |       5 |       3 | five       |
| 18 |       6 |       3 | six        |
| 19 |       1 |       4 | uno        |
| 20 |       2 |       4 | dos        |
| 21 |       3 |       4 | tres       |
| 22 |       4 |       4 | cuatro     |
| 23 |       5 |       4 | cinquo     |
| 24 |       6 |       4 | seis       |
+----+---------+---------+------------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS poubelle
--------------
 
--------------
CREATE TABLE poubelle (
  `id`    integer unsigned not null auto_increment primary key,
  `col1`  varchar(255)         null,
  `col2`  varchar(255)         null,
  `col3`  varchar(255)         null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `poubelle` (`col1`,`col2`,`col3`)
    select  col1,
            col2,
            col3
      from  `trav`
 
     where  col1 is null
        or  col2 is null
--------------
 
--------------
select * from poubelle
--------------
 
+----+------+------+--------------+
| id | col1 | col2 | col3         |
+----+------+------+--------------+
|  1 | NULL | NULL | vingt-quatre |
+----+------+------+--------------+
--------------
COMMIT
--------------
 
--------------
drop table `trav`
--------------
 
--------------
select      m.langue,
            p.nombre,
            f.traduction
      from  `fille` as f
 
inner join  `pere` as p
        on  p.id = f.pere_id
 
inner join  `mere` as m
        on  m.id = f.mere_id
--------------
 
+----------+--------+------------+
| langue   | nombre | traduction |
+----------+--------+------------+
| Allemand | un     | eins       |
| Allemand | deux   | zwei       |
| Allemand | trois  | drei       |
| Allemand | quatre | vier       |
| Allemand | cinq   | fünf       |
| Allemand | six    | sechs      |
| Italien  | un     | uno        |
| Italien  | deux   | due        |
| Italien  | trois  | tre        |
| Italien  | quatre | quattro    |
| Italien  | cinq   | cinque     |
| Italien  | six    | sei        |
| Anglais  | un     | one        |
| Anglais  | deux   | two        |
| Anglais  | trois  | three      |
| Anglais  | quatre | four       |
| Anglais  | cinq   | five       |
| Anglais  | six    | six        |
| Espagnol | un     | uno        |
| Espagnol | deux   | dos        |
| Espagnol | trois  | tres       |
| Espagnol | quatre | cuatro     |
| Espagnol | cinq   | cinquo     |
| Espagnol | six    | seis       |
+----------+--------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
Appuyez sur une touche pour continuer...

@+