MySQL column with various types

Question

It often seems to me that I want to store data of more than one type (usually specially integer and textual) in the same column in a MySQL database. I know this is terrible, but the reason is that it happens when I keep the answers that people asked to questions in the questionnaire. Some questions require an integer answer, some need a text answer, and some may be an item selected from a list.

The approaches that I used in the past were:

• Store everything as text and convert to int (or something else) when needed later.

• There are two columns - one for text and one for int. Then you simply fill in one in each line for one answer and leave the other as zero.

• There are two tables: one for text answers and one for whole answers.

I don't like it anyway, but I feel that there should be a much better way to deal with this situation.

To make it more specific, here is an example of tables that I may have:

CREATE TABLE question ( id int(11) NOT NULL auto_increment, text VARCHAR(200) NOT NULL default '', PRIMARY KEY ('id') ) CREATE TABLE response ( id int(11) NOT NULL auto_increment, question int (11) NOT NULL, user int (11) NOT NULL, response VARCHAR(200) NOT NULL default '' ) 

or, if I went using option 2 above:

 CREATE TABLE response ( id int(11) NOT NULL auto_increment, question int (11) NOT NULL, user int (11) NOT NULL, text_response VARCHAR(200), numeric_response int(11) ) 

and if I used parameter 3, there would be a responseInteger table and a responseText table.

Is any of these correct, or am I not seeing an obvious alternative?

Answer 1

[Option 2 -] NOT the most normalized option [as @Ray requirements]. Most normalized fields should not have fields with a null value, and obviously parameter 2 requires null for each row.

At this point in your design, you should think about usage, the requests you will make, and the reports you will write. Do you want to do the math in all the numerical answers at the same time? those. WHERE numeric_response IS NOT NULL? Probably unlikely.

Most likely, there will be what the average answer is WHERE Question = 11. In these cases, you can either select the INT table or the INT column, and none of them will be easier to do than the other.

If you made two tables, you are likely to constantly combine them together for questions such as% of answers to questions, etc.

Can you see how the questions you ask so that your database answers begin to drive the design?

Answer 2

I would choose option 1. Answers are always text strings, but sometimes a text string is a representation of an integer. It is less simple to determine what restrictions, if any, should be put to answer this question. If some answer should be only a sequence of one or more digits, how do you confirm this? Most likely, the table "Questions" should contain information about possible answers, which should serve as a guide for verification.

I note that the combination of QuestionID and UserID is unique (or should be) unique (for this questionnaire). That way, you really don't need the auto-increment column in the response. You should also have a unique constraint (or primary key constraint) in QuestionID and UserID anyway (regardless of whether you keep the auto-increment column).

Answer 3

Option 2 is the correct, most normalized option.