Méthode pour créer une BDD "efficace" Résolu

Question

Hello,

alors voilà : je commence à m'intéresser aux BDD MySQL pour réaliser un site que j'ai en projet. Après avoir commencé à décortiquer un tuto relativement complet sur la gestion d'une BDD j'ai le sentiment que ça ne relève pas de l'exploit. Néanmoins pour mon site en projet tant qu'à faire j'aimerais tout de suite ne pas me lancer dans une structure "bancales" au niveau de la création des tables. J'entends par là que j'aimerais créer mes tables de manière à ce que les requêtes et leurs résultats soient le plus rapide et le mieux optimisé.
Pour entrer donc dans le détail :
ce sera un site communautaire où chaque membre inscrit pourra déposer ses oeuvres graphiques, en créant différents albums persos. L'idée c'est également que chaque membre inscrit "passif" pourra créer ses propres albums aussi en ajoutant les oeuvres qu'ils préfère des différents membres "actifs". De plus chaque membre "actif" ou "passif" pourra également attribuer une note aux oeuvres qu'il consultera, afin de faire un classement évolutif ans le temps.
Donc,j'avais pensé faire plutôt simple :
1) créer une table avec les champs :
- pseudo
- age
- email
- pays
- date d'inscription
2) créer une autre table plus fournie avec les champs :
- pseudo
- albums
- statut // statut = "actif" ou "passif"
3° créer une table qui stockerait les oeuvres avec les champs :
- pseudo
- albums
- oeuvre

Voilà en gros comment je voyais le truc, maintenant ma question est la suivante : st-ce que d'après vous c'est un "rangement" optimisé pour des requêtes aux résultats rapides ou est-c qu'il y a une autre méthode plus performante pour un gain en vélocité de réponse/affichage ?

Merci d'avance pour vos tuyaux 

A plus,

BBFUNK01
//C'est en forgeant qu'on devient forgeron... ;-) ;

aieeeuuuuu · Accepted Answer

ceci dit, l'auto-jointure existe bien, et il est tout a fait possible de faire une jointure d'une table sur elle meme :
Si par exemple tu as une table avec une liste de valeur avec des indices (qui se suivent, sans trou pour faire simple), et que tu veux récupérer les variations entre les indices (valeur - valeur précédente):

SELECT t2.indice, t2.valeur - t1.valeur AS variation
FROM MaTable t1
INNER JOIN MaTable t2 ON (t1.indice = t2.indice - 1)

En gros, cela te permet de mettre "cote a cote" deux ligne "qui se suivent"

tu peux aussi utiliser l'auto-joiture dans plein d'autres cas, comme une table représentant une hiérarchie par exemple, pour retrouver le parent d'un élément...

aieeeuuuuu · Answer

Bonjour

Une première chose déjà, évitez le pseudo comme identifiant, préférez un ID auto incrémenté, cela pour deux raisons :

1/ Si un membre veux changer de pseudo, ca sera très compliqué
mais surtout :
2/ vos jointure seront bien plus rapides entre deux colonnes de type INT que 2 colonnes de type VARCHAR

ensuite, pouvez vous donner le nom de vos tables, car la je ne comprend pas le rôle de la 2eme et donc de la troisième...

Si son rôle est de stocker la liste des albums de chaque membre, alors la colonne actif devrait être dans la table membre, car d'après votre description, ce sont les membres qui sont actifs/passifs.

Enfin, si la troisième table a pour but de stocker les œuvres de chaque album, ne mettez pas la colonne pseudo :
Les œuvres sont rattachées a un album, lui meme rattaché a un membre.

Je proposerai donc :
Table Membre :
IDMebre
pseudo
email
age
actif

Table Album
IDAlbum
IDMembre
Nom

Table Oeuvre
IDOeuvre
IDAblum
Nom

BBFUNK01 · Answer

Re,

merci pour ta réponse, si je comprends bien : il est préférable de circuler dans les tables au moyen de l'ID de type INT car c'est beaucoup plus rapide ?
Bon effectivement en y songeant la description de mes tables que j'ai fait dans ma question était un peu "à la volée".

J'ai créé la 1ère table de cette manière :
Table membres :
ID
pseudo
email
mdp
pays
statut //actif/passif
ddi //date d'inscription
val // ok ou pas ok -> ce qui correspond à la validation de l'inscription par email (est-ce nécessaire d'après toi ?)

