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

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 2

I don't know if I would even store information like user preferences in a database. It seems that you will want to look at many (if not all) user preferences as soon as the user logs in; if you end up doing a bunch of db queries, your system will end up quite slow. Instead, I would recommend persisting ALL of the user's settings in a single file (or a single record somewhere), and swallowing it up wholesale and caching it as soon as the user logs in.

Answer 3

Presuming the user data is already stored in a database then I don't see any real problem storing it all in one row, especially if you may need to do queries based against the data ie. select all users with preference A OR B etc.

I would, though, load it into a class and persist that class via some sort of caching mechanism.

Answer 4

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 ;)

Answer 5

Another option you could do (if you don't need your items stored in a DB, for instance to check stats for stuff (who does what, etc)), you could just put all their settings in a cookie and store it on their machine.

of course, with cookies comes all the caveats of using cookies. but just another idea...

Answer 6

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 he 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 7

There are certain preferences the user prefers more (all animals are equal but some are more equal than others). These should be specifically optimized for retrieval and application on the interface on login. As the depth of preference goes down, they can be aggregated using whatever criteria and saved as separate columns possibly as arrays(e.g. font preference column includes font type, size and colour and refers to a font table). Also index the heavily used columns using db tooling and redesign to split aggregated columns so that you can identify which element in the aggregation is under heavy demand.

All in all user preferences tend to be very static (i.e. are like a habit), do not confuse the user data which is more highly mutable with the preferences.