Saving user settings in a table - how?

Question

I have settings for the user about 200 settings, including notification settings and tracking parameters from user actions on objects. The problem is how to save it to the database? Should each setting be a row or column? If colunm, then the table will have 200 colonies. If the line is about 3 columns, but 200 lines per user x, even 10 million users = not good.

So, how else can I save all these settings? NOTE. These settings are a combination of text input and FK search with other tables.

Thanks.

Answer 1

You have 200 tracking settings offering a flexible database design. Therefore, I would suggest a hybrid approach:

• Users : a table with a row for the user for properties that the user will probably always have, such as username and password. Foreign keys can also be stored in this table, but this is a heuristic, and also depends on the ratio of zero to zero or from zero to many. If the latter requires a separate table.
• Features : two options
  • a table with a row for each function, effectively a hash table, with userId, name and value columns. It may also be a place for external relations, but you cannot ensure data integrity in this setting.
  • XML, but only with a database that has functions that allow you to query data or only for data that you do not need, but they only work with your application server.

I think the big answer is that you are not going to come to one solution from your original question, but instead you need to use both options to satisfy the data.

Answer 2

Serializing data almost always turns out to be a bad idea, because in doing so you cripple dbms. All human years that have entered into the creation of effective dbms will be wasted on a serialized bucket of bits.

If you have application logic associated with each parameter, I think you should implement it as:

1 column per setting in the settings table. This makes it easy to use the capabilities of your dbms, check constraints, referential integrity, the correct data type for your values, a lot of information for the optimizer. The disadvantage is that the size of the string is increasing.

or

1 table per setting (or a group of related settings). This has all the advantages of the above, but trades in series to reduce performance when you need to get most or all of the settings at once. If the parameters are optional, this alternative will be significantly less if the actual data is sparse.

In addition, a lot of columns are often a "smell", which suggests that you didn’t normalize your data correctly, but that doesn’t have to be that way. Only you know your details.

Answer 3

I think that 200 columns is definitely not a good idea, due to the difficulty of writing stored procedures, manually viewing data, or expanding to additional settings later.

Can you try the XML for all of these 200 settings, and then you will only have 1 line per user. username and related xml settings. But again, this will limit your query capabilities, but now the databases support XML. You can specifically test XML databases.