Ensuite si je vais ton sens je verrai un truc comme ça :

Table albums :
IDMembre // sans auto-incrémentation (car comme chaque membre pourra créer plusieurs albums... Est-ce bon de cette manière ?
IDAlbum
nom

Table œuvres :
IDœuvre
IDAlbum
nom
note // de type INT car les œuvres pourront être notées par chaque membre

D'après toi de cette manière ma BDD sera fiable ?

BBFUNK01
//C'est en forgeant qu'on devient forgeron... ;-) ;

aieeeuuuuu · Answer

Re,

ca me parait pas mal.
Pour le flag de validation, si tu tiens a vérifier ca, c ok. 
Sinon, quand un membre passe-t-il de passif a actif ? pour la validation, tu pourrai jouer sur ce flag aussi :
NULL avant validation, puis passif, puis actif.

ou faire de même avec la date d'inscription (NULL tant que le membre n'a pas validé son email...)

Cependant, si ta définition de "actif" est : "un membre qui a déposé au moins une œuvre", tu n'a pas besoin de ce flag non plus (tu te prendra plus la tête à le mettre a jour, alors qu'une simple requete voire une vue si tu en as besoin, pourra te spécifier si un membre a déposé des œuvres ou pas).


Pour la table album, effectivement, IDMembre étant une clef étrangère, il ne doit pas être en auto-incrément. IDAblum oui par contre, de même la colonne IDOeuvre de la table Oeuvre

Enfin, il reste quand même un petit problème, concernant les notes :
Si chaque membre peux noter une oeuvre, tu ne peux pas mettre la colonne note dans la table oeuvre, qui ne contiendra qu'un ligne par oeuvre !
tu dois créer une table Note avec:
IDMembre
IDOeuvre
Note

Et si comme je le pense un membre ne pourra noter qu'une seule fois une oeuvre données, la clef primaire de cette table sera la clef composée (IDMembre, IDOeuvre)

Bon courage
++

BBFUNK01 · Answer

Hello,

en fait j'ai revu un peu ma copie car au final mes tables sont un peu plus étoffées que le "schéma" que j'en avais fait le 23/12.
Pour le coup concernant les notes, disons que ce ne sont pas réellement des notes à proprement parler, ce sont plutôt des "j'aime", pour schématiser sur le site il y aura 2 boutons : un bouton "j'aime" et un bouton "j'aime pas". Donc les œeuvres seront notées en fonction de leurs nombres de "j'aime" ou "j'aime pas", l'idée sera de faire simplement une requête du type :
$bdd->query('SELECT MAX(jaime) AS Nbrjaime FROM oeuvres');
Mais du coup la question que je me pose est la suivante :
étant donné que ma table œuvres contient les champs suivants :
IDœuvre 
IDAlbum 
nom 
note

...et que je souhaite pouvoir afficher en page d'accueil le nom de l'œuvre la mieux notée ainsi que le nom de l'artiste qui l'a réalisée, sachant que le nom de l'artiste n'est pas stocké dans ma table œuvres mais que je peux le retrouver dans la table albums grâce à l'IDAlbum, puis ensuite aller le chercher dans la table membres en récupérant le champ pseudo qui correspond aux données récupérées... Est-ce qu'il ne serait finalement pas plus simple de mettre un champ "pseudo" dans ma table œuvres ? Où alors ça te paraît plus académique (et surtout ça "économise" un champ dans la table œuvres) de faire la jointure sur 3 tables pour obtenir la seconde info ?

