CODEWITHSUNDEEP
mysqldatabasedatabase-designstructure
timetofly
timetofly

Reputation: 3077

What is the most efficient way to save user preferences in MySQL?

I'm wondering what best tactic is for saving a large array of user preferences for a website. Mostly things like default values, like default timezone, default text size, etc. Making a separate column for each setting doesn't sound practical. The only way I can think of currently is to add a singe column in the users table for preferences, and put all the settings in there.

For example, I could use formatting like this in one column only: timezone:GMT;text_size:12;default_value1:something;default_value2:FALSE. Then when grabbing it in PHP, I would select this column for the logged in user and do an explode on the result, setting $_SESSION['settings']['setting_key'] = "setting_value".

Is there a smarter way to do this, or am I already in the right direction? The only downside to this is not being able to search through each specific setting, of course, since everything is lumped together.

Upvotes: 1

Views: 6770

Answers (2)

No'am Newman
No'am Newman

Reputation: 6477

I would go for the 'column per preference' option; after all, your program has to store the preferences and then retrieve them, so it's not as if there will be preferences floating around which your program does not know how to handle.

I have a similar system in a database program which I wrote and maintain, in which maybe ten preferences are saved per user per screen. Storing on this basis cuts down on complexity.

How many preferences were you considering to store? I doubt that it would be more than ten.

If you are going to retrieve all the preferences in one go then store them all at once, then it is possible to use the concatened string approach, but generally I wouldn't recommend this as it makes it more difficult to access the preferences on an individual basis.

Upvotes: 2

favoretti
favoretti

Reputation: 30167

IMHO that's what serialize is just perfectly meant for.

If you want it searchable you might want to end up splitting them up. On the other hand, you might want to take a totally different approach and instead of storing in a large table with setting per column, you can create a table of a kind:

CREATE TABLE settings (user_id INT, setting VARCHAR, value VARCHAR)

and store settings like that. Then you are not limited by adding/removing columns.

Another approach (to further speed up possible settings lookups) would be to create a multiple of tables:

CREATE TABLE settings (id INT, setting VARCHAR)
CREATE TABLE settings_values (user_id INT, setting_id INT, value VARCHAR)

considering that setting_id is a foreign key to settings.id and assuming that you have some users table that has user id's where user_id is a foreign key to in both examples.

Upvotes: 4

Related Questions