Design DB structure

Question

I want to create web site with tests. Test contains different questions. It could be textbox, checkbox or radiobutton. I want to store in db questions, answers and users.

Here is my thought about tables structure.

Users
{
   Id
   Name
}
Questions
{
   Id
   Description
   Type // textbox, checkbox, etc
}
Answers
{ 
    Id
    UserId
    QuestionId
    AnswerValue
}

But how i can store information about DIFFERENT answers? I can write in AnswerValue something like "Carrot" (textbox),"1011" (checkbox),"3" (radiobutton) etc Unfortunally it's not good solution i think. What is best way to design DB structure in my case?

I've read your question 3 times and still don't understand it. — JohnOpincar, Mar 30 '11 at 18:56
@JohnOpincar sorry i'm not a native speaker :). Can you ask some additional question? — Neir0, Mar 30 '11 at 19:05
@JohnOpincar i think he is asking how to desing the db since different answers need different column types. — mcabral, Mar 30 '11 at 19:08
I thought he was putting the entire string'"Carrot" (textbox),"1011" (checkbox),"3" (radiobutton)' in the answer -- hence my confusion. — JohnOpincar, Mar 30 '11 at 20:21

skabbes · Accepted Answer · 2011-03-30T20:01:54.957

Maybe the design isn't too bad...
Since you know the QuestionId of the Answer, you can lookup which type of question it was.

For example:

SELECT 
   Q.Type, A.* FROM Question Q, Answer A
WHERE
   Q.Id = A.QuestionId

Then in your code, when you're displaying answers, you'd have both the answer value and its type, so your code could process based on Q.Type.

To address the issue of how to store answers to questions with multiple values (checkboxes or radiobuttons) you should consider storing those in another table with a one-to-many relationship.

For example, a Checkbox question can have many Items (check-able things), and a Checkbox answer can have many items (the items which were checked). In your current schema, there doesn't seem to be any way to store the descriptions of each item. So, I suggest a new tables Items and Answers_Items

Items{
    ItemId
    QuestionId   
    Description
}

Answers_Items{
    ItemId
    AnswerId
    AnswerValue
}

yes i can lookup question type, but is it good to store answer as 01010? — Neir0, Mar 30 '11 at 19:07
@Neir0, consider a one-to-many relationship with the new schema above. — skabbes, Mar 30 '11 at 20:02

score 1 · Answer 2 · answered Mar 30 '11 at 19:05

This is a rather large question, so I think I'll answer what I interpret as the core of it. It seems to me that you are looking for a way to, based on the kind of answer, determine what kind of control to display. There are two patterns that I have used with some success on this. One involves storing an AnswerType that I can generally map to a .Net type. Then, based upon that type, determine which kind of control to display (i.e. string -> TextBox, bool -> radiobutton, multiselect/array -> checkbox list, etc.). The other involves storing metadata (much like you described above).

In your case, I would explore the former solution as it is generally "easier" to implement and requires less foreknowledge and programming around metadata. An example of a set of answer types might include: String, Yes/No, Number, List. As you can see, these general types map to .Net types fairly easily. In the case of "List" you may want to add an additional table for lookups (or AllowedValues).

I hope this is what you are looking for. Otherwise, I might suggest looking into similar, open source, systems. This search might be a good start.

score 1 · Answer 3 · answered Mar 30 '11 at 19:18

You might just want to extend your Answers table a bit more with a separate value for each type of answer: TextAnswer RadioAnswer (would be an index to the selected radio button) CheckboxAnswer (the dread bitmap for checked values)

The problem with this approach is that they each have to be nullable in order to allow for another type of value being the answer to a given question...makes data integrity a bit more complicated if you want the db to enforce it.

score 1 · Answer 4 · answered Mar 30 '11 at 19:27

1

Your structure is fine to store a user's multiple answers for a single question. However, you may want to add a datetime field so you know when a particular answer was given (in relation to the other answers for the same question).

answered Mar 30 '11 at 19:27

Steven Striga

1,371
2
10
17

score 1 · Answer 5 · answered Mar 30 '11 at 20:22

I've found that using string/varchar (with base64 encoding for binary data) to be the simplest solution and I've tried/seen all the variations in a production environment. I particularly dislike having multiple columns, one for each datatype. This is a real pain in the A$$ to work with.

Design DB structure

5 Answers5