Database Schema for multiple authentications, Facebook Connect, Twitter, OpenID, etc

Question

I am building an application that I want to interface with Facebook Connect, Twitter, OpenID, and potentially other social networks. Users will be able to login using any number of these methods at the same time. My application uses MySQL as a backend database.

Can someone give me guidance on what my db schema should look like for capturing user info from various social networks at the same time? One idea I have (based on my reading online) is something like:

User {userid, ...}
UserFacebook {fbid, userid, ...}
UserTwitter (twid, userid, ...}
etc., etc.

Then to get a complete picture of a user I would join across all the user tables. Is that how other sites do it or is there a smarter/better way?

Answer 1

Why not just use Janrain Engage for the multi-provider login part?

Answer 2

Janrain is WAAY overpriced for what it does.

Answer 3

This is what i do, i separate the accounts table from the authentication process, e.g. the account holds the account name, registration date, and unique id maybe. Then I can create 4 additional tables for example: users_openid, users_facebook, users_twitter and users (for your normal username/website authentication), all have a foreign key (account_id) that links to the account table.

This way you separate how the user sign-in to your system and the actual account.