Hi. I am trying to design a database for a very simple web survey. It is one question with three mult choice answers. Users select check box, submit, and see the total per answer increase as theirs is added.

I have no problem recording the number of "votes" per possible answer - IE What is your favorite item? 1. Apple 2. Chicken 3. Soap. Data reads as - Apple = 23, Chicken = 45 , Soap = 2

The challenge is to also record the name of the survey, date of survey (hard coded), and the answer data.

I assume that one table will house: SurveyName (Primary Key), SurveyDate.


THIS IS WHERE I AM STUCK
- Linking another table using SurveyName I need to define each mult choice answer.

SurveyName, (SurveyAnswers) or Apple, Chicken, Soap

- Per each mult choice answer I need to record the number of "votes"

Apple = 23 , Chicken = 45 , Soap = 2

Growth allowance:
- The mult choices will be dynamic in the amount that could exist each day. One day could have three possible answers the next seven.

- The individual mult choice answers need to be named so I can track which answer was chosen. These names will also change per survey.

Apple = 23 , Chicken = 45 , Soap = 2

Car = 43 , Truck = 24 , Bike = 7, Train = 56 , Boat = 87

Any thoughts?
Thanks
Rich





-

Would it have to be like this or is there an easier way with less tables?

Table 1

SurveyID (KEY)
SurveyName
SurveyDate (hard coded)


Table 2 linked by SurveyID

SurveyID
Mc1 (Multiple Choice Answer 1)
Mc2
Mc3
.
.
.
Mc12 (leave room up to twelve choices)


Table 3 (linked by each Mc or multiple choice)

Mc1
Mc1Name (IE Apple)
Mc1Value (IE 23)

Table 4 through 12

Mc2
Mc2Name (IE Chicken)
Mc2Value (IE 45)





Would this be correct and efficient?
I guess I will have to develop a way to give the Mc field it's own ID. Perhaps adding the SurveyID to the MC field?

Any thoughts?
Thanks
Rich

Sorry here is a possible fix to my last question:

Table 1

SurveyID (KEY)
SurveyName
SurveyDate (hard coded)


Table 2 linked by SurveyID

SurveyID
Mc1_ID (Multiple Choice Answer 1)
Mc1_Name (IE Apple)
Mc2_ID
Mc2_Name
Mc3
.
.
.
Mc12 (leave room up to twelve choices)


Table 3 (linked by each Mc or multiple choice)

Mc1_ID
Mc1_Value (IE 23)

Table 4 through 12

Mc2_ID
Mc2_Value (IE 45)

Would this be correct and efficient?

Any thoughts?
Thanks
Rich

I figured out less tables and what I believe is the most efficient way and is normalized:


Table 1

SurveyID (KEY)
SurveyName
SurveyDate (hard coded)


Table 2 linked by SurveyID

SurveyID
Mc1_Name (IE Apple)
Mc1_Value (IE 23)
.
.
.
Mc12 (leave room up to twelve choices)


I LEFT THIS UP HERE INSTEAD OF OVERWRITING IN ORDER TO HELP SOMEONE ELSE OUT IN THE FUTURE. THIS WAY THEY CAN TRACE MY THOUGHTS. HOPE IT HELPS!!

Still..............any thoughts?
Thanks
Rich