[Résolu] Structure de données SQL - jointure (Wordpress)

Bonjour,

je suis actuellement en train de coder un site décrivant les données techniques d'un ensemble de produits et j'aimerai organiser correctement ma base de donnée.

Dans un premier temps, j'ai utilisé une table unique pour tester les requêtes :
A un produit on lui associe une liste d'éléments simples (nom, présentation, fiche technique, schéma...) à champ unique. Si l'élément est présent dans la table, on l'affiche dans un onglet, sinon il est masqué.

Désormais je désire permettre à un produit de posséder plusieurs fiches techniques, schémas, ... J'ai donc décidé de modifier ma base de donnée (ça revient à transformer la relation 0-1 à 0-n).

Plusieurs problématiques, pour la suite on considérera un lien PDF contenue dans la table Fiche_Technique pour l'exemple. Elle contient donc deux champs : le nom et l'url.

1. Sachant qu'un produit a entre 0 et N liens PDF et qu'un lien PDF est associé à un et un seul produit, quelle méthode considérer ?

- créer une table de jointure (nommons là P_FT pour Produit/Fiche_technique) contenant une clef primaire composée de deux éléments : foreign_P et foreign_FT (égaux aux ID des tables respectives).
- rajouter un champ dans chaque élément (Fiche_technique ici) nommé foreign_P, égal au seul ID du produit contenant ce lien PDF.
http://i.imgur.com/AihBB0g.png
Ci-dessus sur l'image, à gauche une table de jointure, à droite l'ajout d'un champ dans Fiche_technique. Je n'ai pas affiché les relations qui sont citées plus haut :
-> Un produit contient 0..n fiches techniques
-> Une fiche technique appartient à 1..1 produit

2. Au niveau de la manière d'écrire correctement les requêtes SQL j'ai cru comprendre qu'il était préférable d'utiliser INNER JOIN plutôt que WHERE lorsqu'il s'agit de jointures, c'est bien cela ? Contrairement à l'image les champs foreign_P et foreign_FT définissant la Primary Key de la table de jointure doivent être de type "INT" tout simplement et non "Auto_increment" ?
Ensuite, pour renommer une table j'écrivais d'habitude "FROM Table1 AS t1,Table2 AS t2" : l'écriture "FROM Table1 t1, Table2 t2" fonctionne aussi ? Laquelle est la plus "propre" d'utilisation ?

L'utilisateur doit pouvoir modifier la BDD en rajoutant des fiches techniques à un produit : dans le cas de la table de jointure la requête INSERT INTO semble plus compliquée puisque l'on doit modifier deux tables (Fiche_Technique et PFT) au lieu d'une (Fiche_Technique).
Au niveau de l'insert de donnée on aurait ça ? Quelles possibilités perdons-nous en n'utilisant pas de table de jointure ?

INSERT INTO Fiche_technique (nom,url,foreign_P)
VALUES ('texte à afficher','url du PDF',$id_P);

$id_P étant une variable PHP contenant l'ID (ou le nom) du produit en question.


Au niveau de la requête cela donnerait donc ceci ?

SELECT FT.nom, FT.url
FROM Fiche_technique FT
INNER JOIN Produit P
ON id_P = foreign_P;

Édition : ce modèle logique de données relationnel est-il valide pour gérer un ensemble de produits possédant chacun des caractéristiques différentes ?
http://i.imgur.com/dfBYx4G.png
Modifié par Ara (09 Jul 2015 - 14:00)

Salut,

Pour la question 1 je pense que la solution de droite est la mieux, même si je pige pas pourquoi on stock le nom du produit dans la fiche mais bon.

Pour la question 2, je suis pas sur que cela change grand chose de faire un inner join ou un where. Personnellement j'ai tendance à mettre des where partout plutôt que des inner/outer/left... join, quand je relis du code sql alors que j'en ai pas fais depuis un moment ça m’évite d'avoir à me souvenir les différences entre chaque...
Il faut voir si c'est des auto incrémente de int ou de long, pour que cela correspond ensuite pour le type int pour les clés étrangères. Auto incrémente permet de faire des inserts sans avoir à gérer la valeur de la clé primaire (en gros ça permet d’éviter d'avoir à faire un select max(id)+1 from tatable à chaque insert)
Modifié par mathieu1004 (09 Jul 2015 - 11:52)

Salut, merci de la réponse rapide.

Un élément de Fiche_Technique est un lien PDF, il contient donc un nom (celui affiché) et une url (le lien vers lequel on est renvoyé quand on clique sur le nom). Un produit représente en fait une famille de produits et chacun possède sa fiche technique, d'où l'intérêt d'avoir un champ "nom" dans la Fiche_Technique.

