database design help needed!!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: database design help needed!!

  1. #1
    shobapond Guest

    database design help needed!!


    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

  2. #2
    Arthur Wood Guest

    Re: database design help needed!!


    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



  3. #3
    shobapond Guest

    Re: database design help needed!!


    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

    >



  4. #4
    Arthur Wood Guest

    Re: database design help needed!!


    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

    >>

    >



  5. #5
    Q*bert Guest

    Re: database design help needed!!


    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
    >>>

    >>

    >



  6. #6
    Jason Guest

    Re: database design help needed!!


    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center