E
Edwinah63
I have a set of market survey questions that we ask people over the
internet.
Each survey question set may have 1 or many questions depending on the
product/service about which the person was asked.
Likewise, each survey question set may have fields common to all
surveys, but also some questions specific to the survey. All surveys
have questions requiring responses of varying data types.
I have normalized the survey headers and the survey questions, but I
would like advice on the best way to design the table/s to contain the
survey answers.
eg
tblSurveys
SurveyID auto
SurveyName text
tblSurveyQuestions
SurveyQID auto
SurveyID long
QuestionNo integer
Question text
Very simple sample data for 2 survey questions (question/data type of
expected answer)
Cigarette brands
1. Date of survey? Date (hidden on webform)
2. Do you smoke? boolean (on web form we would show two radio buttons)
3. What brand do smoke? Text (on web form Drop down showing major
brands but can overwrite if brand not in list)
4 How many cigarettes do you smoke in a day? numeric value
etc etc
Pets
1. Date of survey
2. Do you have any pets? boolean
3. How many pets do you have? numeric
4. What kinds of pets do you have? Boolean (on web form this would be a
series of select boxes for the person to choose eg Dog, Cat, Fish, Bird
plus a free form box for "Other (pls specify)" )
etc etc
As I said, these are very simplistic examples, but the design principle
for the answers table should be the same.
I could design a different table for each set of answers, but we could
develop thousands of surveys and we would wind up with thousands of
tables containing the answers.
I would appreciate any advice on this matter.
Edwinah63
Please remove the "h" in Edwinah if replying by email.
internet.
Each survey question set may have 1 or many questions depending on the
product/service about which the person was asked.
Likewise, each survey question set may have fields common to all
surveys, but also some questions specific to the survey. All surveys
have questions requiring responses of varying data types.
I have normalized the survey headers and the survey questions, but I
would like advice on the best way to design the table/s to contain the
survey answers.
eg
tblSurveys
SurveyID auto
SurveyName text
tblSurveyQuestions
SurveyQID auto
SurveyID long
QuestionNo integer
Question text
Very simple sample data for 2 survey questions (question/data type of
expected answer)
Cigarette brands
1. Date of survey? Date (hidden on webform)
2. Do you smoke? boolean (on web form we would show two radio buttons)
3. What brand do smoke? Text (on web form Drop down showing major
brands but can overwrite if brand not in list)
4 How many cigarettes do you smoke in a day? numeric value
etc etc
Pets
1. Date of survey
2. Do you have any pets? boolean
3. How many pets do you have? numeric
4. What kinds of pets do you have? Boolean (on web form this would be a
series of select boxes for the person to choose eg Dog, Cat, Fish, Bird
plus a free form box for "Other (pls specify)" )
etc etc
As I said, these are very simplistic examples, but the design principle
for the answers table should be the same.
I could design a different table for each set of answers, but we could
develop thousands of surveys and we would wind up with thousands of
tables containing the answers.
I would appreciate any advice on this matter.
Edwinah63
Please remove the "h" in Edwinah if replying by email.