table commune VS table individuelle VS XML

Question

Bonjour à tous.

Voilà, actuellement sur un projet de site, chacun des membres peut recevoir des messages d'autres, des invitations pour devenir ami et possède un profil basique sur lequel s'affiche son nombre d'ami ainsi qu'une brève liste (incomplète mais pourrait être parcourue entièrement). 

Chaque connexion au site entraine la vérification de nouveaux messages et la visite d'un profil entraine la lecture de sa liste d'amis.

J'avais dans un premier temps opté pour des fichiers XML créés dynamiquement à l'inscription de chaque nouveau membre: 1 fichier ami et 1 fichier message par membre, que je pouvais éventuellement remplacer par 1 seul fichier XML.

Cependant, une récente question posée sur ce forum m'a plutôt orienté sur l'utilisation des bases de données en général, ces dernières étant le plus souvent plus performantes que l'utilisation de fichiers XML.

Mon soucis est qu'en calquant ma réflexion sur les fichiers XML, il me faudrait créer une nouvelle table pour chaque nouveau membre. 

L'autre possibilité étant de ne créer qu'une seule table commune à tous, mais dans ce cas là le nombre d'enregistrement pourraient vite devenir impressionnant et même atteindre potentiellement des millions d'entrée => (50 amis + 50 messages) * 100 000 membres (soyons fous et optimistes) = 10 millions d'entrées !!

De plus, en utilisant qu'une seule table commune, celle-ci serait "bombardé" de requêtes alors qu'en affectant 1 table par utilisateur, il y aurait beaucoup moins d'enregistrements et de requêtes par tables.

D'où mes questions: laquelle des 2 solutions (table commune VS table individuelle) vous semble la mieux adaptée (voire garder les fichiers XML) ?

En cas de tables individuelles, n'y a-t-il pas un nombre maximal de tables défini par base, et quelles seraient les soucis auxquels je devrais m'attendre ?

Bref, comme vous le voyez, je me pose beaucoup de questions, notamment en essayant de tenir compte des possibles évolutions du site (100 000 membres est au départ une utopie mais mieux vaux y penser avant, juste au cas où ^^)

En attendant vos avis, conseils et critiques.
Merci d'avance.



Signé Sagat

sagat06 · Answer

Re-bonjour.

J'ai réfléchis un peu plus sur mon problème, et trouver un mini-échappatoire via un bon vieux système D sorti de derrière les fagots.

Comme expliqué dans mon précédent post, une table commune pourraient potentiellement contenir beaucoup trop d'enregistrement, alors que gérer plusieurs tables individuelles poseraient d'autres problèmes (pas évident de gérer plusieurs dizaines voire centaines de milliers de tables).

J'ai donc trouver pour solution:

1- Séparer les tables messages et amis,
2- Prendre la 1ère lettre du membre et générer 26+10 tables pour les messages, de même pour les amis (26 lettres + 10 chiffres).

Je me retrouve ainsi avec 72 tables au lieu d'une, obtenant grosso modo pour un chiffre de 10 millions d'entrées potentielles une moyenne d'un peu moins de 140 000 enregistrement par table. Ce qui me paraît beaucoup plus gérable.

Voilà, j'espère toujours vos différents avis et me contenterais au pire de ma solution ici exposée. Merci encore.


Signé Sagat

neigedhiver · Answer

Salut,

Tu ne prends pas le problème dans le bon sens.

Tout d'abord, il est important de rappeler un point fondamentale en conception de base de données : une table est une entité qui ne doit JAMAIS être créé en cours d'exécution. JAMAIS
. Et encore, c'est pas écrit assez gros.

Je ne sais pas comment tu as prévu d'organiser tes données, mais voici comment ça se présente généralement (parce que c'est quasiment un cas d'école, ton problème, là) :

Une table membres :
- membre_id
- membre_pseudo
- membre_motpasse
- membre_email
- etc

