sorry for cross posting...didn't see "database concepts and design" when i posted originally, and could move it to here, so i will copy and paste it here.
________________ from MySQL forum

Dear Dr. DB,

I have a few question regarding the design of a database. I am creating a survey. Suppose my questions were:

1. Do you like this? (option buttons = yes or no)
2. Which software do you use? (checkboxes)
A. Access
B. Excel
C. Word
D. Outlook

My question is, should i create a table for each response item like:
tbl_Item1
-----------
id Answer <-- these are columns

tbl_Item2
-----------
id A B C D <-- these are colums


OR should I create one answers table like
tbl_Answers
--------------
ansID question_id Choice
1 1 Yes
2 1 No
3 2 A
4 2 B
5 2 C
6 2 D


OR am I complete igornant to the proper way of designing a database survey?

Your last design is best - i.e. all answers go in the "Answers" table. To create a table per item would be liking having a separate address book for each of your friends!

thanks tony, that's what i thought.

as a follow-up question, my survey will be over the internet. i plan to have

TABLE 1. tbl_questions (using the & to separate columns)

question_ID & question_text
---------------------------------
1 & Do you like dogs?
2 & Which pets do you have?

TABLE 2. tbl_answers

answer_ID & question_ID & answer_text
-------------------------------------------------
1 & 1 & Yes
2 & 1 & No
3 & 2 & Dog
4 & 2 & Cat
5 & 2 & Pig
*where question 2 allows multiple answers.

Suppose there were 2 survey participants (1 and 2)

TABLE 3. tbl_RESPONSEs

response_ID & question_1 & question_2
------------------------------------------------
1 & answer_ID (1) & answer_ID(1)
2 & answer_ID (2) & answer_ID(2)
* this table tracks an ananomous responder by response_ID and tracks the answer ID for each question.

does this sound like a reasonable design, or is the response table off? should i store different information in the response table?

Thanks again!

The Responses table would be more like:

responses (participant_id, question_id, answer_id);

i.e. "participant X's answer to question Y was Z"

thanks again tony

handle multiple checkbox options?

for example,

Q: Which pets do you own?
A: Dog, Cat, Pig

in the table I would have
answerID & questionID & Text
-------------------------------------
1, 1, Dog
2, 1, Cat
3, 1, Pig

but there are six combinations.
a response could be: Dog and Cat, or Dog and Pig, or all three.

the response table with (participantID, answerID, questionID) is good for exclusive choices, but what about inclusive choices?

8 combos i mean

dog cat pig
0,0,0
0,0,1
0,1,0
0,1,1
1,0,0
1,0,1
1,1,0
1,1,1

The same table design allows that, but now the unique key is all 3 columns:

tony, first of all, thanks again for your help. you are probably saying, go buy a book on db design...which i did...but i need to fill in the gaps....i have this project to do (self-initiated = not for employment, but for school project) with that said, sorry to be a pest, but...

let me see if i have this right.

1. the "choice-id" corresponds to the "answer_id" before, right?
2. assigning all three as primary key make the total combination a primary key, not any one of the fields, right?

3. is this the 'standard' design of survey items(question), responseItems(answers), and responses?

4. this will be a web-based survey. i plan to "pull" in the questoins and answers from these tables...so on the one hand i want the answers to be "text", but on the other hand, when i compile the "statistics" i want to have codes. For example, i want the web form to have "Yes" and "No", but for backend i am going to use "1" and "0". Or better yet, the participant will see "Dog", "Cat", "Pig", but i want to "code" those as "1", "2", "3". Is it best to create a separate table with the coding system and relate it to the answers, or create another field(s) in the same table?

1. Yes

2. Yes

3. I don't know - it's just what I woiuld find natural to do. You could look at this example on DatabaseAnswers.

4. A separate table.

sweet!

:-)