Storing Very Granular User Preferences

Question

I've seen a few questions on here about storing user preferences, but they seem to be referring mostly to a fairly minimal set of preferences. I'm currently working on a highly customizable web app, which will need to store a great number of preferences, and I'm struggling with how to store them.

The kind of preferences I'll be storing include booleans for displaying specific tooltips, arrangement of various content panels on a page, which page to display after login, default values for specific form fields, etc. All-in-all, I'm expecting there will be 50+ preferences of this type for each user, the data being mostly booleans and integers.

I'm not a big fan of serialization, but I'm concerned about the scalability of storing each preference as an individual row. Thoughts?

Answer 1

Serializing a blob of data is the way to go here, but not for performance reasons -- rather because this is an aspect of the system that is likely to see huge numbers of changes. You don't want to have to change your DB schema just because you now need to allow a preference to turn on advanced mode on some page or something.

The entity-attribute-value model that HLGEM mentions fits this from an "easy to evolve" perspective, but as she says it would have very poor performance.

What you would give up with serialized objects would be the ability to directly query the db for users matching a certain pattern (perhaps you're tracking down a bug that would occur only with some combination of settings and you want to see if you have any users who have that combination).

Answer 2

whatever you do avoid using the entity-attrivute-value structure for this (http://en.wikipedia.org/wiki/Entity-Attribute-Value_model) unless you want a very badly performing system. A call to one table with 50 columns is going to be much faster than a call to one table that you have to join to 50 times to get all the information you need.

I'd make a related table for each general group of preferences (login preferences, overall site preferences, specific page or functions preferences) basing it on how you intend to query for preferences (if you want to pull all back on login vice pull those needed for the whole site on login and those needed only for specialized areas when the user hits those, so some combination thereof) and have the boolean columns for the type of preferences set in that. That way all the preferences you will need for each area of the site will be in the same table or at most two or three tables making it relatively easy to get the information back. This is one place where design is critical to performance (you will be looking up preferences all the time so even milliseconds count), so you really should consider performance first in your design. It is far more important here than any desire to make this appear to be object-oriented or to create less work for the developer in setting up.

Answer 3

If you dont need to search on the preferences you could always store the preferences as XML and save it to a "Preference" column. Should make adding new preferences in the future a bit easier ;)