membre_id DOIT ETRE un INDEX de type UNIQUE (ou PRIMARY, puisqu'un index primary est forcément unique)

Dans cette table, tu stockes toutes les données des membres, qui ont une relation 1,1, c'est à dire qu'à un membre correspond une fois l'information et que chaque membre ne peut avoir cette information qu'une seule fois (adresse email, mot de passe, pseudo, etc).
Pour les relations de type 1,n il faut une table séparée : un membre peut avoir plusieurs fois cette informations, mais chaque information ne correspond qu'à un seul membre (liste d'amis, messages privés, n° téléphones, etc).
On parlera après des relations n,n

Alors donc pour chaque membre, on peut faire correspondre plusieurs amis qui sont eux-mêmes des membres.
La table amis ressemblera à ça :
- membre_id
- ami_id

C'est tout. membre_id est la même donnée que dans la table membres. On lui fait correspondre un autre id de membre que l'on doit appeler autrement parce que ce n'est pas sur cette donnée que se fait la relation entre les deux tables. membre_id doit être un index, mais SURTOUT PAS UNIQUE. Par contre, on peut définir un index sur les deux colonnes qui lui est unique : un membre ne peut pas avoir deux fois le même ami, ça n'a pas de sens. On aura donc autant de fois le même membre_id qu'un membre aura d'amis. Si un membre a 5 amis, son membre_id apparaitra 5 fois dans la table, avec l'ami_id de ses 5 amis.

Pour les messages, c'est exactement la même chose :
- mp_id
- membre_id
- mp_destinataire
- mp_titre
- mp_texte
- mp_lu

Ici mp_id est un identifiant unique des messages => index PRIMARY + AUTOINCREMENT pour l'unicité
membre_id fait une fois de plus référence à l'index primary de la table membres, c'est pourquoi il a le même nom. Ce doit être un index, mais pas unique.
On aura autant de lignes qu'un membre aura envoyé (pas reçu !) de messages.
Pour afficher à un membre les messages qu'il a reçus, il suffit de lister ceux pour lequel son membre_id = mp_destinataire. Pour indiquer à l'expéditeur si le message a été lu, il suffit de lire mp_lu.

Terminons par les relations n,n. Par exemple, inscrivons nos membres à des évènements.
On aura une table évènements qui ressemblera à ça :
- evt_id
- evt_titre
- evt_date
- evt_description
- etc
Chaque membre peut participer à un évènement et à un évènement peuvent participer plusieurs membres.
Pour permettre la relation, il faudra une table de liaison qui aura au minimum les champs suivants :
- membre_id
- evt_id
On DOIT définir un index de type UNIQUE sur les deux champs : un membre ne peut pas participer plusieurs fois au même évènement (à moins d'avoir le don d'ubiquité, ce qu'il est raisonnable de ne pas envisager dans une bdd)
On peut même y mettre des données supplémentaires, que l'on appelle "données portées" : ce sont des données qui ne concernent pas directement l'une ou l'autre entité, mais la relation. Ce sont très souvent des dates :
- membre_id
- evt_id
- membre_evt_date_inscription

En conclusion, la table amis contiendra au maximum nb_membres² enregistrements ce qui est très raisonnable. La table messages en aura un nombre qu'il n'est pas facile de prédire. Tu peux mettre une limite sur le nombre de messages par membre, ce qui te permet d'évaluer l'espace maximal (en Mo) occupé par les messages privés.

Je pense que tu devrais te documenter un peu sur la conception de bases de données. Je ne peux que te conseiller un excellent bouquin sur MERISE (méthode française d'analyse), de Dominique Dionisi, aux éditions Eyrolles, intitulé "L'Essentiel sur Merise" (à partir de 5 € sur certains sites de vente en ligne). C'est ma bible sur le sujet, c'est extrêmement bien expliqué et surtout ça permet de se rendre compte que nombreux sont les développeurs open-source qui ne sont pas doués pour l'analyse des données. Bon il existe d'autres livres, que je n'ai pas lus, celui-ci étant une valeur sûre.

Est-ce que c'est plus clair pour toi ?

-- 
Neige

Souvent la réponse à votre question se trouve dans la doc. Commencez par là ;)

sagat06 · Answer

Merci d'avoir pris le temps  d'exposer clairement ton point de vue.

Après lecture, je ne peux que constater qu'effectivement je ne prenais pas le problème dans le bon sens puisque ma priorité était essentiellement de limiter le nombre de lignes par table.

Et si je me doutais bien que créer dynamiquement des tables n'était pas une bonne idée, j'en suis maintenant CONVAINCU ^^

En ce qui concerne ma table membres, elle est organisée comme la tienne (relation 1,1...), ce sont pour les tables amis et messages que quelques questions me viennent en tête:

1) membre_id faisant référence au membre_id de la table membres, j'en déduis que les mêmes données doivent y être stockées pour les 3 tables (ex membres n° 105238), or pour la table amis (et donc de même pour la table membres) tu écris =>

