Storing information about the user

Good afternoon, friends!

If right for the situation — you must store information about the user in the form of parameter / value, but you don't know how much you have to store the information. It can be as traditional name-email-password-status, and a billion ticks mailing-refuse to agree and so on.

As usual, sees two options:
1) One large table with billions of columns under the name of each parameter. Of the minuses — when you add new columns with increasing number of users are friezes and do a lot of nasty things. Of the benefits — preservation and access in the following table, are implemented quickly.
2) One table: id-param, param_id other species — user_id — value. Of the benefits — unlimited extension without delay of the minuses — difficult then to control this zoo on read-write.

If you dig settings, such as Twitter, is there any of the blocks (profile, privacy, and so on) knocking on the preservation at the same address, conditional /profile/update which receives an array of values of the form user[param_1], user[param_2], and so on.

Actually, the question — maybe someone knows what magic may reside in the server in the example about Twitter, and what's the option, you can choose from the above two, or, perhaps, some even of the third to make it all nice and, more importantly, work quickly without severe problems?

Thanks in advance and good Monday evening to all!
October 3rd 19 at 03:29
6 answers
October 3rd 19 at 03:31
3) Data that are not made sorts, not very the same and are any finished units can be stored in Json.
And if sorting and selection happen? Suppose one of the privacy settings determines whether to send the user alerts need to select all users that have this setting is true? - gilbert.Hoeger commented on October 3rd 19 at 03:34
October 3rd 19 at 03:33
Storage in the form of key-value and uncertainty in the description of the data structure is addressed by NoSQL databases. Look in their direction. Only storage flexibility turns into a headache with selections by key fields, as judging by the reviews the building of indexes, to put in mildly, not on all occasions and in many cases will have to do an exhaustive search through the database.
October 3rd 19 at 03:35
I would suggest to look in the direction of MongoDB
October 3rd 19 at 03:37
In the first version of anything "normal" there. This is not an option.
If we do have a key-value store, without nesting and drevovidnye, that is fine second.
Problems with "control read-write" not see any. A unique key on userid-paramide should solve all the problems.

But if the system needs to support a hierarchy — for example, the category "hobby", which may be hundreds of different classes — just suggested above Monga and combines these two, at first glance, conflicting requirements — storage in JSON, plus sampling and sorting.

You should only take into account the peculiarities of this database, which is a consequence of its merits — because the base has no clear structure, it is necessary to store meta-information independently manually maintain a list of all available record fields.
October 3rd 19 at 03:39
October 3rd 19 at 03:41

Find more questions by tags Database administrationSQL