Database design for 2 types of users

Question

I have 2 ways for users to create an account on my website.

a. Normal Registration Form (email, password) b. Registration via Facebook Connect (fb_userid, email)

Which is the best practice to implement this using MySQL (InnoDB engine) ?

My approach:

[USER]
user_id
user_type (normal/facebook)

[USER_NORMAL]
user_normal_id
user_id
email
password

[USER_FACEBOOK]
user_facebook_id
user_id
email
fb_userid

What do you suggest?

Answer 1

If those are the only fields then it's probably easiest to put all the fields in one table and have NULLs as appropriate.

However, if you want a normalised design you would go for something like this:

[USER]
user_id (PK)
email
(Other fields common to both)

[USER_NORMAL]
user_id (PK, FK to USER.user_id)
password
(Other fields specific to 'normal')

[USER_FACEBOOK]
user_id (PK, FK to USER.user_id)
fb_userid
(Other fields specific to FB)

If 'password' is the only field specific to 'normal' users and there are many fields specific to FB users then a compromise might be to have two tables: USER (as above but containing 'password') and USER_FACEBOOK