Best way of storing user settings in MySQL?

Question

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.

Answer 1

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.