Whoops, finished my thought too early. I was going to append, should we also have UserId, PreferenceId be the PRIMARY KEY? Then possibly a secondary index on UserId in case I ever want to get all preferences for a user?
On Sun, Mar 2, 2008 at 6:18 PM, Waynn Lue <[EMAIL PROTECTED]> wrote: > 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]
