How effective is this scheme of work and how can it be improved?

Question

PHP

How efficient is it to store user data in MySQL on a key-value basis?
That is, the user has a number of "characteristics" that are initially set during registration (for example, first name, last name, year of birth).

id user_id field_type field_val
1 1 name ivan
2 1 firstname ivanov
3 1 byear 1989

There is a form on the site with these and other fields.
The user fills in the fields that he wants.

1) Perhaps some of the fields that were previously filled in are now empty, and you need to either remove them from the database or reset them.
2) Perhaps some of the fields that were previously NOT filled out - and they need to be entered into the database.
3) Some of the fields have been changed and their values ​​need to be changed in the database.

What is the best algorithm for updating user data?

Answer 1

Without a doubt , EAV is a useful thing. But it is worth thinking about the advisability of translating absolutely all characteristics into EAV. On the example of the same date of birth: in EAV, attribute values ​​will be either all string or binary, how to track all those who were born in the USSR? on the fly to convert the data in DATE and then to do selection? And how to make a selection by numerical values, sorting? Still, it is worth leaving the set of basic characteristics in the usual string representation.
Updating data is generally crap.
1. Read all lines matching by user_id (save data to session)
2. Print form
3. Line by line compare data from session with data from form.
3.a. new attributes (no data in session/db) aggregate and insert in one query
3.b. zeroed attributes to aggregate and either delete or update in an empty string at will
3.c. modified line-by-line update.
Maybe you should look towards NoSQL/schemaless?
there were posts on habré, the sphinx will help you

Answer 2

It will be inconvenient that numbers (year of birth), dates (date of registration) and strings (firstname) are stored in one column, and all filtering by years will have to be done as with strings.

Answer 3

I do not think that there will be any gain in such a complication of the system. in mysql.

Answer 4

If you have a fixed number of fields, why reinvent the wheel - store everything in one table (user_id, name, birthday, field_1, ... field_N).
If fields are added (from the site admin panel, for example, if it is a site), then it is better to create a new table for each type of field (int, text, date, etc).
In Drupal, for example, in general, each new field has its own table, and then a bunch of JOINs in queries.