But if you're using a varchar for the purposes of getting a list, you might as well store it in a more compact format like int or SET. The only benefit (which shouldn't be ignored) is that you have a human-readable string to parse, right?
It seems like most people ended up with a normalized format with UserPreferences, then? Have people generally found any performance problems with that? I'd imagine it would be something like UserId, PreferenceId, PreferenceSetting Where PreferenceSetting might just be another int, that could take all possible settings of that preference. On Fri, Feb 29, 2008 at 8:41 AM, Curtis Maurand <[EMAIL PROTECTED]> wrote: > > I think that I'd set up a varchar column and store a tab separated list in > it. > > Then parse it upon retrieval. > > Curtis > > > > > Dan Buettner 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] > >> > >> > > > > > > > -- > Curtis Maurand > Head Honcho > Xyonet Hosting Services > Biddeford, ME 04005 > mailto:[EMAIL PROTECTED] > mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
