The primary key sounds like the right one. You don't need an additional one for userid alone, as the primary key will serve to accelerate those queries as well.
- michael dykman On Sun, Mar 2, 2008 at 9:30 PM, Waynn Lue <[EMAIL PROTECTED]> wrote: > 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] > > -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
