Table design performance (2000/XP)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Check out some examples:

Duane Hookom’s Library
At Your Survey
http://www.rogersaccesslibrary.com/OtherLi…sp#Hookom,Duane%5B/url%5D

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

HTH John

Reply | Quote