Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table design peformance (2000/XP)

    I have been given a survey sheet which needs to be turned into database. There are around 80 questions (a mixed of free text, numerical input and single choice and multiple choice options).

    All the questions would be about the same object (in this case a length of river) - there would be no child tables with a one-to-many relationships to the parent table.

    Is there any benefit in trying to split the questions into smaller tables?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table design peformance (2000/XP)

    I'd use several tables:
    - A table to store general info about the participant, date of completion etc., with primary key (say) SurveyID.
    - A table to store the answers, with fields SurveyID, QuestionID, TextAnswer and NumericAnswer (including coded single-response). Primary key is the combination of SurveyID and QuestionID.
    - A table to store the selection for multiple-choice questions, with fields SurveyID, QuestionID and OptionID. Primary key is the combination of these fields.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table design peformance (2000/XP)

    The second table you describe would still have 70 fields (i.e most of the data). Most of the fields would be defined as very short text fields, storing one or two word answers/phrases or 2 character text codes.

    I guess I was wondering whether it was worth splitting the questions into groups. I can't see what that would achieve though really, it would just be a lot more work.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table design peformance (2000/XP)

    Check out some examples:

    Duane Hookom's Library
    At Your Survey
    http://www.rogersaccesslibrary.com/OtherLi...sp#Hookom,Duane

    Sample Survey / Questionnaire Database Version: 2000 (9.0) Noah
    http://www.utteraccess.com/forums/showflat...=&Zt=c&Zs=b&Zy=

    HTH John

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table design peformance (2000/XP)

    Nope - the second table only has 4 fields, but there'd be a separate record for each question. So if the survey has 70 questions, there'd be 70 records per person completing the survey.
    If a question is free text, the text field would be filled in, and if a question is numeric or coded, the number field would be filled in.

    I have attached a sample database that only allows for coded answers, but it could be adapted to allow other types of response.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •