purpletree
purpletree

Reputation: 1943

Best way of storing user settings in MySQL?

I was just wondering what would be the best way of storing user-specific settings for my web applications? Just preferences users may have. I've thought of two options:

  1. Users table - I'll have a table for my users. Creating a column called "preferences" and storing serialized data there in key => value pairs

  2. Settings table - Have a separate table called settings with a user_id column. Save the settings in the same way

Any input would be appreciated. Thanks :)!

--

EDIT: Just to add, if I didn't serialize/json or whatever the data to put in the data, I'd have to have a column for each setting.

Upvotes: 18

Views: 18312

Answers (6)

Sammitch
Sammitch

Reputation: 32232

For anything that is always set for every user you should tend to keep that in the Users table, per usual normalization. As for optional config I tend to like the following table structure:

TABLE Users:
  id INT AI
  name VARCHAR
  ...

TABLE User_Settings
  user_id INT PK,FK
  name VARCHAR PK
  type BOOL
  value_int INT NULL
  value_str VARCHAR NULL

Where User_Settings.type specifies whether the integer or string field should be referenced.

ie:

INSERT INTO Users (id, name) VALUES (1, 'Sammitch');
INSERT INTO User_Settings (user_id, name, type, value_int) VALUES (1, 'level', 1, 75);
INSERT INTO User_Settings (user_id, name, type, value_str) VALUES (1, 'lang', 0, 'en');

And for the INSERT/UPDATE issue:

INSERT INTO User_Settings (user_id, name, type, value_str) VALUES (1, 'lang', 0, 'fr')
  ON DUPLICATE KEY UPDATE value_str='fr';

Also, as most other people are saying, serializing and storing the preferences is not a particularly good idea because:

  1. You can't retrieve a single value with a query, you must retrieve the entire serialized string, de-serialize it, and discard the unnecessary data.
  2. It's easily corruptable, and difficult to recover from.
  3. It's a pain in the booty to write a raw query for, ie: to globally fix a certain setting.
  4. You're storing what is essentially tabular data within a single table field.

Sept 2016 Retrospective Edit:

In the intervening time I've had a few arguments with people about how best to store optional settings, as well as the general table structure defined above.

While that table structure isn't outright bad, it's not exactly good either. It's trying to make the best of a bad situation. Serialization of optional settings can work so long as you can accommodate for these settings:

  1. All being loaded at once, no picking or choosing.
  2. Not being indexable, searchable, or easily modified en masse.

Then you might consider adding a field like optional_settings in the Users table containing a serialized [eg: JSON] form of the settings. You do trade off the above, but it's a more straightforward approach and you can store more complex settings.

Also, if you use a LOB type like TEXT for storage the data is not necessarily stored "in the row" at least in MySQL.

Anyhow, it's up to you to determine what your application's requirements and constraints are, and make the best choice based on that information.

Upvotes: 29

Matt
Matt

Reputation: 2070

This may not be appropriate for your current situation, but NoSQL databases (like Mongo) are great for this. This a mongo database for instance, each user object could actually have different properties, and you could still search on them.

That said, for the situation you're in, in the past I've followed what other answers here are suggesting, and had a separate table. This can be done in two ways, have a separate table you're more comfortable modifying columns in, and join against it. OR, a simpler solution, having a separate table such as,

User, Parameter, Value.

Then each user could have different extra values.

The best approach depends on your required performance, etc..

Upvotes: 0

Matthew
Matthew

Reputation: 25753

It all depends on the settings and schema of your database. Whatever you do I would recommend against serializing data as per your suggestion 1, if you serialize the data you can no longer write queries against it, and you would need to deserialize it using the same language you serialized it with (or write something comparable in that language).

The option that I would recommend is putting the settings within the users table, 1 setting per column. The downside to this is when you add a new setting to the application, you will have to write some DDL script to add the new column. One (very good) advantage to this is each setting can have its own data-type. This will also have a storage penalty if this setting is optional.

Another option could be using a settings table, as you suggested, with primary key of (user_id, setting_name) with a third column of setting value. This solution assumes all settings are of the same data-type. You do not need to write a DDL script to add a new setting, simply use a new key name.

Upvotes: 2

Cynical
Cynical

Reputation: 9568

It's always the same problem: how often do you need to change those data? In my opinion having a separate table is always a good solution, since it separated different aspects of your application.

Having a preferencescolumns it's not a good idea, IMHO, because whenever your user changes its settings, you would have to serialize all his data to store them in the users table, whether it's just an update with a separate one. If you really want to store everything in users, you should divide your settings in different columns.

Upvotes: 1

Expedito
Expedito

Reputation: 7795

I would go with the settings table, because it will make it easier to access later. When you try to do things like making name value pairs in a single column, you might run into problems later trying do design queries to access the data.

Upvotes: 0

Sorin Trimbitas
Sorin Trimbitas

Reputation: 1497

Second option but modified with storing settings separately. Don't store serialized data in a field as in this case is bad practice.

Think of this : you have it like that and you store there the country of the user or if it is registered to your newsletter. Later on .. you need to know how many people are from Russia. What do you do? Take them one by one and decode them and increase a counter? (or using your second method and just run a simple SELECT COUNT(id) query?)

Upvotes: 0

Related Questions