Salut a tous
 
Je suis face à un soucis de modélisation sur une partie d'un nouveau projet. Je vais tenter de l'exposer assez simplement :
 
Domaine d'application :

• Un utilisateur (disposant de critères précis AGE, VILLE) doit répondre à un questionnaire (listes de questions)
• On dispose d'une table dites "Master" de l'ensemble des questions selon des critères précis (AGE, VILLE)
• Quand un utilisateur ouvre son questionnaire, on lui affiche les questions liés à ses critères (AGE, VILLE)

Règles (résumé)
Quand on modifie un paramètre d'une question (son intitulé par exemple) on peut choisir :

• La modification n'impacte que la table Master de question
• La modification impacte le contenu de la table Master ainsi que tout les utilisateurs ayant cette question (selon leurs critères)
• Quand l'utilisateur a répondu a son questionnaire, si on souhaite le revoir même 1an après, on doit pouvoir le faire (et voir le contenu exact de son questionnaire)

(j’espère que c'est assez clair )
 
Je réfléchis a une manière de modéliser ca et jusqu'a présent je n'ai qu'une seule idée en tête :
 
Tables :
 
MASTER_QUESTION
USER_QUESTION
 
Quand on créer un utilisateur, a ce moment on peuple la table USER_QUESTION avec l'ensemble des questions répondant aux critères de cet utilisateur (AGE, VILLE). Et on affichera le questionnaire en se basant sur la table USER_QUESTION.
Chaque fois qu'on modifie une question sur le MASTER -> On Update la table USER_QUESTION
 
 
Soucis :
A chaque fois qu'on créer un utilisateur on duplique pas mal de données, et je trouve que ce n'est pas très élégant comme solution
 
 
J'ai tenter plein d'autres méthodes mais à chaque fois ça coince quelque part    
 
Si vous avez besoin de précisions sur le problème, n’hésitez pas  

Je ferai quelque chose comme ca:

 
Je suppose que la liste de question pour chaque type d'utilisateur (en fonctione de l'age et de la ville) est dans une table, mais si c'est pas le cas ca ne change pas grand chose.
 
MASTER a deux ID, un MasterID et un QuestionID.
Tu utilises le QuestionID et Actif=1 pour savoir quelle est la "version" de la question a utiliser pour un nouvel utilisateur.
Chaque utilisateur aura sa liste de question propre, cette fois ci avec MasterID et plus QuestionID.
QuestionID + Actif est unique.
MasterID est unique.
 
Si tu veux changer une propriete d'une question pour tout le monde, tu fais un update dans MASTER.
Si tu veux changer une propriete pour juste les nouveaux utilisateurs, tu fais un INSERT dans MASTER, tu fous Actif a 1 pour la nouvelle question et Actif a 0 pour les autres questions avec le meme QuestionID.
 
Ca devrai garder de bonne perf peut importe le nombre de questions/mise a jour/Utilisateurs.
 
Il y a d'autre moyen de le faire (avec une date par version de question) mais c'est échanger un peu d'espace pour beaucoup de complexité dans les queries.

Salut merci pour ta proposition
 
J'ai analyser ta solution et il y'a un soucis.
 
L'Update dans le Master très bien
Mais le second type d'Update (Master + tout les questionnaires courant) ne fonctionne pas. Dans ta solution ca utilisera uniquement la nouvelle version de la question pour les futurs questionnaires
 
Si je schématise le fonctionnement :
 
t0 : Utilisateur répond a son questionnaire et voit ces N questions (selon ses critères)
 
t1 : Je modifie une question dans MASTER_QUESTION et je l'applique uniquement a MASTER QUESTION
t1 : Rien ne change pour l'utilisateur qui répond a son questionnaire
t1.5 : Un nouvel utilisateur aura la question modifiée
 
t2 : Je modifie une question dans MASTER_QUESTION et je l'applique a MASTER ET aux utilisateurs
t2 : L'utilisateur voit la question modifiée
t2.5 : Un nouvel utilisateur verra la question modifiée également
...
...
t+1ans : Je veux revoir le questionnaire du fameux utilisateur, je dois pouvoir le voir exactement comme il était
 
 
Après avoir retourné le problème dans ma tête je ne vois pas d'autre solution, que de faire une copie des questions pour chaque utilisateur dans USER_QUESTION    
 
 
ps : et encore j'ai simplifié le problème, car il y'a d'autres questions plus complexes qui sont soulevés comme les cas de suppression de questions, d'insertion de questions etc...  

