MySQL but don't know column names

Question

I create a PHP / MySQL application and I allow users to create their own (as much as they want) profile data (i.e. they can add any amount of information to their profile with additional text fields, but there is a "CORE" set of user profile fields)

For example, they can create a new text box in the form and call it "my pet" and / or "my favorite color." We must store this data in a database and cannot explicitly create columns for each of our options, since we do not know what their additional information is before.

One way to think that we can store all the “extra information” they provide is to store the extra information in JSON form and store it in the MySQL text box (I like MySQL :))

I saw plugins for creating Wordpress forms where you can create your own fields, so I think they should store data in MySQL anyway, as NoSQL solutions go beyond the scope of these plugins.

I would like to stick with MySQL, but you guys think that NoSQL solutions like MongoDB / Redis will be better fixed, because for that?

thanks

Answer 1

One way to get close to this is to use a single table using the EAV paradigm or Entity-Attribute-Value. See Wikipedia article . In most cases, this would be much more accurate than allowing users to select a database schema.

Answer 2

You can create a table of pairs of key values ​​in which nothing will be stored in the kernel. The table will look like this: user_id, name_of_user_specified_field, user_specified_value;

Any name_of_user_specified_field that starts showing a lot, which you could then add to the main table. This is called an attribute attribute. Please note that some people consider this an anti-pattern.

If you do, add controls to limit the number of new entries a user can create, or you can find someone filling your db with a lot of fields :)

Answer 3

MySQL can handle this just fine. If additional data is always collected together (i.e. you no longer need to receive only a pet field without any other additional fields), you can save it in a serialized column of the user table. However, if you need a more relational model, you can save additional data in a separate table associated with the user ID. In the additional table there will be a column for the user ID, additional field and additional field value and all that you may need. Then you just run the JOIN request when you get the profile to get all the extra fields.