Database Structure

[Idire]

If I wanted to make a dynamic database system, where the questions/answers can be created through the system.

What would be the most efficient way of designing the mysql structure?

I thought about creating an array of answers and storing that, but it wouldnt be much use for post manipulation.

So, how would I store questions / answers?

[skywalker2208]

Have one table for questions and one table for answers and link the question with the answers with the question id.

[Idire]

How would that cope with questions that had different types of answers?

answer could be:

any integer
text string
multiple choice integer/string

Should i just store the answer as TEXT?

--

These are the tables I have so far:

table: questionnaire
col1: idquestionnaire INT (PK)
col2: name VARCHAR
col3: description TEXT

table: question
col1: idquestion INT (PK)
col2: idquestionnaire INT (FK)
col3: question_text TEXT
col4: question_type VARCHAR
col5: question_options TEXT - will contain comma seperated answers if question_type is multiple choice

table: answer
col1: idanswer INT (PK)
col2: idquestion INT (FK)
col3: answer_text TEXT


Would that work?

[Idire]

also how would i ensure a user doesnt answer twice? stick a username field in the answer table? would that not generate a lot of duplicate data

[Idire]

Would this work?
http://i38.tinypic.com/1yu0sp.png