Il faut 2 tables, une Master, une Question.
Master alimente Question (par recopie)
 
Dans UserQuestion, y'a une clé étrangère vers User, une autre vers Question et contient un champ "réponse".  
 
Par rapports aux modifs de Master :
Cas 1 : modif de la table Master, pas de recopie dans Question
Cas 2 : modif de la table Master et recopie dans Question uniquement pour les users qui ont leur champ réponse pas renseigné.
 
La table ListeQuestion fait le lien en fait entre Ville et Master (Master ayant un champ "Actif".

 
Salut
 
- Dans ta solution, le Cas 2 n'Update les questions que pour les utilisateurs qui n'y ont pas déjà répondu.
Il faut que ce cas update pour tout les utilisateurs aillant un questionnaire en cours (peu importe qu'ils aient répondu à cette question ou pas en fait)
 
- De plus il n'y a pas vraiment de liaison entre User et Question, car cette liaison est implicite selon un ensemble de critères (un couple AGE, VILLE ou ca pourrait être un triplet AGE VILLE SEXE) etc...
 
 
Ca parait simple, mais c'est pas de la tarte

Table des questions :
 
ID_ques | Num ques | question | Date deb | date fin
 
1 | 1 | blabla |01/01/2011|01/06/2012
2|1 |blablas|01/06/2012|31/12/2099
 
Reponse
Id_res|id_que|id_user|reponse|date-res
 
 
 
Ton user peut répondre plusieurs fois a un même numero de question avec des id_ques différents ou pas et tu peux retenir sa dernière reponse s'il n'a pas repondu à la nouvelle version de la question
 
 
Quand tu modifies une question tu modifies la date fin de la version précedante

Ben, faut bien stocker la réponse de l'utilisateur à la question, non? Tu la mets où la réponse?
 
Pour ta remarque sur l'update, ben tu changes ma règle et tu mets à jour la question si toutes les réponses du questionnaire n'ont pas reçu une réponse de l'utilisateur.

Y a une table reponse dans mon exemple
 
 
Ma méthode permet de savoir s'il a déjà répondu à la question et quand. Et même l'ancienne version de la question
 
 

Mon précédent post étant en réponse à celui de massanu (qui répondait lui-même au mien)

 
Salut
 
A première vu sur ta solution il y'a un problème à l'affichage
 
- Comment déterminer quel version de la question afficher à un utilisateur ? (qu'il y ai répondu ou pas / que son questionnaire soit actif ou validé)
 
- Il n'y a pas de concept de Date dans ce questionnaire qui existera toujours mais un concept d'affichage selon critere donc en gros dans la table question il faut ajouter 3 colonnes genre (Critere1, Critere2, Critere3) selon ce triplet un utilisateur(selon ses criteres correspondant) aura ou pas une question dans son questionnaire
 

Voila une explication plus compréhensible
 

 
J'ai rien compris
 
La notion de date te permet de retenir l'état de l'information a un instant T
 
Je pense qu'il faut une table en plus questionnaire qui regroupe les questions

 
 
c'est a la fois simple et compliqué faut juste se concentrer un peu
 
Je pense que je vais pas me casser la tête et rester sur ma première idée, qui est de dupliquées les questions afin d'intervenir uniquement la ou il faut

Dans ton exemple j'ai pas compris ou tu dupliques ?

Klemix-> Je viens de repenser a ta méthode et peut etre que je vais utiliser un morceau de ta technique
 
Je vais faire un systeme de versioning de question mais pas basé sur la date.
- Chaque question aura une derniere version (dans une table T)
- Les precedentes version (dans une table THistory)
 
Et pour chaque questionnaire utilisateur je stockerais l'id de la question mais aussi son numero de version, ce qui me permettra a n'importe quel moment de taper sur la bonne version de la question
 
 

Pourquoi se limiter à 2 version et faire une table historic
 
Les dates deb - date fin s'est souvent utilisé pour gérer l'historique et tu as x version de ta question et en fonction de la date de la réponse tu connais l'état de la question

 
Il n'y aura pas que 2 versions.
 
La table history contiendras toute les versions précédentes des questions modifiées.
 
Le systeme de date ne peux pas fonctionner dans mon cas. Je n'ai pas pu delivrer l'ensemble des contraintes du projet j'ai vraiment extremement simplifié le problème

[contrepetrie]Tu as toujours le choix dans la date [/contrepetrie]

 
Ah j'avais pas pigé le truc de l'utilisateur courrant, je croyais que c'était soit la nouvelle question pour les nouveaux utilisateurs soit pour tout les autres (inclus ceux qui ont fini).
 
Ben en fait ya rien besoin de changer, ca marche quand meme, si l'update ne dois se faire que pour les nouveaux et les utilisateurs qui n'ont pas encore fini, tu fais un INSERT dans MASTER et tu modifies la liste de question des utilisateurs qui n'ont pas encore fini pour que ca pointe sur la nouvelle version de la question.
 
T'es pas non plus limité par ce scenario, tu peux en fait changer la question de qui tu veux, quand tu veux, comme tu veux.
Un nouvel INSERT dans la table MASTER ne sera pas forcement une nouvelle version pour les nouveaux utilisateurs, ca peut etre une misa a jour pour d'autres utilisateurs. Ce n'est utilise pour les nouveaux que si actif est mis a 1.

 
 
Oui tout a fait je part sur une solution comme tu l'a conseillé avec un système de révision.
 
Je vois le truc grosso modo comme ça (je préférerais stocker les versions modifiées dans une autre table plutôt que de tout garder dans la même comme tu le conseil)
 
QUESTION
ID Revision
 
QUESTION_HISTORY
ID Revision DateCreation
 
Quand un utilisateur est inscrit pour un questionnaire on peuple cette table de liaison :
 
USER_QUESTION
IdUser ID_Question Revision
 
 
Donc ca permet à un instant t d'avoir la liste des questions (dans sa bonne révision) pour un utilisateur donné
 
Par contre j'ai un (plusieurs) petit soucis avec ce système :
 
1°) Qui dis questionnaire dis traduction des questions, dis Options et traductions des Options

• Chaque question a n-traductions
• Chaque question a n-options (en gros les choix de réponses)
• Chaque option à n-traductions

Ce qui implique que les révisions s'applique également aux Options (choix de réponses...)
 
2°) Si un utilisateur a N-Questions dans son questionnaire, avec des révisions diverses et variées, prenons l'exemple :
 
USER_QUESTION
IdUser ID_Question Revision
Paul                 Q1                Rev1
Paul                 Q2                Rev7
Paul                 Q3                Rev5
Paul                 Q4                Rev1
Paul                 Q5                Rev2
 
Ca deviens assez tricky de récuperer la liste des questions, et des options dans leurs révisions respective pour l'affichage (vu qu'on ne sais pas si on doit taper dans la table QUESTION ou QUESTION_HISTORY)
 
 
Ce projet me fait transpirer...

