Should user preferences be included in the users table?

I'm creating a members site, and I'm currently working on the user Preference settings. Should I create a table with all the preference fields (about 17 fields) or should I include them in the main member table along with the account settings?

Is there a limit as to how many fields I should have in a table? curre开发者_JS百科ntly the member table has about 21 fields... not sure if its okay to add another 17 more fields when I can easily just put them in another table. It'll take more coding to pull up the data though... any sugguestions?

i would separate them for maintainability. one query can pull all of them. and the difference should be negligible

Separate into three tables:

• MEMBERS
• PREFERENCE_TYPE_CODE
• MEMBER_PREFERENCES

MySQL has a hard limit of 4,096 columns, but it's not ideal to rely on that knowledge. The table structure I above will allow you to add or remove preferences without resorting to ALTER TABLE statements. It will also support querying for who specifically has a given preference.

It depends.

For a small site, jamming everything into one table would probably require less coding (doesn't require a join). And because user preferences seem to be 1:1 with user accounts, this is what I tend to favor. Also, with a join, there is a possibility that user preferences don't exist for a given user, if the database is corrupted or not constrained properly.

The limit of fields in a table is dependent on your engine. The answer for 5.5 may be found at http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html.

21 fields is quite a lot. It's not a problem for MySQL, but I would prefer a separate table.

If it's likely there will be more preferences later, you might even consider a generic 3 column table for the preferences. The columns being something like:

UserID
PreferenceName
PreferenceValue 

One row for each user preference. You might want to add a 4th column to be able to store lists of values. Effective use of this type of database model would require support for multiple result sets (so you can fetch the user info with all their preferences at once). You might want to check if your database access technology supports them.

Of course, with that approach, you would lose the ability to pick a data type for each preference. As an advantage, you could expand this model to make a Preference table, where you can define defaults for each preference.

# Fetch all preferences for a user, using defaults where no preferences are given
SELECT p.PreferenceName, COALESCE(up.PreferenceValue, p.DefaultValue)
FROM Preference p
LEFT OUTER JOIN UserPreference up ON up.PreferenceID = p.ID
WHERE up.UserID = ?

This would allow you to change defaults for your site easily.