0

I am designing a database schema to support a business case in which a user can submit a request (for him/herself or on someone else's behalf). To process and complete the request, the submitter will be prompted with questions based on their answers to prior questions. That is to say, the next question is conditional based on the current question's answer.

Each question will have an associated type, which will drive the user form for that particular question. A question of type boolean indicates Yes/No radio buttons for the answer. Questions of type multiple indicates a multiple choice answer, where users will select one of multiple radio options.

I have two questions:

  1. How can I modify my schema to "link" answers to multiple choice questions? (ie "the following answers are available for question X.")
  2. How should the answers drive the next question? (ie. "for question #1, if answer A is chosen, then GOTO question 5")

My question_relationships table will let me specify that question 1 is the parent of question 5, and question 5 is the parent of question 6. But I really need the answers to drive this logic.

question
    -id         
    -question_name
    -question_text
    -question_hint
    -question_type (boolean, multiple)  

question_relationship
    -id                 
    -fk_parent_question_id
    -fk_child_question_id

request
    -id         
    -person_id  
    -submitter_id
    -submit_date
    -status 

request_answer
    -id
    -fk_request_id
    -fk_question_id
    -answer_text
    -answer_boolean

I have seen the answers in db design - survey/quiz with questions and answers, but I believe that my scenario is a bit different.

Community
  • 1
  • 1
littleK
  • 19,521
  • 30
  • 128
  • 188
  • @littleK.I won't bother typing up a response unless you are watching this question. Yes, of course, the answers must drive the logic. We need clarification re the requirement first. 1) Is the set of answer-values to each question fixed (probably, since they are radio buttons) ? If yes, then the solution is simple 2) What is `request_answer.answer_text,` is that free text ... if so, which radio button does that apply to ? 3) What is the difference between `question_name` and `question_text` ? – PerformanceDBA Apr 18 '15 at 05:34
  • 4) `question_relationship.id` and `request_answer.id` are totally redundant, you can remove them and the indices that go with them. 5) How, exactly, does the user **identify** persons, and how do you ensure that they are unique ? 6) How does the user **identify** a request when they look one up (the `id` is internal) ? – PerformanceDBA Apr 18 '15 at 05:36
  • I added some info re different graph representations and question contexts. PS There is no redundancy in next_question. Anyway redundancy per se isn't bad. Designs trade off space vs time vs clarity etc. Note though that my answer is trying to "teach you to fish" rather than (presumptively) give you one. (Besides avoiding stinkers and red herrings.) – philipxy Oct 29 '15 at 02:21

1 Answers1

0

A table has an associated fill-in-the-(named-)blanks statement aka predicate. Rows that make it into a true statement go in the table. Rows that make it into a false statement stay out. That's how we interpret a table (base, view or query) and update a base. Each table represents an application relationship.

(So your predicate-style quote for 2 is how to give a table's meaning. Because then JOIN's meaning is the AND of argument meanings, and UNION the OR, EXCEPT is the AND NOT, etc.)

  1. How can I modify my schema to "link" answers to multiple choice questions? (ie "the following answers are available for question X.")
// question [question_id] has available answer [answer_id]
question_answers(question_id, answerid)
  1. How should the answers drive the next question? (ie. "for question #1, if answer A is chosen, then GOTO question 5")
// for question [this_id] if answer [answer_id] is chosen then go to question [next_id]
next_question(this_id, answer_id, next_id)

PS
There are many different ways of representing graphs (nodes with edges between them) via tables. Here the nodes are questions and the edges are this-next question pairs. Different tables support different kinds of graphs and different patterns of reading and update. (I chose one reflecting your application, but framed my answer to help you find your best representation via proper design yourself.)

PPS
If different user traces through questions can mean that which question follows another is context-dependent:

// in context [this_id] if answer [answer_id] is chosen then go to context[next_id]
next_context(this_id, answer_id, next_id)

What a "context" is depends on aspects of your application that you have not given. What you have given suggests that your only notion of context is the current question. Also, depending on what a context contains, this table may need normalization. You might want independent notions of current context vs current question. (Topic: finite state machines.)

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83
  • 1) Definitely not. That is not a database, it is has massive data duplication. 2) littleK's `request_answer,` alhtough not quite correct, already trumps yours. – PerformanceDBA Apr 18 '15 at 00:56