creating database for a big questionaire

rosemary

Hai all,

I am a bit confused with my latest project.

In this work i have to keep the answers of a long questionaire to the database.

The questionaire contains more than 500 questions and many of them have sub-questions also.

Can u please suggests me how to create a database for this?

and if i have to create 500 or more fields in the table, is there any easy way to create it?

I am a newbie, plz help me.

Thanks in advance,
Rosemary

rulian

3 fields

Q_ID | Q_Parent | Question

Enter each question as a row in the database, with and if a question is a sub question then have it's parent id in the Q_Parent table.

Then for the answers, create another table simply with

A_ID | Q_ID | Answer

That should give you the type of redundancy you need.
So a typical result woudl be

Table `questions`

Q_ID | QP_ID | Question
   1    |  null    | Why is the sky blue
   2    |  null    | What color are oranges
   3    |   2       | How do you make orange juice
   4    |   3       | Do you put sugar in OJ? 
   5    |  null    | Is this enough questions. 

Table `answers` (lets say for multiple choice)

A_ID  | Q_ID | Answer
  1      |    1    | Because the earth has blue eyes
  2      |    1    | Because water is blue
  3      |     1   | because of the way sunlight hits our atmosphere. 
  4      |     2   |  Green 

--- OR FOR simple user answers

 A_ID | USER_ID | | Q_ID  | Answer
  1     |    2923     |      2     | Orange
  2     |   2923      |     3      | By mixing tang and water

rosemary

Thanks for your great help, Rulian.

But how can i work on different datatypes in answer?

some are multiple choice questions which can be put in varchar datatype and some needs longtext and some needs int datatype.

wat should i do?

rulian

You can either break the different datatypes into different tables, which at that point you just have to manage which questions get which table, which you can define in it's creation.

The issue that I think you'll run into with keeping them all in one table is when you query for results based on a INT value, if it's set to text then 10 will rank before 2 because it's reading it as text instead of INT.
If you can run the query for an INT value on a TEXT or VARCHAR field then you wont have an issue.

It also depends on what you will need to do with the results. If you are just recording them then you can store INT values in VARCHAR, however like I said, if you need to do any computation with it at that point it becomes a problem.
So let me know what you'll be doing with the data and maybe it can shed some light on the solution

rosemary

Thanks rulian for your reply.
INT field is not for calculation, so i will use varchar. but wat abt longtext and varchar. how can i solve tat?

rulian

http://dev.mysql.com/doc/refman/5.1/en/blob.html

if it's character sets you are worries about and you are not indexing, use blob,
use text if you are oging to sort by some form of relavance.