database design help needed!!

[shobapond]

Hi all,

I'm designing a dbase using SQL server and using VB.Net. i have a form (paper
form ) where the user is required to answer some questions and for some questions
he has to select more than one answer

e.g

What are your favorite colours?
1) checkbox1
2) checkbox2
3) checkbox3
4) checkbox4

Should i create a table only for that question and have my columns as checkbox1
to checkbox4 ? If that's the case if i have 50-100 questions similar to this
does it means i need to have 50-100 tables? i'm not really sure whether i'm
on the right path or if there are better solutions. Please let me know.

Thanks in advance for ur help..

Regards,
shobapond

[Arthur Wood]

It there are many questions, then you would have a Questions table, to hold
the specifics of the Questions: QuestionID(PrimaryKey), QuestionType, QuestionText,AnswerType
for example. Here AnswerType might indicate that the expected answer was
a SINGLE value, or could be multiple values, for example. QuestionType might
indicate Yes/No, Pick One of several, Pick Multiple, etc.

and then an Answers Table (AnswerID (PrimaryKey), PersonID, QuestionID, AnswerValue)
that way you could have 0 or more answeres (as needed) for any particular
question.

You should NEVER have a Table with AnswerID, Check1, Check2, Check3, Check4

That is a VERY poor design.

Arthur Wood


"shobapond" <shobapond@hotmail.com> wrote:
>
>Hi all,
>
>I'm designing a dbase using SQL server and using VB.Net. i have a form (paper
>form ) where the user is required to answer some questions and for some
questions
>he has to select more than one answer
>
>e.g
>
>What are your favorite colours?
>1) checkbox1
>2) checkbox2
>3) checkbox3
>4) checkbox4
>
>Should i create a table only for that question and have my columns as checkbox1
>to checkbox4 ? If that's the case if i have 50-100 questions similar to
this
>does it means i need to have 50-100 tables? i'm not really sure whether
i'm
>on the right path or if there are better solutions. Please let me know.
>
>Thanks in advance for ur help..
>
>Regards,
>shobapond

[shobapond]

Hi

Thanks for the response. I still have something which is unclear in my mind
and it would be good if you could provide me with some help.

first the reason why i'm using my table to contain check1 to check4 is that
i need the data for statistical analysis in the later part..

When you said to use an Answer table to hold the values how do u actually
hold many different values?. May be i did not make myself clear when i asked
the question i'll try to clear it up.

I have different questions

e.g

1) what are your favorite colors?
check1
check2
check3

2) what are your favorite foods?
check1
check2
check3

Each one of them can have multiple anwers.. and i need to capture all the
data.

What i was thinking is to have a table for question 1 with colum check1 check2
check3 then when the user makes their selection a under column check1 will
be 0/1 same for the rest.

I'm still not really sure whether to do it this way or your way though i
admit your way is the proper way but statistic it will be very hard in the
later part.. please help

Thanks in advance for your help.












"Arthur Wood" <wooda@nospam.com> wrote:
>
>It there are many questions, then you would have a Questions table, to hold
>the specifics of the Questions: QuestionID(PrimaryKey), QuestionType, QuestionText,AnswerType
>for example. Here AnswerType might indicate that the expected answer was
>a SINGLE value, or could be multiple values, for example. QuestionType might
>indicate Yes/No, Pick One of several, Pick Multiple, etc.
>
>and then an Answers Table (AnswerID (PrimaryKey), PersonID, QuestionID,
AnswerValue)
> that way you could have 0 or more answeres (as needed) for any particular
>question.
>
>You should NEVER have a Table with AnswerID, Check1, Check2, Check3, Check4
>
>That is a VERY poor design.
>
>Arthur Wood
>
>
>"shobapond" <shobapond@hotmail.com> wrote:
>>
>>Hi all,
>>
>>I'm designing a dbase using SQL server and using VB.Net. i have a form
(paper
>>form ) where the user is required to answer some questions and for some
>questions
>>he has to select more than one answer
>>
>>e.g
>>
>>What are your favorite colours?
>>1) checkbox1
>>2) checkbox2
>>3) checkbox3
>>4) checkbox4
>>
>>Should i create a table only for that question and have my columns as checkbox1
>>to checkbox4 ? If that's the case if i have 50-100 questions similar to
>this
>>does it means i need to have 50-100 tables? i'm not really sure whether
>i'm
>>on the right path or if there are better solutions. Please let me know.
>>
>>Thanks in advance for ur help..
>>
>>Regards,
>>shobapond
>

[Arthur Wood]

The Tables that I suggested would hold data like this:

QuestionTable
QID QType QText AType
1 1 What are your favorite colors 1
2 1 What are your fovorite foods 1


AnswerTable
AID PID QID Answer
1 1 1 1
2 1 1 2
3 1 2 2
4 1 2 3