Je suis en train de faire les tests en ce moment même mais la grande question est finalement est-ce qu'il est vraiment intéressant d'économiser un champ dans la table œuvres ? (en imaginant qu'un jour ma table œuvres contiennent par exemple 100 000 entrées ...).

A plus et merci pour tes conseils

BBFUNK01
//C'est en forgeant qu'on devient forgeron... ;-) ;

BBFUNK01 · Answer

Re, bon finalement après tests j'opte pour la solution académique qui consiste à faire les jointures entre les tables. Cependant je me permets une questin subsidiaire : pour me familiariser avec MySQL et le langage SQL je m'appuie sur un tuto très complet sur un site bien connu, et dans le tuto en question il est vivement conseillé de faire les jointures entre les tables avec la méthode JOIN au détriment de la méthode WHERE qui serait visiblement une syntaxe déjà "vieillote". Qu'à cela ne tienne je me lance donc dans des essais avec la méthode JOIN, essais laborieux car je dois joindre 3 tables, après avoir multipliés les tests j'arrive à un script dans lequel je ne vois à mon sens aucune erreur : query('SELECT MAX(h.UP) NBRup, h.idAL Alb, h.oeuvre oeu, a.idMembre memb, m.pseudo pseu FROM hits h INNER JOIN albums a ON h.idAL a.idMembre INNER JOIN membres m ON a.idMembre m.pseudo'); while ($donnees = $reponse->fetch()) { echo $donnees['h.UP']. ' + ' . $donnees['Alb'] . ' + ' . $donnees['oeu'] . ' + ' . $donnees['memb'] . ' + ' . $donnees['pseu'] .' '; } $reponse->closeCursor(); } catch (Exception $e) { die('Erreur : ' . $e->getMessage()); } ?> Il a de toute manière forcément une erreur car je ne récupère pas les données souhaitées, mais seulement les "plus", voici ce que j'obtiens dans mon navigateur : + + + + Alors j'essaie avec la méthode WHERE : query('SELECT MAX(h.UP) as NBRup, h.idAL as Alb, h.oeuvre as oeu, a.idMembre as memb, m.pseudo as pseu FROM hits as h, albums as a, membres as m'); while ($donnees = $reponse->fetch()) { echo $donnees['NBRup']. ' + ' . $donnees['Alb'] . ' + ' . $donnees['oeu'] . ' + ' . $donnees['memb'] . ' + ' . $donnees['pseu'] .' '; } $reponse->closeCursor(); } catch (Exception $e) { die('Erreur : ' . $e->getMessage()); } ?> Et là ça marche du 1er coup. Je dis méthode WHERE mais dans les faits on ne peut finalement pas l'appeler ainsi vu que je n'utilise même pas le WHERE, mais le "critère" de récupération des données est juste le MAX afin de récupérer la valeur maximum d'un champ, et ensuite de récupérer les données en rapport dans les autres tables. Mais ça me chiffonne tout de même car comme il est apparemment conseillé d'utiliser JOIN, et bien j'aimerais y arriver afin de partir sur de bonnes bases. A mon avis je pense que là où ça pêche c'est que je veux récupérer la valeur MAX d'un champ, mais je n'ai aucune erreur de script à priori et ce qui me perturbe c'est que je récupère bien les "+" mais pas les données... . Donc si jamais tu as des suggestions je suis preneur. Thks , BBFUNK01 //C'est en forgeant qu'on devient forgeron... ;-) ;

aieeeuuuuu · Answer

Oui, reste sur la méthode "académique" comme tu dis, si elle est académique, c'est pour de bonnes raisons !

quand ta table aura 100 000 lignes, le fait d'avoir la colonne (et non pas champ) dans ta table album obligera ton SGBDR a travailler sur un jeu de données plus volumineux pour rien la plupart du temps. un exemple :

Imagine que tu cherches le nom de toutes les oeuvres du pseudo 'toto'
Si tu declare une colone varchar(50) dans ta table oeuvre, tu fera la requete :

SELECT nom
FROM Oeuvre
WHERE pseudo = 'toto'


qui te paraitra plus simple a ecrire (vrai) et te paraitra plus rapide a executer pour le SGBDR (faux) que celle ci

SELECT Nom
FROM Oeuvre
INNER JOIN Album ON Oeuvre.IDAlbum = Album.IDAlbum
INNER JOIN Membre ON Membre.IDMembre = Album.IDMembre
WHERE Membre.pseudo = 'toto'


Dans la premiere, le SGBDR va lire toute la table (a moins que tu pose aussi un index sur pseudo). Ta table oeuvre sera plus volumineuse de 52 * 100 000 octets, soit environ 5 Mo quand même, et en plus le SGBDR devra comparer 100 000 fois 52 octets.

Alors que dans la 2éme requete, le SBGDR va aller chercher l'IDMembre pour 'toto' (soit une page de lecture avec un bon index), puis les IDAlbums, et enfin les IDOeuvres.
Meme si le processus parait plus compliqué, avec les indexs qui vont bien, ca sera un jeu d'enfant pour le SGBDR, qui en plus travaillera sur des données 4 octets au lieu de 52 !
D'autre part il s'appuiera sur des index certainement existants, car clefs étrangère, et que si tu place alors un index sur pseudo de la table membre, ca sera un index unique, donc d'autant plus efficace...(et sur moins de données, car tu aura forcement moins de membres que d'oeuvres !) CQFD

