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:
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
User_Settings.type specifies whether the integer or string field should be referenced.
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:
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:
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.