Discussion :

[mango86]

Bonjour,

Débutant en gestion de base de données, je me questionne au sujet de la mise à jour des tables associatives.

Dans un projet personnel, je souhaite mettre en place une base de données de livres, articles et magazines. Entre mes entités "Auteurs" et "Livres", j'ai une association dont les cardinalités maximales sont toutes deux égales à n, je dois donc créer une table associative.

Tous mes documents sont rassemblés dans une arborescence de dossiers pour les catégoriser par thèmes, j'ai nommé chacun de mes fichiers (ouvrages) suivant le format : titre - prenom nom auteur - date de parution. Quand il y a plusieurs co-auteurs, j'ai pris le premier cité dans l'ouvrage. Une requête Power Query me permet ensuite d'exporter l'arborescence de mon dossier racine et d'obtenir, entre autres, la liste des titres et des auteurs sans doublon.

Maintenant, pour avoir l'ensemble des co-auteurs de l'ouvrage, je dois compléter ma table auteur en ajoutant tous les co-auteurs (qui m'auraient jamais été auteur "premier cité") et créer ensuite ma table associative avec les identifiants (id_auteur, id_ouvrage). Mais avant de me lancer tête baissée dans l'écriture de cette table, je me pose le problème de sa mise à jour. En effet, suite à l'enregistrement d'un fichier par exemple, ma requête Power Query va insérer une ligne correspondant dans le tableau d'export relativement à sa position dans l'arborescence. Du coup, tout l'indiçage des tables, notamment la table associative est à refaire

Avez-vous des méthodes à me proposer pour n'écrire qu'une seule fois la table associative (en tout cas) et avoir une mise à jour sécurisée ? Si vous avez des liens, des ressources ou des exemples sur lesquels je pourrais m'inspirer, je suis preneur.

Ne sachant pas trop si je dois chercher du côté SGBD ou vers PowerQuery - Excel, je ne poste peut-être pas dans la bonne section du forum, du coup n'hésitez pas à le déplacer

Merci par avance,

[tatayo]

Bonjour,
Je ne vois pas pourquoi tu devrais modifier la table Auteur ici.
L'auteur "principal" d'un ouvrage peut tout à fait être l'auteur 'secondaire" d'un autre ouvrage (même si pour ma part je trouve cette notion pour le moins "bizarre").
Je pense donc que cette notion de rang doit être dans la table associative, avec comme clé unique L'id du livre et le rang si tu ne veux pas d'ex-aequo.


Tatayo.

[escartefigue]

Bonjour,

Tout comme Tatayo, je ne suis pas convaincu par la notion d'auteur principal, mais même s'il fallait la retenir, associer auteur et co-auteur indépendamment du livre n'a pas de sens.

Voici ci-dessous un sujet sur la modélisation d'une base de données bibliothèque.
https://www.developpez.net/forums/d2...cd-bdd-livres/

La réponse n°3 propose un modèle "type" de MCD pour une bibliothèque de prêt
La réponse n°13 se concentre sur la partie ouvrage et édition, en y ajoutant la notion de collection et en ignorant la partie prêt propre aux médiathèques.

[mango86]

Bonjour,

Je n'ai sans doute pas été assez clair. Je ne cherche pas du tout à avoir un "rang" parmi les auteurs.

Je ne vois pas pourquoi tu devrais modifier la table Auteur ici.

Cela vient de la manière de mettre à jour mes tables - mais peut-être qu'il y a mieux à faire.
1. Au départ, tous mes fichiers sont enregistrés dans une arborescence de dossiers. Chaque nom de fichier est formaté suivant titre ouvrage - prenom nom - date de parution
J'ai choisi Prenom et nom du premier auteur sur la couverture de l'ouvrage s'il y en a plusieurs.
2. Par une requête Power Query, j'exporte l'arborescence à partir du dossier racine dans Excel, de cette manière, j'obtiens un tableau avec un nom de fichier par ligne.
Une ligne ressemble à : chemin.1 - chemin.2 - chemin.3 - (...) - titre ouvrage - prenom nom - date de parution (Chaque "-" sépare une colonne)
Si je viens enregistrer un nouveau fichier (je vérifie qu'il n'existe pas au préalable) dans mon arborescence, il va donc se positionner dans le tableau suivant son chemin : chemin.1 - chemin.2 ...
3. Je sépare ensuite en deux tables les titres (uniques) et les noms d'auteurs, un code élimine les doublons pour ceux-ci. Je pourrais donc sans difficulté avoir une relation 1 auteur pour n ouvrages, et insérer une clé étrangère id_auteur dans ma table d'ouvrage.
Mais dans ma construction, il me manque obligatoirement des auteurs, comme à chaque fois je n'ai renseigné que le premier. Je dois donc trouver un moyen de compléter ma table Auteurs et aussi créer mettre à jour la table associative entre Auteurs et Ouvrages.

Comment procéder ? Est-ce que je dois complétement abandonner cette méthode de mise à jour et partir sur de nouvelles bases ?

Et pour ce qui est l'export de l'arborescence, cela m'a semblé utile pour caractériser l'appartenance d'un ouvrage à une thématique, sous-thématique etc. et donc pour cela je pense arriver à la création d'une table intervallaire comme expliqué ici

Merci par avance

[escartefigue]

Votre méthode n'est pas la bonne, il faut l'abandonner : elle n'est pas fiable et contre-performante.

Déjà, la plupart des auteurs écrivent sous un ou plusieurs pseudonymes, il faut en tenir compte, le pseudo est associé à l'ouvrage, les noms et prénoms sont invariants quelque soit l'ouvrage d'un même auteur.

Ensuite, nom et prénom ça fait court pour identifier de façon certaine un auteur : il y a des synonymes (Alexandre Dumas père et fils pour ne citer que les plus célèbres). Attention donc à ne pas associer des ouvrages à de mauvais auteurs.

Un ouvrage pouvant avoir plusieurs auteurs, il faut dès la création de l'ouvrage, lui associer le ou les auteurs, avec éventuellement un rôle (scénariste, illustrateur, coloriste...).
Tenez compte aussi du fait que certains ouvrages n'ont pas d'auteur connu.

Tenez également compte du fait qu'un titre n'est pas unique en soi, plusieurs auteurs ont pu écrire des ouvrages portant le même titre et un même titre peut faire l'objet de plusieurs tomes (ayant ou pas un sous titre...)

[Paprick]

Bonjour,

Tous les bons conseils donnés dans les messages ci-dessus conduisent à la nécessité absolue de, tout d'abord, bien construire le Modèle Conceptuel de Données.
Les tables associatives (qui apparaitront dans le MLD) seront ensuite générées et traitées très naturellement.

[mango86]

Merci beaucoup pour vos indications, je vais donc abandonner l'idée de mettre à jour mes tables par une requête.

@ Paprick Effectivement, j'ai commencé par construire un MCD qui a été bien amélioré au fil des messages dans ce post MCD bibliotheque

Voici le MLD correspondant :


Je suis un peu rebuté par le nombre d'identifiants que je vois apparaître dans les tables, même si c'est sans doute normal qu'ils apparaissent. A ce stade, je peux commencer par remplir les tables correspondant aux entités (non issues d'une association) avec l'ensemble des enregistrements connus, ou y a-t-il une étape préliminaire ?

