1

I am designing database for a question and answer projects , and I am stuck. I do not know how to do it.

Here is information:

  • There will be many users
  • There will be many questions
  • Each question has 3 options (answers) and user chooses one.
  • Each question has 3 time constraints to answer. (Time constraints will change as questions.)
  • Each time constraint has different point. (Also points will change as questions.) (For example, question 1 has 3, 10 , 40 seconds time constraints. If user answers in 3 seconds, he/she will take 30 points. If users answers in 10 seconds, he/she will take 10 points etc.)
  • Only one answer is correct, other 2 are wrong.
  • There will be score table for users.
  • User will answer once a question.

My current design can be seen here: https://s32.postimg.org/lolr4vqvp/Screen_Shot_2016_06_29_at_17_14_01.png

I want to manage everything with Core data after I want to collect all data in sqlite database. Could you help me to create database design?

closed as unclear what you're asking by mustaccio, LowlyDBA, Mr.Brownstone, McNets, Colin 't Hart Jan 12 at 10:29

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • What do you already have? Where are you stuck? – Marco Jun 29 '16 at 14:08
  • Take a look here for a similar, if perhaps more tractable, question. Could you please post DDL (CREATE TABLE blah .... ) rather than a graphic? Makes it easier to enter your schema into an RDBMS for experimenting! p.s. Welcome to the forum! – Vérace Jun 29 '16 at 14:59
  • When is your assignment due? ;) There are now 3 questions on SE all asking almost identical questions. – Sir Swears-a-lot Jun 30 '16 at 21:17
0

So, this is

  • an MCQ (Multiple Choice Questionnaire)
  • a test where users will only take it once
  • there are time constraints

I think that you've over-engineered on your first pass - you woudn't be the first! :-)

A preliminary proposal would be be as follows:

CREATE TABLE Student (Student_ID int, Name VARCHAR(25));

CREATE TABLE Question (Question_ID int, Name VARCHAR(25), 
                       Question_Text VARCHAR(64000), 
                       Answer1 VARCHAR(4096), 
                       Answer2 VARCHAR(4096), 
                       Answer3 VARCHAR(4096), 
                       Correct_Answer int,
                       Threshold_1 int,  -- time constraints per question
                       Points_T1   int,
                       Threshold_2 int,
                       Points_T2   int,
                       Threshold_3 int,
                       Points_T3   int);

The reason I put the thresholds in with the question is that you have said that they may vary with the question.

CREATE TABLE Student_Response (Student_ID int, 
                               Question_ID int, 
                               Student_Answer int,  
                               Answer_Time int, 
                               Answer_Points int);

Now, from here, SQLite doesn't support calculated/computed/generated fields, and from here nor does it support IF or CASE statements in TRIGGERs, so I think you're going to have to manage the calculation of Answer_Points in application code.

This will obviously depend on

  1. whether the answer is correct and

  2. the time taken to answer

You will also need the RI (Referential Integrity) CONSTRAINTs below:

ALTER TABLE Student_Response
  ADD CONSTRAINT fk_sr_question FOREIGN KEY (Question_ID) 
    REFERENCES Question (Question_ID);
ALTER TABLE Student_Response 
  ADD CONSTRAINT fk_sr_student FOREIGN KEY (Student_ID) 
    REFERENCES Student (Student_ID);
 ALTER TABLE Student_Response 
  ADD CONSTRAINT uq_sr_student_question 
    UNIQUE (Student_ID, Question_ID);

You also said

User will answer once a question

so, you will need this UNIQUE CONSTRAINT:

ALTER TABLE Student_Response 
  ADD CONSTRAINT uq_sr_student_question 
    UNIQUE (Student_ID, Question_ID);

.

  • I have tried codes above with sqlitebrowser on mac, There is error **near "CONSTRAINT": syntax error: ** when I execute Referential Integrity query, I have also tried with that query, 'PRAGMA foreign_keys = ON;' . There is still same error. – Ünal Öztürk Jul 1 '16 at 6:48
  • Ok I have solved it adding manually, **Each time constraint has different point. (Also points will change as questions.) (For example, question 1 has 3, 10 , 40 seconds time constraints. If user answers in 3 seconds, he/she will take 30 points. If users answers in 10 seconds, he/she will take 10 points etc.) ** . Where should put these data in table, could you please explain? :) – Ünal Öztürk Jul 1 '16 at 7:51
  • I edited the answer to change T_Constraints to thresholds - perhaps that makes more sense - the English word "threshold" is perhaps clearer? The threshols in this context are the times at which a grade changes. You said that these times may vary per question, so each question has to keep track of its own thresholds. If the points will also vary, please let me know and I will put that in also - I'm assuming a constant 30-20-10 points corresponding to threshols 1-3. – Vérace Jul 1 '16 at 12:51
  • Thanks for answer. Points will also vary as questions because some of questions will be hard and they will have different time also different points. So, points are various as questions – Ünal Öztürk Jul 1 '16 at 14:31
  • This scenario is quite easy to deal with (see edit). Each point score is uniquely associated with a particular question, so they belong in the Question table - as I've done - see the addition of Points_T1 to Points_T3. This will complicate your application logic slightly, but if you complicate your requirements, you complicate your requirement provider - "ye gets nuttin fer nuttin". :-).. (colloquial - you get nothing for nothing). Sorry, I forgot to mention that I'm using MySQL syntax, so some of the DDL/DML may not work exactly for you, but it shouldn't be too difficult to translated. – Vérace Jul 1 '16 at 17:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.