in the above, QType might indicate that this QuestionType uses Checkboxes
and AType indicates that MultipleAnswers to the questions are allowed.

The in the AnswersTable, PID is the ID of the persson answering the question,
QID is the QuestionNumber, and Answer is the choice for that ANSWER (here
Person 1, answered Question1 with choices 1 AND 2, and answered Question2
with choice 2 AND 3)

Now you can easily determine how each question was answered with s0ome basic
SQL

Arthur Wood


"shobapond" <shobapond@hotmail.com> wrote:
>
>Hi
>
>Thanks for the response. I still have something which is unclear in my mind
>and it would be good if you could provide me with some help.
>
>first the reason why i'm using my table to contain check1 to check4 is that
>i need the data for statistical analysis in the later part..
>
>When you said to use an Answer table to hold the values how do u actually
>hold many different values?. May be i did not make myself clear when i asked
>the question i'll try to clear it up.
>
>I have different questions
>
>e.g
>
>1) what are your favorite colors?
> check1
> check2
> check3
>
>2) what are your favorite foods?
> check1
> check2
> check3
>
>Each one of them can have multiple anwers.. and i need to capture all the
>data.
>
>What i was thinking is to have a table for question 1 with colum check1
check2
>check3 then when the user makes their selection a under column check1 will
>be 0/1 same for the rest.
>
>I'm still not really sure whether to do it this way or your way though i
>admit your way is the proper way but statistic it will be very hard in the
>later part.. please help
>
>Thanks in advance for your help.
>
>
>
>
>
>
>
>
>
>
>
>
>"Arthur Wood" <wooda@nospam.com> wrote:
>>
>>It there are many questions, then you would have a Questions table, to
hold
>>the specifics of the Questions: QuestionID(PrimaryKey), QuestionType, QuestionText,AnswerType
>>for example. Here AnswerType might indicate that the expected answer was
>>a SINGLE value, or could be multiple values, for example. QuestionType
might
>>indicate Yes/No, Pick One of several, Pick Multiple, etc.
>>
>>and then an Answers Table (AnswerID (PrimaryKey), PersonID, QuestionID,
>AnswerValue)
>> that way you could have 0 or more answeres (as needed) for any particular
>>question.
>>
>>You should NEVER have a Table with AnswerID, Check1, Check2, Check3, Check4
>>
>>That is a VERY poor design.
>>
>>Arthur Wood
>>
>>
>>"shobapond" <shobapond@hotmail.com> wrote:
>>>
>>>Hi all,
>>>
>>>I'm designing a dbase using SQL server and using VB.Net. i have a form
>(paper
>>>form ) where the user is required to answer some questions and for some
>>questions
>>>he has to select more than one answer
>>>
>>>e.g
>>>
>>>What are your favorite colours?
>>>1) checkbox1
>>>2) checkbox2
>>>3) checkbox3
>>>4) checkbox4
>>>
>>>Should i create a table only for that question and have my columns as
checkbox1
>>>to checkbox4 ? If that's the case if i have 50-100 questions similar to
>>this
>>>does it means i need to have 50-100 tables? i'm not really sure whether
>>i'm
>>>on the right path or if there are better solutions. Please let me know.
>>>
>>>Thanks in advance for ur help..
>>>
>>>Regards,
>>>shobapond
>>
>

[Q*bert]

I think this is implied but just want to make sure its there...

While the answer table stores the answers that each person has selected relative
to the questions asked, you will also need an AnswerType Table that will
house the available answers for the questions listed.
So to follow with what Arthur Wood has said...

One last thing... If the datatype for the answer is not something that is
selected, your answer table will need to support a Text column for answers,
not just a Foreign key relationship to another table

AnswerTypeTable
QID AID AText
1 1 Red
1 2 Blue
1 3 Orange
1 4 Pink
2 1 Chicken
2 2 Beef
2 3 Other...
3 1 Arizona
3 2 Other...


