1. Blobs suck. I suggest a serialized array or JSON instead of a BLOB. 2. I have used this before and would love to know what the design pattern is called. This patterns works well, though I would not be surprised to see it called an anti-pattern. Adding fields make the normalized table grow very quickly... 3. I agree with Dan.
On Thu, Feb 28, 2008 at 5:25 AM, Dan Buettner <[EMAIL PROTECTED]> wrote: > 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] > > > > > -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
