What is the best way to store user settings in a database?

Question

Database design

A lot of settings for users are planned in the web project. Also in the future they will have to be added / removed.
Several options for storing the values ​​of these settings are considered:

1. Create two tables (provided that the table with user_id already exists):
1) option, option_id;
2) user_id, option_id, value.
2. Store everything in one additional table: user_id, all_options_format(xml|json). In this case, all settings will be in one line of xml or json format.
3. Again, an additional table but already of the form: key (user_id, option), value.

Which of these options do you think is better?

Answer 1

Store the default settings in a separate location. For users, store in json / xml form only settings that differ from the default ones. The total user settings will be obtained by overlaying the different ones with the default settings. Thus, when adding a new setting, only one config will change - the general one, and nevertheless it will be available to the user. And if the user changes such a setting, the changed value will be written to his personal config.

Answer 2

If we talk about MySQL, then in PunBB, for example, they chose to store settings directly in the users table as the fastest option: each setting has its own column.
If you often change the list of settings, then it is easier to put them in a separate table, where the user_id will be the key, and the settings themselves will be in the columns.
Then you can get the data immediately in finished form with one request, there is not much point in saving here, but you can make complex queries if something happens.
And in general, of course, it is better to use NoSQL for this.

Answer 3

Mongo or Couch (and certainly many NoSQL) are ideal for storing data that is naturally represented in the form of XML / JSON (value tree with attributes). Storing and working with trees in SQL is not sugar at all, and even when there is no rigid scheme ...

Answer 4

If stored as field=value , there is a disadvantage in handling nested properties. For example, an application has windows, and each window can have its own style applied. Naturally, everything that belongs to this window is grouped "in the form of a tree". In this case, XML is convenient. I think the disadvantage of this method is clear - you have to load the entire field, and then process it. But the advantage is that everything is nicely grouped, not difficult to process and not difficult to display in any report.

Answer 5

You are asking the wrong question. how to store - no difference, the data will not deteriorate from this. The question is what will you do with them. And here there are options:
If you plan to make selections by users by properties, for example, belonging to a group, then such properties should be explicit. Normalize or not - decide for yourself.
If the options are used in bulk, for example, when rendering a page for it, and nothing else, then it makes sense to pack the array in the form of json / xml.
I personally like the Wordpress version, where there is a separate table for named options (user, post, and what else) and it stores either a separate value or an array as separate lines, but with one name, by which you can make a selection or a serialized array - optional. More precisely, how convenient it is to use them. And if desired, all options can be mixed.