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]

Reply via email to