Je sais globalement comment fonctionne Auto_increment (même si il m'avait semblé que Phpmyadmin enregistrait la prochaine valeur de Auto_increment à chaque INSERT, plutôt que de chercher la plus grande valeur déjà existante, la différence étant que si on supprime la dernière ligne (disons id=7) la valeur sera non pas de 6+1=7 mais toujours 7+1=8). Je me demandais plutôt si je devais écrire moi-même les Foreign Key ou si cela pouvait se faire automatiquement.

Plus précisément, je travaille actuellement sur Wordpress et je gère ma base de donnée à l'aide des fonctions existantes. Sans rentrer dans les détails pour toute modification de type CREATE TABLE et UPDATE, on utilise une fonction dbDelta() qui ne gère pas l'attribut FOREIGN KEY. Les tables natives de Wordpress n'ont pas de structure nécessitant de Foreign Key et cette propriété n'a pas été implantée : Source

Je pense que le plus simple (avec la deuxième solution) est de simuler la Foreign Key (qui n'est pas géré par la BDD de Wordpress) en créant un champ dans chaque table schema/video/... (foreign_xx) qui serait égal à l'id du bon produit. Cela est-il une méthode assez "propre" de procéder dans le contexte où je ne peux pas définir officiellement le champ comme étant une Foreign Key ? Est ce que cela peut amener des erreurs et si oui lesquels ?

Au niveau de la création des tables j'ai quelque chose comme ça :

$sql = "CREATE TABLE $table_produit (
		id_P bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		nom_P text  NOT NULL,
		desc_P longtext,
		PRIMARY KEY  (id_P)
	) /* ... */ "
dbDelta($sql);

$sql = "CREATE TABLE $table_fiche_technique (
		id_FT bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		nom_FT text NOT NULL,
		url_FT text NOT NULL,
		foreign_FT text NOT NULL,
		PRIMARY KEY  (id_FT)
	) /* ... */ "
dbDelta($sql);

Je compte simuler les clefs étrangères lors de mes requêtes avec une condition "WHERE foreign_xx = nom_P". Techniquement une clef étrangère renvoie vers une clef principale id_P mais nom_P est unique (il contient le nom du produit) et il ne devrait pas y avoir de soucis.

Cela peut-il engendrer des soucis sachant que le champ nom_P est unique mais pas la clé principale ? Qu'apporte en plus le fait qu'un champ ait l'attribut "Foreign Key" par rapport à si on le simule dans nos jonctions ?
Modifié par Ara (09 Jul 2015 - 15:44)

Non non je parlais bien des clés étrangères qui permettent d'assurer de la cohérence dans tes données.
Exemple simple pour ton plugin :
Disons qu'il existe 50 produit avec des ids de 1 à 50.
Ton admin veut ajouter une fiche technique au dernier produit qu'il vient de crée grâce a ton plugin.
Ton plugin va présenter 3 champs a remplir pour une fiche technique : le nom , l'url , l'identifiant du produit.

Et bien si par exemple il se trompe et marque 60 au lieu de 50 , sans clé étrangère, il n'y aura aucune erreur de retourné. Cela va donc être a toi de mettre en place la vérification dans ton plugin afin de gérer si le numéro qu'il a mis est bien un numéro d'identifiant existant ou pas.
Si il y avait une clé étrangère, tu n'aurai qu'a gérer les retours d'erreur de ta base de données qui n'autoriserai pas l'ajout d'un élément avec une clé étrangère qui n'existe pas dans produit.

En gros : les clés étrangères ne sont pas une obligation, mais en étant bien gérées, elles permettent de gagner du temps pour certaines opérations (particulièrement ajout dans la table ou il y a la clé étrangère, modification/suppression dans la table qui est référencé par la clé étrangère)

Après il faut y faire attention, en étant mal géré ça peut faire des gros blocages.. exemple con :système de site fermé qui fonctionne par invitation: pour qu'un utilisateur puisse s'enregistrer il doit indiqué l'identifiant de l'utilisateur qui l'a invité -> logiquement je met une clé étrangère sur le champ id_parrain qui renvoie vers l'id d'un autre utilisateur, et qui doit donc être valide. Problème a l'utilisation .. je peux pas ajouter de 1er utilisateur car la clé étrangère m’empêche d'ajouter un utilisateur si je ne fourni pas un numéro d’identifiant qui existe.
Donc bon la c'est assez simple a voir, mais quand tu as des clé étrangères qui partent un peu dans tous les sens sur plusieurs tables, tu as vite fais de faire un truc qui semble super logique, mais qui a l'utilisation t’empêche de faire un ajout dans toutes les tables ^^. (ça se corrige assez simplement en désactivant la contrainte pour pouvoir faire le 1er ajout mais bon ^^)