K
K
Konstantin Tolmachev2018-07-21 16:15:23
MySQL
Konstantin Tolmachev, 2018-07-21 16:15:23

A table with a JSON field or several tables for each data type, which design option to choose?

Colleagues, welcome. There is a questionnaire for an applicant entering training, in which a decent amount of data is entered (about 100-120 fields in the database), and the question arose of how best to do this whole thing in order to be able to search by data and not overload the database too much with a huge amount of them.
If you make one parameter one line in the corresponding table in the variant, then in a number of tables there can be up to 20-30 million records at the time of reception. If all this is done in the json version of the field, then the question of speed arises, and the maximum number of fields that can fit in this very field. For it may at any time be necessary to add some more fields, at the request of the law or the administration.
Who designed databases with a dynamic number of fields of different types, how did you solve this problem? And of course, if there are good articles on this topic, I will be glad to read.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
J
JhaoDa, 2018-07-21
@dark_tke

JSON can fit whatever you want to put in it, mysql doesn't care how many "fields" there are.
The size of the JSON field is determined by the max_allowed_packet variable .
Well, it’s better to use PostgreSQL, everything is better with JSON (in the JSONB version) - for example, normal indexes.

D
Dmitry Entelis, 2018-07-21
@DmitriyEntelis

Why reinvent the wheel?
There is a form on 120 fields - make 120 fields in the table.
1 questionnaire - one line.
Easy to choose, easy to filter.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question