PS : Je vais ajouter un ou deux attributs à Auteurs et Ouvrages pour lever toute ambigüité, même si pour la taille de ma BDD, je ne pense pas qu'il y en aura.

Bonne soirée

[escartefigue]

bonsoir mango86

Je n'y avais pas prêté attention dans l'autre discussion sur la modélisation, mais avoir identifié l'ouvrage relativement au type est suspect.
Qu'est-ce que le type ? À préciser, mais il est fort probable que l'ouvrage existe même en l'absence de type, auquel cas l'ouvrage n'est pas une entité-type faible et ne doit pas être identifié relativement au type.

Ensuite, sur le "comment" charger les différentes tables, il faut privilégier une solution qui évite les mises à jour multiples.
Donc, à partir du moment où vous avez toutes les informations relatives à un ouvrage, il faut créer dans une même transaction, les auteurs s'ils ne sont pas déjà connus, l'ouvrage et les différentes occurrences de lien entre l'un et l'autre.

[mango86]

Bonsoir escartefigue,

Qu'est-ce que le type ?

Dans mon MCD, le type peut-être un livre, un magazine, une encyclopédie et j'ai quelques bulletins ou articles, je crois que c'est tout.

Pour les mises à jour multiples, d'accord, je vais changer ma façon de voir les choses. Si ce n'est pas trop difficile à mettre en oeuvre, j'importerai mes tables depuis excel vers phpmyadmin, sinon je saisirai les champs un par un.

il faut créer dans une même transaction, les auteurs s'ils ne sont pas déjà connus, l'ouvrage et les différentes occurrences de lien entre l'un et l'autre.

Là, je ne suis pas sûr de bien comprendre.

[escartefigue]

Dans mon MCD, le type peut-être un livre, un magazine, une encyclopédie et j'ai quelques bulletins ou articles, je crois que c'est tout.

OK, donc ça confirme que l'ouvrage n'est pas une entité-type faible, il ne faut pas l'identifier relativement au type.

Là, je ne suis pas sûr de bien comprendre.

Dans une base de données, l'une des choses essentielles est de garantir la qualité et la cohérence des données.
Or, insérer d'abord un premier auteur d'un ouvrage, puis, dans un deuxième temps, les autres auteurs, crée une incohérence des données.
La notion de "transaction" est ce qui permet de garantir la cohérence des données dans la base : soit toutes les données ont été ajoutées et on les valide (ordre COMMIT), soit un problème est rencontré qui ne permet pas de valider un ensemble cohérent et on abandonne la mise à jour (ordre ROLLBACK).

[mango86]

Bonsoir,

Voici ci-dessous le MLD mis-à-jour. Je vois qu'il y a un astérisque à droite de la clé étrangère id_collection dans la table Ouvrage, y a-t-il une signification particulière ?



Merci

[Paprick]

Bonsoir,

Voici ci-dessous le MLD mis-à-jour. Je vois qu'il y a un astérisque à droite de la clé étrangère id_collection dans la table Ouvrage, y a-t-il une signification particulière ?

Cela signifie que cette clé étrangère peut prendre une valeur NULL : c'est le cas lorsque l'on utilise cardinalités 0,1

[escartefigue]

Ou plus précisément un marqueur "nul".
En effet, "nul" n'est pas une valeur, c'est tout le contraire, une absence de valeur

[mango86]

Bonjour

Merci Paprick et escartefigue pour vos réponses et précisions.

J'ai commencé à remplir ma base de données de toutes les informations disponibles pour quelques ouvrages avec l'interface phpmyadmin en saisissant les champs un par un, sans utiliser de requête. Je crois que c'est le plus sûr dans un premier temps.

Egalement, j'ai commencé à renseigner ma table réflexive theme comme-ci dessous :

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
MariaDB [tempbd]> SELECT * FROM theme;
+----------+----------------------+------------+
| id_theme | theme                | id_theme_1 |
+----------+----------------------+------------+
|        1 | trahison             |       NULL |
|        2 | amour                |       NULL |
|        3 | amitie               |       NULL |
|        4 | liberte              |       NULL |
|        5 | education            |       NULL |
|        6 | historique           |       NULL |
|        7 | prehistoire          |          6 |
|        8 | antiquite            |          6 |
|        9 | moyen age            |          6 |
|       10 | epoque moderne       |          6 |
|       11 | epoque contemporaine |          6 |
|       12 | age de la pierre     |          7 |
|       13 | age du bronze        |          7 |
|       14 | age du fer           |          7 |
|       15 | haute antiquite      |          8 |
|       16 | antiquite classique  |          8 |
|       17 | basse antiquite      |          8 |
|       18 | haut moyen age       |          9 |
|       19 | moyen age central    |          9 |
|       20 | bas moyen age        |          9 |
|       21 | XVIeme siecle        |         10 |
|       22 | XVIIeme siecle       |         10 |
|       23 | XVIIIeme siecle      |         10 |
|       24 | XIXeme siecle        |         11 |
|       25 | XXeme siecle         |         11 |
|       26 | XXIeme siecle        |         11 |
+----------+----------------------+------------+
26 rows in set (0.001 sec)

Et j'aimerais bien pouvoir la transformer grâce à une procédure en table intervallaire. Voici la table que je souhaite obtenir :

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
MariaDB [tempbd]> SELECT * FROM theme_inter;
+----------+----------------------+------+------+
| id_theme | theme                | bg   | bd   |
+----------+----------------------+------+------+
|        1 | trahison             |    1 |    2 |
|        2 | amour                |    3 |    4 |
|        3 | amitie               |    5 |    6 |
|        4 | liberte              |    7 |    8 |
|        5 | education            |    9 |   10 |
|        6 | historique           |   11 |   52 |
|        7 | prehistoire          |   12 |   19 |
|        8 | antiquite            |   20 |   27 |
|        9 | moyen age            |   28 |   35 |
|       10 | epoque moderne       |   36 |   43 |
|       11 | epoque contemporaine |   44 |   51 |
|       12 | age de la pierre     |   13 |   14 |
|       13 | age du bronze        |   15 |   16 |
|       14 | age du fer           |   17 |   18 |
|       15 | haute antiquite      |   21 |   22 |
|       16 | antiquite classique  |   23 |   24 |
|       17 | basse antiquite      |   25 |   26 |
|       18 | haut moyen age       |   29 |   30 |
|       19 | moyen age central    |   31 |   32 |
|       20 | bas moyen age        |   33 |   34 |
|       21 | XVIeme siecle        |   37 |   38 |
|       22 | XVIIeme siecle       |   39 |   40 |
|       23 | XVIIIeme siecle      |   41 |   42 |
|       24 | XIXeme siecle        |   45 |   46 |
|       25 | XXeme siecle         |   47 |   48 |
|       26 | XXIeme siecle        |   49 |   50 |
+----------+----------------------+------+------+
26 rows in set (0.001 sec)

