[SOLVED] Can I really do this with only 2 tables

[cooldude832]

I'm trying to build some survey software (i know how to do the gd part not my issue)

The software would allow multiple surveys to be made of differing number of questions/question types

question types include(comment box (in mysql a blob/text), multiple choice(from 2-10 choices), short text answer (255 or less chracters; varchar)) Can i get away with throwing all my survey questions in the same table?  And then the bigger question is then can I have all the survey results FOR ALL SURVEYS in all in the same table (seperate from the survey questions)  My issue is that I want the user to order the questions in any order they want and it will run into an issue when i have a blob type question as the same number as a varchar one.  Is there a way to do this easily.  I know I can let the number of fields just grow, should I make a varchar and blob for each question number so my fields are

 

ID

SurveyID (linker to survey question table)

Field1varchar

Field1blob

Field2varchar

Field2blob

Field3varchar

Field3blob

Field4varchar

Field4blob

...

 

Thanks for any help

[Hypnos]

Wouldn't it be better to have a table like this:

ID

SurveyID (linker to survey question table)

FieldNumber

Fieldvarchar

Fieldblob

 

Then you aren't changing your table structure if you add more fields.

 

[cooldude832]

okay so then my queries are

SELECT Fieldvarchar, Fieldblob FROM Results WHERE SurveyID = $SurveyID ORDER BY Fieldnumber