MYSQL: User - profile details table setup - best practice

Question

Next to your normal user table "user"(user_id/user_email/user_pwd/etc), what is the best way to go to store profile information?

Would one just add fields to the user table like "user"

(user_id/user_email/user_pwd/user_firstname/user_lastname/user_views/etc)

or create another table called "profiles"

(profile_id/user_id/user_firstname/user_lastname/user_views/etc)

or would one go for a table with property definitions and another table to store those values?

I know the last one is the most flexible, as you can add and remove fields easily. But for a big site (50k users up) would this be fast?

Answer 1

Things to consider with your approaches

Storing User Profile in Users Table

• This is generally going to be the fastest approach in terms of getting at the profile data, although you may have a lot of redundant data in here (columns that may not have any information in them).
• Quick (especially if you only pull columns you need from the db)
• Wasted Data
• More difficult to work with / maintain (arguably with interfaces such as PHPMyAdmin)

Storing User Profile in User_Profile Table 1-1 relationship to users

• Should still be quite quick with a join and you may eliminate some data redundancy if user profiles aren't created unless a user fills one in.
• Easier to work with
• Ever so slightly slower due to join (or 2nd query)

Storing User Profile as properties and values in tables

*i.e. Table to store possible options, table to store user_id, option_id and value*

• No redundant data stored, all data is relevant
• Most normalised method
• Slower to retrieve and update data

My impression is that most websites use the 2nd method and store profile information in a second table, its common for most larger websites to de-normalize the database (twitter, facebook) to achieve greater read performance at the expense of slower write performance.

I would think that keeping the profile information in a second table is likely the way to go when you are looking at 50,000 records. For optimum performance you want to keep data that is written heavily seperated from data that is read heavy to ensure cache can work effectively.

Answer 2

Table with property definitions isn't the good idea. I suggest to use three tables to store data:

user(id,login,email,pwd, is_banned, expired, ...) 
-- rarely changed, keep small, extremaly fast search, easy to cache, admin data
profile(id, user_id, firstname,lastname, hobby,description, motto)
--data often changed by user,...    
user_stats(id,user_id,last_login,first_login,post_counter, visit_counter, comment_counter)
--counters are very often updated, dml invalidate cache

The better way to store authorisation and authentication data is LDAP.