J'ai pas mal cherché sur le net une procédure qui serait facilement adaptable, malheureusement je suis loin d'avoir le niveau suffisant en SQL et je n'y arrive pas, ne serait-ce que pour l'exécuter correctement.

Par exemple ci-dessous une procédure tirée de "Trees and hierarchies in sql for smarties" - Converting Adjacency List to Nested Sets Model de Joe Celko. Par rapport au code originel, j'ai juste supprimé les commentaires et modifié le DELIMITER avant et après la création de la procédure :

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
CREATE TABLE Tree
(node CHAR(10) NOT NULL,
parent CHAR(10));
 
CREATE TABLE Stack
(stack_top INTEGER NOT NULL,
node CHAR(10) NOT NULL,
lft INTEGER,
rgt INTEGER);
 
 
DELIMITER |
 
CREATE PROCEDURE AdjToNested()
LANGUAGE SQL
DETERMINISTIC
BEGIN ATOMIC
DECLARE lft_rgt INTEGER;
DECLARE max_lft_rgt INTEGER;
DECLARE current_top INTEGER;
 
SET lft_rgt = 2;
SET max_lft_rgt = 2 * (SELECT COUNT(*) FROM Tree);
SET current_top = 1;
 
DELETE FROM Stack;
 
INSERT INTO Stack
SELECT 1, node, 1, max_lft_rgt
FROM Tree
WHERE parent IS NULL;
 
DELETE FROM Tree WHERE parent IS NULL;
 
WHILE lft_rgt <= max_lft_rgt - 1
DO IF EXISTS (SELECT *
FROM Stack AS S1, Tree AS T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top)
THEN BEGIN 
 
INSERT INTO Stack
SELECT (current_top + 1), MIN(T1.node), lft_rgt, NULL
FROM Stack AS S1, Tree AS T1
WHERE S1.node = T1.parent
AND S1.stack_top = current_top;
 
DELETE FROM Tree
WHERE node = (SELECT node
FROM Stack
WHERE stack_top = current_top + 1);
 
SET lft_rgt = lft_rgt + 1;
SET current_top = current_top + 1;
END;
ELSE BEGIN 
UPDATE Stack
SET rgt = lft_rgt,
stack_top = - stack_top 
WHERE stack_top = current_top;
 
SET lft_rgt = lft_rgt + 1;
SET current_top = current_top - 1;
END;
END IF;
END WHILE;
 
IF EXISTS (SELECT * FROM Tree)
THEN « error handling for orphans in original tree »
END IF;
END|
 
DELIMITER;

Il faudrait changer la structure des tables de départ et d'arrivée (ça je pense savoir faire ), mais ensuite modifier le code pour que ça marche correctement (aïe aïe aïe)

Si vous avez des solutions ou s'il est plus simple de partir sur une autre procédure, je vous en remercie par avance.

Bonne soirée,

[mango86]

Bonjour,

La procédure (cf mon précédent post) n'était pas écrite en SQL "MySQL/MariaDB" et apparemment n'est pas facilement adaptable à mon SGBD.

Finalement, avec de l'aide, je m'en suis sorti avec une solution en PHP s'inspirant de ce code,

La structure de ma nouvelle table theme est :

Code :

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

1
2
3
4
5
6
7
8
9
10
| theme | CREATE TABLE `theme` (
  `id_theme` int(11) unsigned NOT NULL,
  `theme` varchar(50) NOT NULL,
  `id_theme_1` int(11) unsigned DEFAULT NULL,
  `bg` int(11) unsigned DEFAULT NULL,
  `bd` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_theme`),
  KEY `id_theme_1` (`id_theme_1`),
  CONSTRAINT `theme_ibfk_1` FOREIGN KEY (`id_theme_1`) REFERENCES `theme` (`id_theme`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

En interprétant le script PHP sur mon serveur, il me recalcule les valeurs des deux colonnes bg et bd ajoutées.

Pour l'instant je n'ai pas mieux, c'est néanmoins suffisant pour effectuer des requêtes sur l'arbre.