"User Preferences" Database Table Design

Question

I'm looking to create a table for user preferences and can't figure the best way to do it. The way that the ASP.NET does it by default seems extremely awkward, and would like to avoid that. Currently, I'm using one row per user, where I have a different column for each user preference (not normalized, I know).

So, the other idea that I had come up with was to split the Preferences themselves up into their own table, and then have a row PER preference PER user in a user preferences table; however, this would mean each preference would need to be the exact same datatype, which also doesn't sound too appealing to me.

So, my question is: What is the best/most logical way to design a database to hold user preference values?

Answer 1

Some of the ideas that I try to avoid in database work, is data duplication and unnecessary complication. You also want to avoid "insert, update, and deletion anomalies". Having said that, storing user preferences in one table with each row = one user and the columns, the different preferences that are available, makes sense.

Now if you can see these preferences being used in any other form or fashion in your database, like multiple objects (not just users) using the same preferences, then you'll want to go down your second route and reference the preferences with FK/PK pairs.

As for what you've described I see no reason why the first route won't work.

Answer 2

I usually do this:

Users table (user_id, .... etc.)
.
Options table (option_id, data_type, ... etc.)
(list of things that can be set by user)
.
Preferences table (user_id, option_id, setting)

I use the new SQLVARIANT data type for the setting field so it can be different data types and record the data type of the option as part of the option definition in the Options table for casting it back to the right type when queried.

Ron

Answer 3

Real quick, one method:

User(UserID, UserName, ...)

PreferenceDataType(PreferenceDataTypeID, PreferenceDataTypeName)

PreferenceDataValue(PreferenceDataValueID, PreferenceDataTypeID, IntValue, VarcharValue, BitValue, ...)

Preference(PreferenceID, PreferenceDataTypeID, PreferenceName, ...)

UserHasPreference(UserID, PreferenceID, PreferenceDataValueID)

Answer 4

If you store all your user preferences in a single row of a User table you will have a maintenance nightmare!

Use one row per preference, per user and store the preference value as a varchar (length 255 say, or some value large enough to meet your requirements). You will have to convert values in/out of this column obviously.

The only situation where this won't work easily is if you want to store some large binary data as a User preference, but I have not found that to be a common requirement.