Ben c'est pour ce qu'il ne faut pas utiliser une table history
Si il te faut de l'archivage tu peux a la limite "déplacer" les records d'une table vers l'autre pour certaine questions et tout leur users (Donc tu auras un MASTER_HISTORY et USER_HISTORY).
 
Sinon pour les traductions ca ne change rien, c'est une question comme les autres, le language importe peu.
Si tu veux lier une question originale avec les traductions tu peux faire une autre table avec MasterID_Original, MasterID_Traduction, LanguageID.
Tu fais le lien soit avec MasterID soit avec QuestionID en fonction de la flexibilité dont tu as besoin.
 
Pour les options tu fais une autre table et tu ajoutes une foreign key dans MASTER, ca te donne aussi la possiblité de changer les questions de n'importe qui, n'importe comment, tu peux aussi evidement ecrire les options dans le language que tu veux.
 
C'est pas si dur que ca, mais je suppose que quand on est coicné dedans on en voit pas forcement le bout
 
Sinon, en general, plus un design est flexible et plus il faudra bosser dur pour avoir de bonne performances. Il faudra faire attention a ca quand tu feras tes queries.

 
Hello
 
Pour la gestion des traductions, oui je vais faire le même système que d'habitude avec une table de liaison entre les différentes traductions et les questions. Evidemment quand on créer une nouvelle révision d'une question, on sauvegarde également ses traductions (afin que si un user veut revoir ses questions 1ans plus tard, il accède également aux bonnes traductions)
 
Cependant la partie que j'arrive moins à imaginer, est la 2eme.
Pour un User donné, qui dispose de questions de différentes révisions, comment pouvoir récupérer les questions sachant qu'elles seront dans des tables différentes.
 