"Arthur Wood" <wooda@nospam.com> wrote:
>
>The Tables that I suggested would hold data like this:
>
> QuestionTable
>QID QType QText AType
>1 1 What are your favorite colors 1
>2 1 What are your fovorite foods 1
>3 2 What state do you reside? 1
>
>AnswerTable
>AID PID QID Answer
>1 1 1 1
>2 1 1 2
>3 1 2 2
>4 1 2 3
>
>
>in the above, QType might indicate that this QuestionType uses Checkboxes
>and AType indicates that MultipleAnswers to the questions are allowed.
>
>The in the AnswersTable, PID is the ID of the persson answering the question,
>QID is the QuestionNumber, and Answer is the choice for that ANSWER (here
>Person 1, answered Question1 with choices 1 AND 2, and answered Question2
>with choice 2 AND 3)
>
>Now you can easily determine how each question was answered with s0ome basic
>SQL
>
>Arthur Wood
>
>
>"shobapond" <shobapond@hotmail.com> wrote:
>>
>>Hi
>>
>>Thanks for the response. I still have something which is unclear in my
mind
>>and it would be good if you could provide me with some help.
>>
>>first the reason why i'm using my table to contain check1 to check4 is
that
>>i need the data for statistical analysis in the later part..
>>
>>When you said to use an Answer table to hold the values how do u actually
>>hold many different values?. May be i did not make myself clear when i
asked
>>the question i'll try to clear it up.
>>
>>I have different questions
>>
>>e.g
>>
>>1) what are your favorite colors?
>> check1
>> check2
>> check3
>>
>>2) what are your favorite foods?
>> check1
>> check2
>> check3
>>
>>Each one of them can have multiple anwers.. and i need to capture all the
>>data.
>>
>>What i was thinking is to have a table for question 1 with colum check1
>check2
>>check3 then when the user makes their selection a under column check1 will
>>be 0/1 same for the rest.
>>
>>I'm still not really sure whether to do it this way or your way though
i
>>admit your way is the proper way but statistic it will be very hard in
the
>>later part.. please help
>>
>>Thanks in advance for your help.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>"Arthur Wood" <wooda@nospam.com> wrote:
>>>
>>>It there are many questions, then you would have a Questions table, to
>hold
>>>the specifics of the Questions: QuestionID(PrimaryKey), QuestionType,
QuestionText,AnswerType
>>>for example. Here AnswerType might indicate that the expected answer was
>>>a SINGLE value, or could be multiple values, for example. QuestionType
>might
>>>indicate Yes/No, Pick One of several, Pick Multiple, etc.
>>>
>>>and then an Answers Table (AnswerID (PrimaryKey), PersonID, QuestionID,
>>AnswerValue)
>>> that way you could have 0 or more answeres (as needed) for any particular
>>>question.
>>>
>>>You should NEVER have a Table with AnswerID, Check1, Check2, Check3, Check4
>>>
>>>That is a VERY poor design.
>>>
>>>Arthur Wood
>>>
>>>
>>>"shobapond" <shobapond@hotmail.com> wrote:
>>>>
>>>>Hi all,
>>>>
>>>>I'm designing a dbase using SQL server and using VB.Net. i have a form
>>(paper
>>>>form ) where the user is required to answer some questions and for some
>>>questions
>>>>he has to select more than one answer
>>>>
>>>>e.g
>>>>
>>>>What are your favorite colours?
>>>>1) checkbox1
>>>>2) checkbox2
>>>>3) checkbox3
>>>>4) checkbox4
>>>>
>>>>Should i create a table only for that question and have my columns as
>checkbox1
>>>>to checkbox4 ? If that's the case if i have 50-100 questions similar
to
>>>this
>>>>does it means i need to have 50-100 tables? i'm not really sure whether
>>>i'm
>>>>on the right path or if there are better solutions. Please let me know.
>>>>
>>>>Thanks in advance for ur help..
>>>>
>>>>Regards,
>>>>shobapond
>>>
>>
>

[Jason]

Hey,

You should not add checkbox1 .... checkbox4 to your table. This breaks the
first rule of database normalization which states there should not exist
repeating values. In other words, it is easier (scaleable) for a table to
grow vertically than horizontally. What if you need to add checkbox 5?

You need to add an unique identifier (Identity) for the question, questionanswer,
and user tables. Notice the linking between tables.


Demographic Table
=================
UserID INT PK
FirstName VARCHAR


Question Table (assuming checkbox)
==============
QuestionID INT PK
Question VARCHAR

MultipleAnswer Table
=====================
AnswerID INT PK
UserID INT FK
QuestionID INT FK
Value VARCHAR or INT (Depends what datatype your form is passing)


This can get more complicated as your business rules change (adding group
question functionality and question types i.e. text, dropdown, radio) or
you want to make this into a boilerplate.


Jason


"shobapond" <shobapond@hotmail.com> wrote:
>
>Hi all,
>
>I'm designing a dbase using SQL server and using VB.Net. i have a form (paper
>form ) where the user is required to answer some questions and for some
questions
>he has to select more than one answer
>
>e.g
>
>What are your favorite colours?
>1) checkbox1
>2) checkbox2
>3) checkbox3
>4) checkbox4
>
>Should i create a table only for that question and have my columns as checkbox1
>to checkbox4 ? If that's the case if i have 50-100 questions similar to
this
>does it means i need to have 50-100 tables? i'm not really sure whether
i'm
>on the right path or if there are better solutions. Please let me know.
>
>Thanks in advance for ur help..
>
>Regards,
>shobapond