Unified User Account with Multiple Social Logins/Signups Database Structure

Question

I am creating a website where the users can login/signup via facebook, twitter, linkedin, google plus and the regular form process.

my Database Structure for this process so far is

**users_table** 
id - username - password_hash - email - auth_key -date_created - last_login 

**user_profile**
id - user_id (foreign_key) - first_name - last_name - gender - phone - photo

**facebook_users**
id - facebook_id - profile_link - user_id (foreign_key)

**linkedin_users**
id - linkedin_id - profile_link - user_id (foreign_key)

**have_social_account**
facebook(tinyint)[0-1] - twitter(tinyint)[0-1] - linkedin(tinyint)[0-1] - google(tinyint)[0-1]

now when the user login with his facebook for example am creating a new record for him on users table and user_profile with the additional info, and i insert the auth key and profile link into facebook_users (since its a facebook login), finally i insert a new record in have_social_account with value (1) under facebook.

but if the same user tried to login with his linkedin account; a new record will be created with a new user_id, so thats why i cant unify the account for this user. i can handle it if the email is the same, but what if the user have different email address for each social account.

can anybody help me with the best structure for this process? Thanks in advance

Answer 1

I don't think you can do anything if a user uses different e-mail addresses across your different authentication providers. You need something you can rely on for uniqueness that all the major players will be able to provide to you, and IMHO e-mail is about as good as it gets.

That being said, I don't think you need so many tables. Your users and user_profiles tables are fine, but above that why not just have a single table for storing the different provider accounts?

provider_accounts
-----------------
    id
    user_id
    type (enum of 'Facebook', 'Linked In')
    profile_link

With this structure you can easily get all of the provider accounts for a single user and determine if a given email already has a specific account for a given provider type.

You could potentially mitigate this by having some sort of utility that will 'merge' two accounts together, should it be requested by the user or in another way determined the two accounts are actually the same user.