What is the best way to implement dynamic fields?

Question

MySQL

There is a task to implement the storage, output and filtering of data that is not known in advance, something like a CRM system
. For example, there is a table "accounts" with already known fields, links to directories, etc., there is also a filtering of this data and output in a tabular form, but the bottom line is that at some stage of development this data will become scarce and in order not to kick the proger and not wait - you need to give the admin the opportunity to play around with the settings and dynamically add the necessary fields to this entity (the types of which will be known in advance) indicate whether it is necessary to display in the plate this field, filter by it, whether it will be required to be filled in or not.

so here's how to store this wonderful thing in the database, at this stage it is planned to use mysql with the following table architecture:

---

data_account (id, ...) - сама таблица счетов

fields(id, type[int,string,date,directory], name, directory_name, is_required, use_on_table, use_on_filter, use_on_report, ...) - поля и типы

data_fields_map(data_type, field_id, sort, ...) - мапа связей сущности и полей в ней

data_fields_values(id, data_type, data_id, field_id, val_int, val_date, val_string, ...) - сохранка значений полей

So, how appropriate would such an approach be?
I feel there will be a lot of pitfalls in such an implementation - or maybe there are some other solutions that can add fields on the fly in the table (data_account).

Answer 1

Basically there are two options.
The first one already mentioned in the comment is from Yerlan Ibraev , EAV.
The second is the serialization of dynamic parameters. JSON, XML, etc.
In both cases, parameters can be easily transferred from dynamic storage to permanent storage as development progresses. Both options also allow multiple values ​​- including in permanent and temporary storage at the same time (which also gives the potency of one-stage prioritization).
The obvious drawback of both methods is the use of a unified data type in the table (at least string, and the data may also require binary collation) regardless of the actual type of data being stored. Another obvious drawback is the need to move integrity control to the client level.
What to choose? but it already depends on what exactly is done with the data.
In any case, you should immediately discard the idea of ​​dynamically adjusting the storage structure (changing the structure of tables, adding tables) from the client code.