Design d'une base pour un site de question/réponse (stackoverflow)

Bonjour

Je remercie par avance ceux qui auront le courage de lire ma question et d'y répondre ^^.

Je développe une application web (PHP/MySQL) de question/réponse, comme stackoverflow (je sais qu'il y a déjà des solutions existantes comme question2answer, là n'est pas la question).

Donc en gros, le point le plus important ce sont les messages qui peuvent être de 3 types :

• Question : une question peut avoir 0,N réponses
• Réponse : une réponse appartient à une question
• Commentaire : un commentaire est un message qui appartient à une question ou à une réponse

Voilà ce que ce ça donne à l'affichage :

http://finalclap.free.fr/TEMP/FORUMS...ma/preview.png

Et voici le schéma de ma base de données :
schema.sql
http://finalclap.free.fr/TEMP/FORUMS...ema/schema.png

J'ai choisi de rassembler les questions, réponses et commentaires dans une même entité : la table post (message), avec un champ type qui les différencie :
Q: Question
A: Réponse (pour answer)
C: Commentaire

Il y a aussi un système de tag pour les questions, et un système de vote qui permet aux utilisateurs de voter pour ou contre une question/réponse.

Maintenant, passons à ce qui m'amène ici :

Sur le papier ce schéma a l'air raisonnable, mais je suis déçu par les performances (pourtant je pense avoir mis des index là où il faut).

Si je veux récupérer toutes les informations d'une question, je suis obligé de faire 2 jointures : une pour joindre les réponses à la question, et une seconde pour joindre les commentaires :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT qac.*, IFNULL(SUM(v.value), 0) AS _score, u.username, u.email , NULL AS _user_vote FROM sc_post q LEFT JOIN sc_post qa ON (qa.parent_id = q.id AND qa.`type` = 'A') OR qa.id = q.id LEFT JOIN sc_post qac ON (qac.parent_id = qa.id AND qac.`type` = 'C') OR qac.id = qa.id LEFT JOIN sc_vote v ON v.post_id = qac.id LEFT JOIN sc_user u ON u.id = qac.user_id WHERE q.id = '2731' AND q.`type` = 'Q' GROUP BY qac.id ORDER BY qac.`type` ASC, IF(qac.`type` = 'C', qac.created, NULL) ASC, qac.resolved DESC, _score DESC

---

Donc je souhaite avoir votre avis, j'envisage plusieurs solutions :

1. Optimiser ma requête (mais dans ce cas, je ne vois pas comment, si vous avez des suggestions...)
2. Ajouter un champ de dénormalisation dans la table post : "question_id".
   Ce champ va contenir l'id de la question auquel appartient le message (peut importe son type), comme ça plus besoin de jointure.
   Je compte gérer la mise à jour de ce champ avec des triggers.
3. Utiliser la représentation intervallaire (MPTT) pour stocker la hiérarchie des post (Question/Réponse/Commentaire)

Qu'est-ce que vous en pensez ?

Merci

Bonjour,

J'ai une ou deux petites questions.
Quel intérêt de rassembler les questions, les réponses et le commentaire dans la même table ?

Au final, tu as une grosse table avec tout plein d'informations. Je me demande si ça ne serai pas plus simple de séparer ces 3 éléments en 3 tables, reliés entre elles par l'id de la question.

En ce qui concerne l'organisation MPTT, j'ai vu que c'était assez lourd, mais je pense que ça correspond bien à la demande (qui demande un hiérarchie).

(Et je me permet de répondre à ce post, car je suis curieux !)

Bon courage en tout cas !

Bonjour,


Votre modèle représente un sac où tout est mélangé, sans même que l’intégrité référentielle entre les types d’objets qu’il contient soit mise en oeuvre. Vous êtes pris dans les rets de la table unique obèse qui pourrait faire l’objet d’une vue qui serait la jointure des 3 tables mentionnées par Kurogane.

Je n’ai pas cherché à interpréter votre SELECT, car je n’ai pas de tube d’aspirine sous la main. Ceci dit, j’aperçois des connecteurs "OR" qui sont bien souvent l’ennemi de la performance, disqualifiant l’usage des index. Peut-être que dans votre cas je me trompe : pour avoir une explication de la médiocre performance de la requête, soumettez-la à un EXPLAIN (ou plusieurs, en la décomposant en morceaux pour voir quelle(s) partie(s) pose(nt) un problème).

Bon courage.

Merci pour vos réponses, je viens seulement de les voir (je pensais que mon topic avait coulé sans réponse).

Citation:

---

Quel intérêt de rassembler les questions, les réponses et le commentaire dans la même table ?

Au final, tu as une grosse table avec tout plein d'informations. Je me demande si ça ne serai pas plus simple de séparer ces 3 éléments en 3 tables, reliés entre elles par l'id de la question.

---

J'ai tout rassemblé dans une même table parce que ces 3 entités sont vraiment très proches, dans tous les cas il s'agit d'un message qui possède :

• Un contenu
• Une date
• Un auteur
• Appartient à une question (l'équivalent d'un topic de forum)
• Peut recevoir des votes

Mais par contre il y a quand même un certain nombre de propriétés et caractéristiques qui ne sont propres qu'aux questions :

• Une question a un titre
• On peut lui attribuer des tags

Donc au final, je crois que la meilleure solution serait, comme dit fsmrel, de créer une table pour les questions.

J'apporterai peut-être cette modification au schéma plus tard, pour le moment j'ai finalement créé un champ de dénormalisation (id de la question, pour chaque post), ça m'a permis d'accélérer considérablement les requêtes.

Je pense que le MPTT (que j'avais envisagé au départ) est une mauvaise idée car cette approche alourdit le temps d'écriture de manière linéaire (peut-être logarithmique en étant optimiste) : plus il y a de post, et plus la mise à jour de l'arbre est lente. Du coup c'est pas très "scalable".

Concernant les OR, dans mes nouvelles requêtes j'en ai beaucoup moins, je vais m'arranger pour supprimer tous ceux qui restent, je peux faire sans.