How to build a database architecture where users have many fields?

Question

MySQL

Hello,

The specifics of the project is such that each user has a fairly extensive profile of 50-100 questions, while some questions support one answer, and some several. It was not a big problem for me to bring all the answer options to unique integers within the framework of questions that also have unique numeric identifiers among themselves. That is, we are dealing with integers.

For a number of reasons, I am very impressed with such a data structure (here and below, option "A"):

user_id (clustered index, primary) | question_1 | question_2 | ... | question_n |

With this model, there is always one line per user. This model is very convenient for searching rows by user_id, while id are added sequentially and will not split indexes, when updating, I also control the logic so that this cannot happen. This is clear.

But, as I noted, some profile questions support multiple answers, i.e. simple arrays of multiple integer values. You can use the json or blob column data type for the columns responsible for such questions.

If you do the architecture "according to the book" (here and below option "B"):
user_id | answer_variable ,
where there will be a lot of rows for one user_id, I don't see where it would benefit at all, other than avoiding arrays.

So I think it’s theoretically faster, say, with 100,000 users:
A. It’s very fast to find the user’s row, but then there is a need to perform some additional manipulations with it, or
B. In the heap table, look for all rows for user_id, but upon completion search, we get a very nice neat list of values.

Then table "A" will have 100,000 rows and 50 columns, while table "B" will have 5,000,000 rows and 2 columns. But in the first option, I always select one row first and quickly get a selection of 50 fields.

Can you give some advice on how to optimize work with a table of type "A" in order to smooth out / minimize the costs of working with a string that has json (serialized) in addition to integer? Or even the second / your own version?

Answer 1

Everything for which there is no search is rendered in json.
If you need to search by json, for example, the guys from bar 7 field, a new table is created for this.
And from there a list is taken to search by Id from the main table

Answer 2

Mysql 5.6+ has a JSON_TABLE function that can be used to get a "neat list" from a JSON field.

Answer 3

Since a relational DBMS is used, it probably makes sense to use classic relational models: