Reply To: How do I represent this data?!

[__]

EDIT

I wrote this before I read your post above. I think it still applies to your questions, though.

, – – – – – – – – – – – – – – – – – – – –

You’ll probably need to do a lot of figuring about how the followup questions are determined. It might get fairly complex if, say, question X depends on the answers of both questions A and B, etc..

Maybe start out with something like this (MySQL):

CREATE TABLE question(
    id SERIAL PRIMARY KEY  -- primary key
   ,q VARCHAR(255)         -- text of question
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE question_opt(
    id SERIAL PRIMARY KEY          -- primary key
   ,q_id BIGINT UNSIGNED NOT NULL  -- foreign key for question
   ,o VARCHAR(255)                 -- text of option
   ,FOREIGN KEY( q_id ) REFERENCES question( id )
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE question_map(
    o_id BIGINT UNSIGNED NOT NULL  -- foreign key for option
   ,q_id BIGINT UNSIGNED NOT NULL  -- foreign key for question
   ,PRIMARY KEY( o_id,q_id )       -- primary key
   ,FOREIGN KEY( o_id ) REFERENCES question_opt( id )
   ,FOREIGN KEY( q_id ) REFERENCES question( id )
   ,INDEX( q_id )
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

Basically, we store questions in one table, options in another, and then we have a third table that ties certain questions to the answers of other questions. As a simple example:

question
+---+-----------------------------+
|id | q                           |
+===+=============================+
| 1 | What is your favorite color |
+---+-----------------------------+
| 2 | Who is your favorite Beatle |
+---+-----------------------------+
| 3 | Do you also like green      |
+---+-----------------------------+
| 4 | Is it Yoko's fault          |
+---+-----------------------------+

option
+---+------+--------+
|id | q_id | o      |
+===+======+========+
| 1 | 1    | red    |
+---+------+--------+
| 2 | 1    | blue   |
+---+------+--------+
| 3 | 1    | yellow |
+---+------+--------+
| 4 | 2    | John   |
+---+------+--------+
| 5 | 2    | Paul   |
+---+------+--------+
| 6 | 2    | George |
+---+------+--------+
| 7 | 2    | Ringo  |
+---+------+--------+
| 8 | etc ...

question_map
+------+------+
| o_id | q_id |
+======+======+
| 2    | 3    |
+------+------+
| 4    | 4    |
+------+------+

So, to start, you could select all questions that are not associated with any particular answer:

SELECT id,q 
  FROM question
  WHERE id NOT IN(
    SELECT q_id FROM question_map 
  )
;

You’d get:

1 What is your favorite color

2 Who is your favorite Beatle

Next, select the options for those questions (using the id from each row in the previous result):

SELECT id,o 
  FROM question_opt
  WHERE q_id IN(
    1,2
  )
;

You’d get options 1 through 7, and you could assign them to the questions using the question’s id.

Say the user chooses option 1 (“red”) for the first question, and option 4 (“John”) for the second. You’d use the option ids to look for more questions:

SELECT q.id,q.q
  FROM question q
  JOIN question_map m
  ON q.id = m.q_id
  WHERE m.q_id IN(
    1,4
  )
;

Because there is a question associated with option 4, you’d then ask the user:

4 Was it Yoko’s fault