que des avantages donc.


Pour ce qui est des notes, je persiste et signe !
même si ce ne sont pas des notes mais des sortes de "flags", comment feras-tu pour savoir quel membre a déjà voté pour quelle œuvre, si tu incrémente a chaque fois un seul et même compteur pour l'ouvre, et donc comment fera-tu pour empêcher qu'un membre puisse cliquer 10 000 fois sur j'aime ?
rajoute donc une table, en plus si plus tard tu veux permettre aux membre de pouvoir laisser des commentaires sur les oeuvres, elle sera la ;)

BBFUNK01 · Answer

Hello,

pour ce qui est des commentaires c'est prévu et j'ai donc naturellement créé une table "comments" constituée ainsi :
idCOM //l'ID du commentaire
idAL //l'ID de l'album commenté
idMembre //l'ID du membre qui fait le commentaire
COM //le commentaire
ddiCOM //la date d'ajout du commentaire

Dans mon esprit j'envisage de permettre de laisser des commentaires sur les albums, mais pas sur les œuvres, enfin un commentaire laissé sur une œuvre serait stocké sur les commentaires de l'album qui contient l'œuvre.
Donc c'est pour cela que j'ai rajouté la colonne "note" dans la table "œuvres", du fait que les notes ne seraient attribuées qu'aux œuvres et non aux albums... Après pour ce qui est de "pister" le fait que tel membre a déjà noté telle œuvre j'y songe également, donc il est probable que je revoie l'organisation de mes tables .
De toute manière je n'en suis qu'à mes balbutiements dans la manipulation des bases de données, donc long est encore le chemin pour arriver à une bonne maîtrise .

Mais encore un point sur lequel j'aimerais s.t.p. que tu éclaires ma lanterne : je reviens sur mon test raté sur la jointure entre mes 3 tables via la méthode JOIN, dans ta dernière réponse tu as donné comm exemple ce code :
SELECT Nom
FROM Oeuvre
INNER JOIN Album ON Oeuvre.IDAlbum = Album.IDAlbum
INNER JOIN Membre ON Membre.IDMembre = Album.IDMembre
WHERE Membre.pseudo = 'toto'

Si par exemple je souhaite récupérer la valeur INT maxi contenue dans la table œuvres, colonne "note", et que je souhaite récupérer aussi le titre de l'œuvre (dans œuvres), l'id de l'album (dans "album"), et le pseudo de l'artiste (dans "membres"), comment puis-je écrire cela avec JOIN ?
Moi j'essaierai un truc comme ça :
SELECT MAX(note), titre
FROM œuvres
INNER JOIN album ON œuvres.IDAlbum = album.IDAlbum
INNER JOIN membres ON membres.IDMembre = album.IDMembre


mais comment dois-je écrire ma requête pour récupérer le pseudo ? En créant des alias ?

En tout cas merci pour tes conseils ,

BBFUNK01
//C'est en forgeant qu'on devient forgeron... ;-) ;

BBFUNK01 · Answer

Re,

après relecture je confirme qu'effectivement une table supplémentaire serait la bienvenue pour la gestion des notes, après mon idée était d'afficher en 1ère page l'œuvre la mieux notée, donc il me reste à regarder comment rédiger cette requête.

A plus ,

BBFUNK01
//C'est en forgeant qu'on devient forgeron... ;-) ;

aieeeuuuuu · Answer

pour votre requete, a  vrai dire, je ne sais meme pas comment elle peut fonctionner, meme la deuxieme...

une fonction de fenetrage comme max sans clause d'agregation, c'est etrange que cela ne genera pas une erreur, mais MySQL est parfois très permissif, et a l'instar de PHP essaie de comprendre ce que le developpeur a voulu, seulement 9 fois sur 109 il va se planter.

D'ailleur en l'occurence, je pense qu'il vous renvoie la max sur tout la table, mais avec tous les enreguistrement.
Bref, je n'ai pas de MySQl sous la main pour tester, mais je pense que votre requete est bancale...