"membre_id est la même donnée que dans la table membres. On lui fait correspondre un autre id de membre que l'on doit appeler autrement parce que ce n'est pas sur cette donnée que se fait la relation entre les deux tables."

Ca me laisse plutôt perplexe...si tu pouvais m'éclairer un peu plus sur ce point ?

2) En ne connaissant que le pseudo de l'individu, toute interrogation sur les tables amis et messages impliquerait une requête vers la table membres pour obtenir membre_id, ne puis-je donc pas utiliser comme membre_id le pseudo de l'utilisateur (par défaut composé uniquement de chiffre, lettre, des 2 tirets et d'une longueur maximale de 20 caractères) ?

3) Le nombre d'enregistrement ne semblent pas du tout être ta priorité =>

"la table amis contiendra au maximum nb_membres² enregistrements ce qui est très raisonnable"

Ca me paraît plutôt énorme, encore plus pour les messages, même si pour pallier à ces soucis je compte dans un premier temps limiter le nombre d'amis (pourquoi pas 100 max), ainsi qu'effacer automatiquement certains messages lus depuis plusieurs mois (pourquoi pas au bout de 4 mois).
Mais avec ton idée on peut facilement obtenir - potentiellement - 25 millions d'enregistrements pour seulement 5000 membres...

De plus, enregistrer les messages avec comme index l'expéditeur et non le destinataire est assez troublant, bien que cela doit être étudié pour une plus grande efficacité. Il faudra vraiment que je lise le bouquin que tu me conseilles pour y voir plus clair.

4) merci d'aller plus loin que ma simple demande: je ne pense pas dans un premier temps avoir besoin de table avec des relations n,n mais je m'y intéresserais quand même.

5) Va sérieusement falloir que je bosse sur les bases de données...

Merci encore


Signé Sagat

neigedhiver · Answer

Re,

