How to store per-user parameters - opinions sought

Question

Sorry this is quite an open-ended question!

I am looking to enable users on my site to be able to modify html/css parameters for templates they use for their profiles - a bit like myspace (really, really sorry about using that as an example...).

• There is a library of "themes" that can be selected by the user • Some themes can be customised some can not • Themes do not necessarily have the same customisable parameters

I want to store the customised variables for each user, for each theme they choose to customise. What's the best format for this?

DB could have

--------------------------------------
user id | theme id | custom parameters
--------------------------------------

How could I store things like bg colour = #34ed55 bg image = bg.jpg font = comic sans color = 554323 css = h1{ font-size:1em; color:blue; } .customCss{ padding:10px; }

Answer 1

If the parameters are all of the same type (such as string values), you could have a separate parameters table for mapping key/value pairs that represent the user's theme properties.

user_theme
    user_id
    theme_id

user_theme_parameters
    user_id
    key
    value

The table could like like:

user_id  key      value
1        bgcolor  #000000
1        bgimage  bg.jpg
2        font     Arial

If themes share common keys, you might factor the parameter keys into another table and reference them here by ID instead of duplicating the key strings (such as "bgcolor").

Answer 2

You could use the following tables:

custom_param: 
    param_id
    theme_id
    param_name
user_params: 
    user_id
    param_id
    value
user_settings:
    user_id
    ...        -- whatever other user settings you store
    theme_id   -- for current theme

You get some good behavior from cascading deletes with this method. If a parameter no longer exists then you simply delete it from custom_param and viola it would deleted from the user's settings. If you want to delete an entire theme it would cascade to custom_param and delete all the relevant parameters and then all the relevant user_params.

Just make sure to set up your user_settings table so when a theme is deleted it sets the theme_id to some default theme and doesn't delete the row!

Answer 3

You could create a class to contain the custom parameters and store a serialized version of the class in the CustomParameters field in the table. In my case I extended my existing Users class to get and set values in my UserCustomParameters class.

The nice thing about this is that once it's in place I don't have to modify my DB.

Of course, you have to decide if something is important to need its own field in a table or not. For me, simple user settings, like those that control look and feel, don't need their own fields.

Properly serializing and unserializing the class was the most time-consuming part.