Waynn, I've used both schemes 1 and 2 as you describe, and in my experience 2 is the best way to go. It's easy to scale up as you add users and settings, and it's easy to make changes if the meaning of settings should change (i.e. you need to do a backend change to people's settings).
#1 is harder to make those kind of back end updates on, and harder for someone troubleshooting to make sense of the data. #3 may not scale well - you would end up having to track too many tables, I think. What I'm doing in my current project is using a data model that has a method for each preference setting, and returns a sensible value by default if the user has no pref set for a given lookup key; otherwise, I return what the user has set. This means adding a method every time I add a preference setting, which on the one hand means adding code - on the other hand, chances are very high that if I am adding the ability for a user to set a preference, I'm already adding code somewhere to ensure that preference has an effect. HTH, Dan On Thu, Feb 28, 2008 at 9:50 AM, Waynn Lue <[EMAIL PROTECTED]> wrote: > I'm looking for a good way to store user preferences. The most > straightforward way is just to add a column to the Users table for > each preference we're looking to store. Downside is that it requires > an ALTER TABLE which gets prohibitively expensive as it gets larger, > as it's fairly inflexible. I've come up with a few alternatives, and > I'm wondering if people have ideas or suggestions, as this has to be a > common problem. A quick Google search didn't turn up anything. > > 1. Store the preferences as a binary blob on the Users table. This > blob could be either a blob, or an integer that I use application > logic to read/write from, or I could use the SET datatype. > 2. Store the preferences in normalized form, with a new table called > UserPreferences that has UserId, Setting, Preference and we add a row > for each setting of that user. > 3. Create a separate table each time we want to add a new setting, > UserId, WhateverTheNameOfThePreferenceIs. > > Anyone have any experience with this, or better suggestions? > > Thanks, > Waynn > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