par contre pour la requete de recuperation de l'ouvre la mieux notée, il faudra connaitre le mode de calcul des notes !?
+ 1 pour j'aime et -1 pour j'aime pas ?

ou laisses-tu seulement la possibilité de cliquer sur j'aime ?

ou autre ?

BBFUNK01 · Answer

Re,

justement c'est ce à quoi je planche en ce moment : essayer de récupérer l'œuvre la mieux notée. pour ce faire j'ai créé cette table nommée "notes" :
idMembre
idœuvre
jaime
jaimepas

Bref, ce qui m'intéresse dans un 1er temps c'est de pouvoir "isoler" l'id de l'œuvre la mieux notée afin de calculer ensuite le total de "jaime", et pour le coup je rame considérablement. Ne connaissant pas encore toutes les fonctions SQL c'est laborieux , dans l'idée ce que je veux faire est assez simple : récupérer dans la table "notes" l'idœuvre qui contient le plus de "jaime", sachant que la colonne "jaime" vaut NULL ou 1. Donc existerait-il une fonction d'agrégat qui permette d'isoler l'idœuvre souhaité pour ensuite faire un SUM(jaime) ? J'ai essayé en spécifiant DISTINCT(idœuvre) puis en faisant un SUM(jaime) mais évidemment ça me renvoie le total sur toute la table... Bref ce que je n'arrive pas à faire c'est trouver la bonne "formule" pour écrire cela.

Si tu as des suggestions je suis tout ouïe  

BBFUNK01
//C'est en forgeant qu'on devient forgeron... ;-) ;

BBFUNK01 · Answer

En fait ce que j'aimerais faire ressemblerait un peu à cela :
SELECT idœuvre as œu, SUM(jaime) as tot FROM œuvres WHERE jaime = 1 and idœuvre contient le plus d'entrées
...mais en vrai SQL 

BBFUNK01
//C'est en forgeant qu'on devient forgeron... ;-) ;

aieeeuuuuu · Answer

Re,


SELECT oeuvre.IDOeuvre as oeu, count(*) AS NbJaime
FROM Oeuvre
INNER JOIN Notes ON(Oeuvre.IDOeuvre = Note.IDNote)
WHERE Note.jaime = 1
GROUP BY oeuvre.Nom
ORDER BY NbJaime DESC
LIMIT 1


l'oeuvre avec le plus de "jaime"


NB : tu peux utiliser une seule colonne de type BIT : 0 j'aime pas, 1 j'aime

Sauf si tu veux qu'un membre puisse, pour une seule et même oeuvre dire j'aime et j'aime pas (des fois que tu aurai des indécis parmi tes membres... ;) )

BBFUNK01 · Answer

Re,

merci beaucoup pour ta contribution, je ne savais pas qu'on pouvait utiliser INNER JOIN pour jointer des données au sein d'une même table, je vais tester ça. En tout cas merci infiniment pour tous tes conseils et tuyaux. Dorénavant je pense qu'il faut que je me concentre sur un apprentissage plus approfondi du langage SQL et pour ce que je multiplie les tests afin de parvenir aux résultats que je souhaite.
Encore merci pour tes éclaircissements .

Bonne soirée, et à charge de revanche ,

BBFUNK01
//C'est en forgeant qu'on devient forgeron... ;-) ;

BBFUNK01 · Answer

Arf, après avoir relu ta réponse je constate que je me suis avancé dans l'analyse de celle-ci : je n'avais pas vu qu'en fait tu faisais al jointure entre la table œuvres et la table notes... Autant pour moi . En tout cas ça me laisse entrevoir les potentielles méthodes pour "circuler" dans les tables et récupérer les infos qui m'intéressent.

Thks 

BBFUNK01
//C'est en forgeant qu'on devient forgeron... ;-) ;

aieeeuuuuu · Answer

re,

en effet il y a une petite erreur dans ma requete, j'écris une fois NoteS et une fois Note (sans le "s")

il s'agit bien d'une seule et même deuxième table, a ecrire deux fois pareil donc :)

BBFUNK01 · Answer

Hello,

merci pour ce précisions complémentaires, j'en prends bonne note pour circuler dans ma table notes .

A plus ,

BBFUNK01
//C'est en forgeant qu'on devient forgeron... ;-) ;

Méthode pour créer une BDD "efficace"

17 réponses

Votre réponse

Discussions similaires