Choosing a method to store user profiles?

Question

I'm in the process of working on a user profile system for a website and am pondering what would be a better (scalable) approach to take. I've come up with two solutions and am looking for either input or perhaps pointers to something I might have missed.

The following create table statements are not meant to be executable but are merely there to give an idea of the layout of the tables involved.

My initial thought was something like this:

CREATE TABLE user(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    user_email VARCHAR(320),

    user_joined DATATIME,
    user_last_seen DATATIME,

    user_name_first VARCHAR,
    user_name_last VARCHAR,

    user_name_alias VARCHAR,

    user_location_country VARCHAR,
    user_location_region VARCHAR,
    user_location_city VARCHAR

    # ...
);

Obviously this isn't very scalable at all and adding additional properties i annoying. The one advantage is I can quickly search for users matching a specific set of properties. I've done a bit of looking around and this is a pretty common approach (e.g. Wordpress).

My second approach (the one I'm currently playing around with) is much more scalable but I'm a little concerned about performance:

CREATE TABLE user(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    user_email VARCHAR(320)
);

CREATE TABLE user_profile(
    user_id INT UNSIGNED NOT NULL,

    visibility ENUM('PRIVATE', 'PUBLIC'),

    name VARCHAR,
    value VARCHAR
);

Using this approach every use has a set of key value pairs associated with it which makes adding additional properties trivial as well as loading the users profile when they login. However I lose all the type information I had in the first approach (e.g. DATETIME is now stored as a formatted string) so some searches become annoying. This does give me more control over selecting which properties the user wants publicly displayed.

Would a hybrid approach be better allowing me to balance the advantages and disadvantages of both methods? What method does SO use? Is there another approach to this that I haven't thought of or missed?

Extension: With a hybrid approach would it be advantageous to also insert the properties from the user table into the user_profile table to control their visibility to other users or could that possibly be seen as additional overhead?

Answer 1

I'd use a hybrid approach. Some basic properties such as username, email, lastlogindate etc should be added to your user table. Items of secondary importance can be added as key/value pairs.

This way you can still easily search for the most essential information and keep adding profile items without schema changes.