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 :


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


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


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. Commented Aug 4, 2015 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
    Commented Aug 4, 2015 at 13:04


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.