The best way to store sample responses in multiple samples in a database

Question

I am currently working on a web application for creating / administering a survey with PHP / MySQL. I looked at several versions of the database tables, and I once again discovered that I might need to rethink the repository of a particular type of response.

Right now I have a table that looks like this:

survey_answers

id PK eid sesid intvalue Nullable charvalue Nullable 

id = unique value assigned to each row

eid = Poll question that this answer answers

sesid = polling session (time and date information) id

intvalue = Response value if it is a numeric value

charvalue = response value if it is a text representation

This allowed me to continue to use the mathematical functions of MySQL to speed up processing.

However, I found a new task: storing questions that have multiple answers. An example is:

Which of the following do you like to eat? (select all applicable)

• Culinary recipes for girls
• Bacon
• Corn
• Fat whale

Now that I want to save the result, I'm not sure of the best way to handle this. Currently, I have a table for only a few choices, which looks like this:

survey_element_options

 id PK eid value 

id = unique value associated with each row

eid = question / element that this parameter is related to

value = text value of this option

With this setting, I then save the returned multiple choice answers to "survey_answers" as comma-separated strings of the element_options row identifiers that were selected in the survey. (for example, something like "4,6,7,9"). I am wondering if this is really a better solution, or if it would be more practical to create a new table that will contain each selected answer, and then refer to a specific answer row, which, in turn, refers to the element and, ultimately, to the survey .

---

EDIT

for everyone who is interested, here is the approach I got (in the PhpMyAdmin relationship view):

And the rudimentary query for collecting counts for a multiple choice question would look like this:

 SELECT e.question AS question, eo.value AS value, COUNT(eo.value) AS count FROM survey_elements e, survey_element_options eo, survey_answer_options ao WHERE e.id = 19 AND eo.eid = e.id AND ao.oid = eo.id GROUP BY eo.value 

Answer 1

It really depends on many things.

• As a rule, keeping lists of values ​​separated by commas in the database is bad, especially if you plan to do something remotely intelligent with this data. Especially if you want to make any advanced reports on the answers.
• The best relational way of storing data is to also determine the answers in the second table, and then associate them with the users answer to the question in the third table (with several entries to the user's question or, perhaps, the question is, can the user perform several polls with the same question on it .

This may be a bit complicated as a possible scenario as a simple example:

Examples of tables:

• Users ( Username , UserID )
• Questions ( qID , QuestionsText )
• Answers ( AnswerText [in this case, the example can be reused, but it also causes an additional level of complexity], aID )
• Question_Answers ([Available answers to this question, several entries for the question] qaID , qID , aID ),
• UserQuestionAnswers ( qaID , uID )

Note. For example, not a recommendation

Answer 2

Convert the primary key to a non-unique index and add the answers for the same question under the same identifier.

For instance.

 id | eid | sesid | intval | charval 3 45 30 2 3 45 30 4 

If necessary, you can add another column for a regular unique PC.

Keep things simple. No need for a relationship here.

Answer 3

These are real horses.

You can store it as a string, separated by a comma (but then what happens when you have a literal comma in one of your answers).

You can store it as a one-to-many table, for example:

survey_element_answers

 id PK survey_answers_id FK intvalue Nullable charvalue Nullable 

And then move on to this table. If you select one answer, it will create one row in this table. If you select two answers, they will create two rows in this table, etc. Then you remove the intvalue and charvalue value from the survey_answers table.

Another option, since you already save the item parameters in your own table, is to create a many-to-many table, for example:

survey_element_answers

 id PK survey_answers_id FK survey_element_options_id FK 

Again, one row is selected for each option selected.

Another option is to save the bitmask value. This will eliminate the need for a many-to-many table.

survey_element_options

 id PK eid FK value Text optionnumber unique for each eid optionbitmask 2 ^ optionnumber 

optionnumber must be unique for each eid and increment, starting with one. If you use bigint or 31 parameters, if you use int, a limit of 63 parameters will be imposed.

And then in survey_answers

 id PK eid sesid answerbitmask bigint 

Answerbitmask is calculated by adding the entire bitmask option together, for each user-selected option. For example, if 7 were saved in Answerbitmask, then this means that the user has selected the first three options.

Connections can be made:

WHERE survey_answers.answerbitmask and survey_element_options.optionbitmask> 0

So yes, there are several options to consider.

Answer 4

If you are not using the identifier as a foreign key in another request, or if you can request results using sesid, try establishing several relationships. Otherwise, I would save the answers to several options in the form of a serialized array, such as JSON or through the php function serialize ().