1/ Oui: dans une base de données correctement conçue, un même nom de donnée ne doit représenter qu'une seule et unique donnée. Réciproquement, une même donnée doit toujours être représentée par le même nom.
La seule exception est le cas d'une relation réflexive, comme le cas des amis, parce qu'on a sauté une étape dans la conception (plus précisément, j'ai fait au plus court).
Pour bien comprendre, il ne faut pas penser "table dans la bdd", mais "entités et relations". On fait correspondre à un membre (identifié par membre_id) un ami (identifié par ami_id). Chaque ami étant également un membre, la relation est dite réflexive.
Concrètement, on pourrait avoir un champ membre_ami_id dans la table membres, permettant la relation entre membre_id (le membre en question) et membre_ami_id (du membre ami du premier). Comme chaque membre_ami_id doit être unique pour chaque membre et qu'il fait partie de la table membres, on peut simplement utiliser membre_id pour la réflexion. Mais avec un autre nom, parce que ce n'est pas "le membre auquel on s'intéresse" à un moment donné.

2/ A priori oui. D'un point de vue conceptuel, je ne pense pas que ce soit une erreur (à confirmer). Tout dépend comment est consitué le pseudo : il faut s'assurer de l'unicité. Cependant, il est également possible de définir un index de type UNIQUE sur le pseudo du membre : l'id n'est pas le seul moyen de retrouver un membre (lors d'un login, on utilise le pseudo, ou l'identifiant de connexion, associé au mot de passe, il faut donc un index sur ces deux champs). Cependant, il est quand même plus aisé de récupérer un membre avec un identifiant numérique, je pense à des raisons de performances (forcément plus optimisé que sur du texte). Est-ce que tu dois toujours récupérer les infos du membre d'après son pseudo ? Tu n'utilises jamais l'id numérique ? C'est pourtant plus simple de faire lier deux identifiants numériques pour toutes les relations.

3/ En effet, le nombre d'enregistrements n'est pas la priorité : la raison est que pour récupérer les messages d'un membre, tu vas attaquer la table en tapant sur un index numérique... La sélection et le tris seront très optimisés. Pour les amis, la table ne contenant a priori que deux champs (tu peux rajouter des données portées, comme la date à laquelle la relation a été créée, un "niveau de confiance" du membre dans son ami, un commentaire...), tous deux étant des index, ça ne consommera pas beaucoup de mémoire et ne demandera pas beaucoup de ressources processeur au sgbdr. On peut également miser sur le fait que chaque personne ne devrait pas avoir 5000 amis... Si tu as plusieurs dizaines de milliers d'utilisateurs chacun ayant plusieurs centaines/milliers d'amis, il faudra peut-être t'orienter vers une solution telle que la mise en clusters de plusieurs serveurs de base de donnes, voire utiliser un sgbdr plus performant (postgresql voire Oracle). Les performances, ça se paie, forcément... Si ton site le justifie, il faudra que les ressources allouées soient augmentées... C'est un peu la rançon du succès : mettre en place un serveur MySQL pour un site qui commence, ça ne me choque pas. Si ton site prend de l'ampleur, ce que je te souhaite, tu devras très certainement revoir ton organisation en terme hardware...

J'ai oublié un détail, qui peut être intéressant. Sur la page de profil du membre, tu peux avoir envie d'afficher le nombre d'amis (pas forcément toute la liste systématiquement), le nombre de (nouveaux) messages, etc. Pour ce faire, tu peux stocker dans la table membres certaines "méta-données", qui sont en réalité le résultat d'une requête.
Ainsi, par exemple : membre_nb_nouveaux_mp contiendra le résultat de la requête :
SELECT COUNT(*) FROM messages WHERE mp_destinataire XX AND mp_lu 0;
Ca évite d'avoir à l'exécuter à chaque fois (parce que si tu veux afficher cette info sur chaque page pour rappeler au membre qu'il a des mp non lus, plus le nombre de membres augmente, plus ça va faire franchement lourd).
Idem pour le nombre d'amis, etc.

Pour t'assurer de la cohérence des données (que la donnée stockée correspond bien à la réalité), tu peux (et dois) mettre à jour le champ quand une opération le concernant est effectuée (soustraire 1 quand le membre lit un mp non lu). Mais tu peux aussi utiliser une tâche cron exécutée chaque nuit qui va mettre à jour les "méta-données" pour chaque membre, éventuellement laisser au membre la possibilité de cliquer sur un bouton pour que les données soient mises à jour s'il estime que c'est nécessaire (c'est un truc con qui ne devrait pas arriver, mais si pour une raison X ou Y la requête de mise à jour n'est pas exécutée alors que le mp est bien lu, ça peut servir).

4/ De rien, moi, ça me fait plaisir, et si ça peut profiter à d'autres, tant mieux.

5/ C'est LE gros point négatif des développeurs, en général. Même sur des applications qui rencontrent un grand succès, la modélisation de la base de données est souvent mal faite. Le premier exemple qui me vient en tête est la bdd de phpBB2, qui était vraiment mal foutue (je ne sais pas ce qu'il en est de phpBB3, j'ai cru voir vite fait qu'ils avaient fait des efforts).
Mon frère est Admin/Concepteur de BDD pour un éditeur de logiciels de VOD (qu'on retrouve chez certains FAI) : j'ai eu droit à pas mal d'anecdotes concernant les développeurs avec lesquels il travaille et qui ont tendance à faire un peu comme ça les arrange de leur point de vue de développeur, sans se soucier vraiment de la cohérence de la base et des conséquences sur les autres développeurs.

Voilà voilà
Bon courage !

-- 
Neige

Souvent la réponse à votre question se trouve dans la doc. Commencez par là ;)

sagat06 · Answer

Re,

je conçois facilement qu'une recherche indexée sur un nombre soit plus efficace que sur une chaîne de caractères.

Dans mon projet, le problème d'un identifiant numérique ne se pose à priori pas lorsqu'un membre se connecte au site: je le relève en contrôlant le couple pseudo/password, je peux le stocker en session puis m'en servir pour chercher les infos dans les tables amis et messages.

Mais lors d'une visite d'un profil dont l'url est du type site/profil/pseudo (url-rewriting => profil.php?id=pseudo), je prévoyais de directement aller dans les tables amis et messages avec le pseudo fourni
(en le vérifiant histoire d'éviter les injections) pour en retirer les données voulues. 

Avec un identifiant numérique et ce type d'url, je devrais d'abord le rechercher dans la table membres puis m'en servir avec les autres tables et je n'ai aucune idée des performances de l'une ou l'autre des méthodes.
En même temps, je raisonne peut être encore en prenant le problème dans le mauvais sens (le temps d'assimiler...).

De plus, jusqu'à maintenant j'utilise pour identifiant numérique un id en auto-increment dans la table membres, je suppose que non mais cela pose-t-il un soucis particulier ? Dans le sens, où les premiers membres du site auront un très simple et court identifiant (1 puis 2 puis 3...).

Sinon, tu viens de m'apprendre qu'un champs d'une table pouvait contenir une sous requête !! Ou peut-être que je m'enflamme beaucoup trop ^^
En effet, je connaissais les sous-requêtes SQL du style => 
SELECT ---- FROM ---- WHERE ---- < (SELECT ---- FROM ----)

Mais cela semble différents de ce que tu proposes.

Lors d'une création/modification de la table membres, comment est décrit le champs "membre_nb_nouveaux_mp" qui contiendrait le résultat de la requête énoncée ?

En gros, plus tu m'informes, moins j'en sais et plus je t'en demande ^^ (désolé....)

Pour finir, je te remercie encore une fois de tous tes conseils et encouragements, c'est très instructif.

Merci encore


Signé Sagat

neigedhiver · Answer

Ton URL rewriting est correct sur le plan fonctionnel. Oui, ça va marcher, mais... Tu vas perdre en performances.
Si tu veux faire un site pour quelques amis, alors pas de problème, tu t'en fous des ressources. Mais puisque tu semblais envisager les 5000 membres, alors tu dois commencer tout de suite à penser aux problèmes de perfs lors de la montée en charge.
Concrètement, prends exemple sur de nombreux sites. Rien que sur PHPCS, tiens... L'url du profil d'un membre est http://www.phpcs.com/auteur/SAGAT06/1097096.aspx
En gros, l'URL contient ton pseudo mais aussi et surtout ton user_id qui est 1097096. Et faut pas se leurrer : c'est pas ton pseudo qui est utilisé pour trouver tes infos, mais bien ton id.
Ce qui est important, pour le référencement, c'est que le pseudo apparaisse dans l'URL. Que tu t'en serves ou non. Les moteurs de recherche ont des capacités infiniment supérieures à celles de ton serveur, ils peuvent se permettre d'indexer tous les mots qu'ils trouvent... Toi, non.
Tu peux tout à fait mettre l'ID avant le pseudo, séparer les deux par un /, un tiret... Ca peut donner des liens de différentes formes :
monsite.com/membre/12345-sagat06
monsite.com/membre/12345/sagat06
Avec ou sans extension (.html ou autre). Tout est possible : les moteurs de recherche s'en sortiront très bien et c'est pas la fin du monde d'avoir l'id d'un membre dans l'url...

Et utiliser un entier autoincrement comme ID, c'est très bien.

Un champ d'une table ne peut pas contenir une sous-requête. Enfin... Il peut contenir le texte d'une requête, oui, comme du texte normal quoi.
Ce que je te proposais était de stocker dans un champ le RESULTAT d'une requête, ce qui évite de faire une requête ou une jointure supplémentaire.
Pour avoir le nombre de messages non lus, on a une requête de ce genre (rappel) :
SELECT COUNT(*) FROM messages WHERE mp_destinataire XX AND mp_lu 0;

Pour sélectionner ce nombre dans la requête qui récupère les infos du membre, ça donnerait ça :
SELECT membre_pseudo,
       membre_pseudo,
       membre_email,
       ......,
       (SELECT COUNT(*) FROM messages WHERE mp_destinataire XX AND mp_lu 0) AS nb_nonlus
FROM membres
WHERE membre_id = XX;


Si tu t'arranges pour stocker dans la table membres, dans un champ membre_mp_nonlus, par exemple, le résultat de la requête SELECT COUNT(*).... tu gagneras en perfs, puisque ce ne sera pas à recalculer à chaque page (d'autant que ce n'est pas une valeur qui change fréquemment). C'est en fait le même principe que du cache.
L'avantage, c'est que même si tu as déjà un système de messagerie en place, tu peux encore mettre ça en place, en ajoutant le champ dans la table membres (ALTER TABLE) puis en exécutant une requête de ce genre :
UPDATE membres m SET m.membre_mp_nonlus (SELECT COUNT(*) FROM messages mp WHERE m.user_id mp.mp_destinataire AND mp.mp_lu = 0);
Non testé, mais a priori, ça doit être un truc dans le genre...

[quote=sagat06]En gros, plus tu m'informes, moins j'en sais et plus je t'en demande ^^ (désolé....) /quote
Et ben tant mimeux ! C'est en se posant plein de questions qu'on parvient à avancer ;)

-- 
Neige

Souvent la réponse à votre question se trouve dans la doc. Commencez par là ;)

sagat06 · Answer

Re-bonjour,

en effet, voir l'identifiant dans l'URL est une possibilité bien plus pratique et efficace, je réfléchirai à la manière de bien présenter tout ça.

Sinon, je me doutais bien que je m'enflammais avec cette histoire de sous_requête automatique à l'intérieur d'un champs, ça me semblait assez hallucinant sur le coup ^^

Mais l'idée est très bonne, c'est le genre de système D que j'apprécie (et je regrette généralement de ne pas y penser par moi-même...). En gros, dès qu'un membre acceptera un nouvel ami, j'incrémenterai un champs nb_ami dans la table membres. 

Pour les messages, étant donnée que seul un membre pourra savoir les messages qu'il reçoit, je pensais faire une requête à sa connexion pour 
connaître le nb de message non lu et stocker ce chiffre en session (et éventuellement refaire une requête au bout d'un certain temps, 30 mn pourquoi pas). L'idée étant de faire cette requête uniquement sur la même page, quand il y revient au bout d'un certains temps et d'utiliser la session dans toutes les autres pages.

Bien sûr, s'il lit des messages, je décrémente la valeur dans la session et modifie la table messages.

Bref, ces petites séances de question-réponse m'auront vraiment fait avancer et je t'en remercie encore une fois.

Merci encore.

Signé Sagat

Table commune VS table individuelle VS XML

7 réponses

Votre réponse

Discussions similaires