2

We have a database design problem of a Learning System. Please can somebody help us ?

These are the requirements :

  1. A person can register for a module which has "n" number of questions.

  2. The person gives an exercise of that module of "n" questions every day.

  3. The questions of that person should not be repeated.

  4. If the answer of the question is wrong, the person will correct it.

Now there is a issue. Consider the entries as :

RegistrationTable

RegNo StudNo ModNo QPerModule
R0    S0         1          2
R1    S0         2          2

ExcerciseTable

ExcerciseID  RegID  ExcerciseDate   
E0           R0     1/7/2015
E1           R0     2/7/2015    
E2           R1     1/7/2015
E3           R1     2/7/2015        

AttemptedQuestionsTable

QAttemptDate ExerciseID QuestionID Answer   Status
1/7          E0         1          "äbcdef" "Correct"
1/7          E0         2          "agsdgh" "Wrong"
2/7          E1         3          "äsdasd" "Correct"
2/7          E1         2          "hsdahj" "-----"
2/7          E0         2          "ajsklj" "----"
2/7          E2         2          "fhasgd" "bkhijio"

Question is :

Here in this table, the entries

2/7   E1  2  "hsdahj" "-----"
2/7   E0  2  "ajsklj" "----"

Are not valid as the Questions are getting repeated for the same person on the same date. The two exercises are given by the same person.

But the entries

2/7   E0  2  "ajsklj"  "----"
2/7   E2  2  "fhasgd"  "bkhijio"

Are allowed as the two diff exercises are given by two diff people.

What design change should be made to solve this problem ?

  • 2
    Please format your tables properly, it's not very easy to follow this way. – dezso Aug 4 '15 at 8:43
  • 1
    This is very hard to follow. It should be fairly easy to handle by having a questions table (which you don't seem to list) and a UserQuestions table that you insert into before showing the questions. If UserQuestions has a unique constraint on (UserID, QuestionID) it will be impossible to have the same question for the same user more than once. – JNK Aug 4 '15 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.