Storing User Preferences & Site Preferences

[eluser]Zack Kitzmiller[/eluser]
Before I go into what I'm looking for, I do want to say that I have something in place. It works, and it works fine.. But I know that it's not the best practice. I'm wondering about a better way to handle it.

The way I have it set up current is I have 2 tables with information about members. One table has usernames, real names, password, etc. and the second table has 4 columns. Preference ID, Preference Name, Preference Value, Then a foreign key of who the preference belongs to.

This works, but I end up with a HHUUGGEE table, one row for each preference for each user _cannot_ be the right way to do it. I suppose that I could build a table for each preference, but instead of having 4 millions rows, I have 4 million tables.

Any suggestions of cleaning this up?

Thanks <3

[eluser]jayrulez[/eluser]
change the structure of your preference table to something like this

fk-> user_id
show_email
show_profile... etc...

that way you can have 1 record for the preference of the user linked to the user table by the user_id key.

it might not b really effecient when adding new preferences as you would have to add new rows to the table but if you have an idea of all the criterias you need for preference then it should do the trick

[eluser]Zack Kitzmiller[/eluser]
So you're suggesting I do exactly what I'm doing, but only use the foreign key?

I'm not sure if that's what I'm looking for.. But maybe it is.

[eluser]gtech[/eluser]
how many preferences per user are you likely to have, and how many users are you catering for?

what type of preferences are you storing?

[eluser]skunkbad[/eluser]
what about something like this:

Code:

CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL,
  `user_name` varchar(12) NOT NULL,
  `user_pass` varchar(60) NOT NULL,
  `user_email` varchar(60) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY (`user_name`),
  UNIQUE KEY (`user_email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE `user_prefs` (
  `user_id` int(10) unsigned NOT NULL,
  `user_phone` varchar(10) default NULL,
  `user_address` varchar(80) default NOT NULL,
  -- `user_state` ...
  -- `user_city` ...
  -- `user_zip` ...
  -- `user_country` ...
  -- ...
  -- ...
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You would add fields to the user_prefs table if needed, and each site user only needs one row in each table.

[eluser]jayrulez[/eluser]
[quote author="skunkbad" date="1245574396"]what about something like this:

Code:

CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL,
  `user_name` varchar(12) NOT NULL,
  `user_pass` varchar(60) NOT NULL,
  `user_email` varchar(60) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY (`user_name`),
  UNIQUE KEY (`user_email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE `user_prefs` (
  `user_id` int(10) unsigned NOT NULL,
  `user_phone` varchar(10) default NULL,
  `user_address` varchar(80) default NOT NULL,
  -- `user_state` ...
  -- `user_city` ...
  -- `user_zip` ...
  -- `user_country` ...
  -- ...
  -- ...
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You would add fields to the user_prefs table if needed, and each site user only needs one row in each table.[/quote]

yes this is what i was suggesting

[eluser]Zack Kitzmiller[/eluser]
That could possibly work.. I've got about 6000 users, each with about 50 - 60 preferences so that suggested table won't really be feasible.

[eluser]jayrulez[/eluser]
it will significantly decrease the number of records in the preference table as it will only b 1 record per user

[eluser]tomcode[/eluser]
You could split the prefs into some groups (tables), depending on what data You need on Your different pages.

[eluser]nZac[/eluser]
jayrulez is correct in saying that it will.

6000 users with 50-60 pref = 330000 rows.

With this new system you will have 6000 rows, one for each user much more efficient.