CODEWITHSUNDEEP
mysql
user1082764
user1082764

Reputation: 2015

How can i store the order

I have a questionnaire table and a question table.

I need to record the order of questions in my questionnaire, it may require another table.

Example:

A questionnaire may display question 42, then 56 then 14

Another questionnaire may record question 34, 67, 56, 27

The same questions used by multiple questionnaires.

Questionnaire:

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(255)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

Question:

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(255)     | NO   | PRI | NULL    | auto_increment |
| question | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

Upvotes: 0

Views: 37

Answers (2)

gen_Eric
gen_Eric

Reputation: 227270

I suggest you create a third table to link the questions to the questionnaire. Something like this:

questionnaire_link:

questionnaire_id    question_id    sortOrder
       1                42            1
       1                56            2
       1                14            3
       2                34            1
       2                67            2
       2                56            3
       2                27            4

(make sure to add indexes to this table)

Then you can make a query like this to get all the questions:

SELECT question.name FROM question
JOIN questionnaire_link ON question.id = questionnaire_link.question_id
WHERE questionnaire_link.questionnaire_id = 1

Upvotes: 1

Mike Shi
Mike Shi

Reputation: 474

You probably want a M:M relationship table between Question and Questionnaire probably with the fk's for Question.id and Questionnaire.id and a column for the number question it was. That way you can find the questions per questionnaire id and sort by question number and join with the question table.

Upvotes: 1

Related Questions