Pour la partie perf, oui il va falloir s'appliquer sur les procédures stockées

Le truc c'est de ne depalcer que les questions qui sont liées a des utilisateurs qui ont eux aussi été archivé. Comme ca si un User est dans la table USER_Archive, sa question est forcement dans MASTER_Archive.
 
Une question ne peu etre déplacée que si les users ont tous fini d'y repondre et un user ne peut etre déplacé que si toute ses questions peuvent etre déplacée.
L'archivage est un peut plus délicat a faire (mais pas beaucoup) mais par contre les select sont plus facil.

 
 
En fait y'a pas de concept de USER Archivé donc j'ai relu ce que tu m'avais proposé plus haut et je pense que je ne vais pas faire de table d'archivage, mais une table unique contenant toute les révisions des questions, avec un flag LastVersion pour la version la plus récente
 
Ca me permettra de garder le système de révision mais également de pouvoir aller chercher les questions dans la même table
 
Je suis en train de faire le diagramme pour résumer le tout, dés que j'ai fini je le poste tu me diras ce que tu en penses
 
En tout cas merci pour ton aide précieuse, ca m'a beaucoup aidé
 

Le truc que je dis avec les dates permet de connaitre les anciennes version, la version actuelle et la version lors de la réponse

 
J'ai garder un peu ce que tu m'a proposer avec le systeme de numero de question
 
Mais le systeme de date ne me plait pas trop, donc on peux faire sans juste en gardant le couple idquestion/versiondequestion

En fait, travailler avec les date est une facon tres logique de faire les choses en théorie, mais en pratique c'est horrible a implementer.
Par exemple, pour chaque nouvel utilisateur il faut trouver les questions les plus recente, donc ca donne un index seek en plus pour chaque question.
Ca fonctionne correctement au debut mais les perfs s'effondrent assez vite des que la table grossit.

Tout a fait surtout que les calculs sur les dates c'est pas tiptop dans l'absolu, c'est assez chiant à controler
 
Tiens une quetion au passage, je suis sur le schéma de la BDD avec toute l'aide que vous m'avez donner
 
Je me pose la question suivante sur le choix des PK :
 
Question
--------------
IdQuestion | IdRevision | TextQuestion
 
Option
--------------
IdOption | TextOption
 
QuestionOption
--------------
IdOption | IdQuestion | IdRevision
 
Etc..
 
Vous voyez le probleme ? Je me ballade a chaque fois avec le couple (IdQuestion / IdRevision) ce qui represente de manière unique une version de question. Est-il préférable de créer une PK Automatique ou garder ce duo d'identifiant ? (J'ai fait des recherches à ce sujet, et le débat est très ouvert n'apportant pas vraiment de réponse sur les best practices)
 
Donc remplacer la table Question comme ceci :
 
Question
-------------
ID | IdQuestion | IdRevision | TextQuestion
 
QuestionOption
--------------
IdOption | ID
 
C'est une question assez critique car cela impact tout l'ensemble du schéma. Par exemple je bosse en parallèle sur un système de branchement logique de question. Si le User coche telle ou telle réponse d'une question (OR), ca lui affiche une question conditionnelle.  
 
 
Quand j'irais au bout de tout ca je pense que je vais écrire un article la dessus (création de questionnaire), car ca a l'air de poser beaucoup de soucis à pas mal de personne (dont moi) et y'a pas beaucoup de vrais réponses sur le web

Idéalement une PK ca doit etre le plus petit possible (vu que ca va se retrouver aussi dans tout les indexes), c'est pour ca qu'on céée souvent une pk artificielle et on ajoute une contrainte unique sur les colonnes qui composent la pk logique.
 
Tu n'as pas vraiment besoin de mettre les textes dans les pk, apres tout il est possible que tu veuilles avoir plusieurs fois la meme question, et ca ralentit tout
 
Sinon au lieu d'avoir une IdQuestion et une IdRevision tu peux utiliser une IdUnique et une IdQuestion, la combinaison des deux te donnera la revision et idUnique pourras etre ta pk (et la seule FK dans les autres tables).

 
Donc tu es plutot de l'ecole des "surrogate key" Je vais aller au plus simple, mettre un ID auto-incremental unique, et garder mes champs IdQuestion/IdRevision pour une meilleur compréhension si quelqu'un passe derrière moi