There is an abstract user. For him, there is a questionnaire. An example of questions with the options for answers below.

1) What is your parrot's name? (single choice of several values ​​- type radio)

  1. Kesha
  2. Gesha
  3. Sasha

2) Who do you like? (Abstract string - string)

3) When do you feed a parrot? (Multiple choice from multiple values ​​- checkbox)

  1. In the morning
  2. Happy
  3. In the evening
  4. Is always

Storage of fields of the questionnaire - fields id | Question | Answer 1 | What is the name of the parrot? | serialize (array ('Kesha', 'Gesha', 'Sasha')) 2 | When do you feed a parrot? |

User selectable storage

id | user_id | field_id | value 1 | 1 | 1 | Кеша 1 | 1 | 2 | Даже не знаю чуваки.

With this storage, we are fine, we can do filtering by fields of the first type (which is important right away in MySQL).

But there is a problem with filtering a field of the third type, since several values ​​can be selected there.

An excellent solution for a checkbox would be to use a bitmask, but the problem is that we are dealing with fields of not only this type. Yes, and collect normally the bits of what we want will be problematic.

Who can push to the solution? Or submit a good idea or reference?

  • Do I understand correctly that your data is always unstructured? Those. You do not know the composition of the fields that will come in the future? And to use something like MongoDB is not possible? I am afraid in this case there is no convenient way, only to denormalize the third value and store it classically. - cheops
  • @cheops, with a checkbox - yes, it is unknown which fields can be selected. Can be selected as one item, or all. That is, you propose to isolate the table and the value field and store it in a separate table along with the id records for the profile? Unfortunately, there is only relational available ( - DarkVss
  • It is convenient to store the collection in only in some document-oriented database, in MySQL it will be convenient to work only after normalization. There are no good data types, like JSON, as in postgres here and apparently it will not appear anymore. - cheops

1 answer 1

You can store responses in a string field. By your type numbers:

  1. Item number
  2. literally entered string
  3. all selected Nos. separated by commas in ascending order

Search by type 1 and 2 is obvious - a simple string comparison. Search by 3rd type:

  • for complete coincidence - as a simple string comparison,
  • on hit of separate point - through FIND_IN_SET () .