Database design for OpenID, Oauth: Twitter and Facebook

Question

I want to allow my users to login using OpenID, Twitter and Facebook, if they choose not to register directly to my website.

After they have logged in with oAuth/OpenID (and before you wonder: yes I have looked up the difference *), I will ask them to choose a username.

All I need from the user is their email address and fullname, (along with an IP Address but that's not relevant now).

The current table I use now is:

author (id, fullname, username, password, salt, ip_address, email_hash, verified, created, deleted)

Password is hashed, and email_hash is used to recover lost usernames.

How would I change this structure to support OpenID/oAuth accounts with one username?

* or OAuth? ,OpenID? Neither? Which one should my site support?

Answer 1

You need 2 additional fields in your table, one is the login source (twitter, openID, facebook) and the other is the source user id (the id provided from twitter, openID, facebook) I recommend making this a string as some log in sources have letters in their user IDs and not just numbers.

Then when they login, you already know the source they have tried to log in from, you can marry it up with the remote id from your table and if it exists log them in, if not, send them to your register form to get them to fill out their name and email.

Another thing you can do in this situation is have a join table:

remote_source_users (user_id:integer, remote_source_id:string, remote_source:string)

This means you can support multiple remote accounts for the same user.