Storing user information

Question

SQL

Good afternoon friends!

If immediately according to the situation, it is necessary to store information about the user in the form of a parameter-value, but it is not known in advance how much information will have to be stored. It can be either traditional name-mail-password-status, or a billion checkboxes mailing-refuse-agree and so on.

As usual, there are two options:
1) One large table with a billion columns under the name of each parameter. Of the minuses - when adding new columns with an increasing number of users, there will be friezes and in general a lot of nasty things. Of the benefits - saving and accessing such a table will be carried out quickly.
2) One table of the form id-param, another of the form param_id - user_id - value. Of the pluses - unlimited expansion without delays, of the minuses - it is difficult then to control this zoo for read-write.

If you dig into the settings, for example, twitter, there any of the blocks (profile, privacy, and so on) is knocked to save at one address, conditionally, /profile/update, where an array of values ​​​​of the form user[param_1], user[param_2] is passed and so on Further.

Actually, the question is - maybe someone knows what kind of magic can be on the server in the example about twitter, and which option can be chosen from the above two, or maybe some kind of third one, so that all this is beautiful and , the main thing, quickly worked without strong problems?

Thanks in advance and have a great Monday evening everyone!

Answer 1

3) Data that is not sorted does not change very much and is some kind of finished blocks stored in the form of Json.

Answer 2

Key-value storage and uncertainty in the description of the data structure are tasks solved within the framework of NoSQL databases. Look in their direction. Only the flexibility of storage turns into a headache with selections by key fields, since judging by the reviews, building indexes there, to put it mildly, is not for all occasions and in many cases you will have to do a complete search through the database.

Answer 3

I would suggest looking towards MongoDB

Answer 4

There is nothing “normal” in the first option. It's not an option at all.
If we really have a key-value storage, without nesting and tree structure, then the second one is quite suitable.
I don't see any problems with "read-write control". A unique key on user_id-param_id should solve all problems.
But if the system must support a hierarchy - for example, the category "hobby", which can include hundreds of different activities - then it is just the Monga proposed above that combines these two, at first glance, contradictory requirements - storage in JSON plus selections and sorting.
It is only necessary to take into account the features of this database, which are a consequence of its advantages - since the database does not have a clear structure, it is imperative to store meta-information on your own - manually maintain a list of all fields in the records.

Answer 5

MongoDB

Answer 6

MongoDB