On Mon, 2005-05-02 at 09:34 +0200, Arvinn L?kebakken wrote: > > Gerald V. Livingston II wrote: > > >OK, this is probably just an over-cautious MySQL question. > > > >All of the examples I look at for setting up per-user prefs using SQL show > >creating a table that looks like: > > > >username pref value > > > >So, if I want to allow users to control 5 values I would have a table that > >looks like thsi: > > > >user1 pref1 value1 > >user1 pref2 value2 > >user1 pref3 value3 > >user1 pref4 value4 > >user1 pref5 value5 > >user2 pref1 value1 > >user2 pref2 value2 > >user2 pref3 value3 > >user2 pref4 value4 > >user2 pref5 value5 > >user3 ..... etc. > > > >When talking about importing a userbase of 6000+ that's gonna be a TALL > >table really fast. > > > > > > > 30.000, 5 * 6.000, rows isn't a tall SQL table at all IMHO.
Nope, but think of how it would scale. The design above is bad because there is no unique data in there, so the table will get slow. A better design would be this: 1. A table with just users on there, each with their unique user ID, eg: UsersTable UID Friendlyname 1 bob 2 joe 2. A table for each preference, linked back by the UID in the first table: pref1Table UID Value 1 10 SA can then join the tables based on the UID, and the application only needs to be passed the UID to get all the values. You can also gain efficiencies with these smaller tables because you can optimise what fields are in there (eg on your SpamCutoffTable will only have integer and tinyint as field types). Your only problem would be perhaps passing to the application what values the user has got customised, but you could fix that up in two(four) ways which would alter the number of select statements needed: UsersPrefsTable UID Preferences 1 pref1, pref2, pref3 A different way of doing this is multiple fields with booleans: UsersPrefsTable UID pref1 pref2 pref3 1 1 1 0 Or you can build it into your original users table: UsersTable UID Friendlyname Preferences 1 bob pref1, pref2, pref3 The other way: UsersTable UID Friendlyname pref1 pref2 pref3 pref4 1 bob 1 0 0 1 I'm looking into integrating user prefs this quarter where I work, and I do have some concerns on how it will scale (e.g., with mysql replication you need to send writes to a different machine from reads if you need to have seperate databases, like one on each machine for reads and a master for writes). I wish more apps could be more db-aware :) Cheers Mike -- | Mike Grice Broadband Solutions for | Systems Engineer Home & Business @ | PlusNet plc. www.plus.net + ----- PlusNet - The smarter way to broadband ------
